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.

Hardware Requirements

Software Requirements

 yum -y install \
    postgresql postgresql-contrib \
    postgresql-odbc postgresql-plperl \
    postgresql-server

RHEL/CentOS 7
yum -y install \ 
 https://repo.ificlaims.com/ifi-claims-direct/centos/7/x86_64/ifi-claims-direct-beta-0.0.2-1.el7.centos.x86_64.rpm

Amazon Linux
yum -y install \
 https://repo.ificlaims.com/ifi-claims-direct/amazon/x86_64/ifi-claims-direct-beta-0.0.2-1.amzn1.x86_64.rpm 

Fedora
yum -y install \
 https://repo.ificlaims.com/ifi-claims-direct/fedora/27/x86_64/ifi-claims-direct-beta-0.0.2-1.fc27.x86_64.rpm

PostgreSQL Installation

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.

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:

/var/lib/pgsql/data/pg_hba.conf

# Allow trusted local access

/var/lib/pgsql/data/postgresql.conf

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:

psql -Upostgres postgres
=#> create role alexandria with superuser login;

# create the database
cat alexandria-dwh.sql | psql -Ualexandria postgres

Tuning the Data Warehouse

pgtune (optional)

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.

yum -y install pgtune
  pgtune -i postgresql.conf  -T DW -c 500
  [ paste added configuration options at end of postgres.conf ]

Redirecting errors to LOG.2 (optional)

If desired, you can redirect errors (if any) to LOG.2:

cat alexandria-dwh.sql | psql -Ualexandria postgres 2>LOG.2

Pre-flight check

Use the pre-flight check to ensure the database and operating system are properly configured before loading the tables:

./pre-flight-check.sh

The sample output of a properly configured system would look like this:

# Testing localhost/alexandria ...
#   OK    : procedual language sql
#   OK    : procedual language plpgsql
#   OK    : procedual language plperl
#   OK    : procedual language plperlu
#   OK    : XML capability (test 1/libxml): 
#   OK    : XML capability (test 2/libxml): 

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:

# for each table, load ...
gunzip -c data/<table>.gz | psql -Ualexandria alexandria
 
# sample batch script
for tbldata in $(ls data/*.gz); do
  gunzip -c $tbldata | psql -Ualexandria alexandria
done