The PostgreSQL component is the heart of CLAIMS Direct. It contains the XML for the entire data warehouse collection, processes updates from the primary, and functions as data source for the optional SOLR index.
|Storage Capacity||4TB (SSD preferred)|
|Operating System||RHEL 7, CentOS 7, Amazon Linux AMI|
postgresql postgresql-contrib \
Note: Versions 10 and above are incompatible with CLAIMS Direct.
|IFI CLAIMS Repository|
Note: CentOS 7 needs an additional repository:
Some CLAIMS Direct loading and maintenance code utilizes the PostgreSQL perl extension (plperl) as well as a heavy reliance on the libxml2 XML parsing library. IFI CLAIMS has produced a patched release of libxml2 as an RPM. It is highly recommended to update libxml2 from the IFI CLAIMS software repository. For additional distributions, please contact firstname.lastname@example.org.
Regardless of installation type, careful planning of disk resources is important for efficient data loading into and extraction out of PostgreSQL. There are 6 logical segments inside the CLAIMS Direct data warehouse.
|work index||All indices pertaining to loading||30GB (variable)|
|work text||All raw table data queued for loading||100GB (variable)|
|xml index||All permanent indices for the data warehouse||400GB|
|xml text||All permanent text for the data warehouse||1TB|
|pg data||The cluster metadata, reporting, and logging directory||5GB (variable)|
|pg xlog||Log shipping for replication||50GB (variable)|
Each of these segments can be allocated discrete disk space through the use of TABLESPACES. Although not required, the use of TABLESPACES will improve loading and extraction performance. The total logical size of the data warehouse is approximately 2TB after initial loading.
Suggested PostgreSQL Disk Layout
As mentioned above, the CLAIMS Direct PostgreSQL cluster can utilize TABLESPACES to separate text, index, and work table data. An optimal (but not mandatory) layout will have each of the following paths on separate disk groups where "disk group" is understood to be a discrete disk or set of disks exposed to the operating system as a device capable of supporting an ext4 file system.
Please note, these are only suggestions. Your environment and disk sub-system naming may be different, or you can choose not to use TABLESPACES at all. A PostgreSQL cluster running on a 2TB RAID0 sub-system exposed as one device, for example, wouldn't benefit as noticeably using TABLESPACES as a mixed RAID environment with multiple devices.
If you choose to implement TABLESPACES, you will need to edit
alexandria-dwh.sql and insert your custom tablespace definitions. These are clearly marked per segment as:
During cluster initialization (initdb), you can further define the location for the segments: pg data and pg xlog.
PostgreSQL Initial Configuration
CLAIMS Direct requires a working PostgreSQL cluster. If you do not have an initialized cluster, the following steps will initialize the cluster and give you rudimentary authentication and access levels needed to run CLAIMS Direct.
The following configuration files are to be edited:
# Allow trusted local access
If you already have an initialized cluster, please be certain that local access is enabled for stand-alone installation. In either distributed install, if a separate services machine is created, its IP address needs access. This is imperative for the client update procedures.
Finally, enable and start the PostgreSQL cluster:
CLAIMS Direct PostgreSQL Role
CLAIMS Direct requires the role alexandria:
psql -Upostgres postgres
Creating the Database
The PostgreSQL data warehouse portion of CLAIMS Direct is delivered in two parts:
- PostgreSQL database schema (alexandria-dwh.sql)
- <table>.gz files located in the sub-directory data
# create the database
Tuning the Data Warehouse
Although specifying exact postgresql.conf configuration parameters may seem beneficial, in reality, every installation is different. There are many factors in tuning your cluster including system memory, resource contention from other services running on the server, available disk space, disk types et al. The primary cluster supporting your updates was tuned using the tool pgtune. It is recommended to run the tool and apply the changes before bulk loading the data. Note that this requires Python.
Redirecting errors to LOG.2 (optional)
If desired, you can redirect errors (if any) to LOG.2:
Use the pre-flight check to ensure the database and operating system are properly configured before loading the tables:
The sample output of a properly configured system would look like this:
Loading the Tables
The process of loading CLAIMS Direct data into PostgreSQL tables will take 1-2 days on most hardware configurations.
The following code block outlines the loading of the CLAIMS Direct data into PostgreSQL tables: