The PostgreSQL component is the heart 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|dnf groupinstall "Development Tools"

 yum|dnf install \
    postgresql postgresql-contrib \
    postgresql-odbc postgresql-pl-perl \
    postgresql-server

yum|dnf install \
    libxml2 libxml2-devel \
    libxslt libxslt-devel

yum|dnf install \
    perl-Module-Install \
    perl-DBD-Pg \
    perl-XML-LibXML \
    perl-XML-LibXSLT \
    perl-CPAN

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 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 discreet 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 chose 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

/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

CLAIMS Direct postgresql role

CLAIMS Direct requires the role alexandria

Creating the Database

The postgresql data warehouse portion of CLAIMS Direct is delivered in 2 parts:

•  postgresql database schema (alexandria-dwh.sql)
• <table>.gz files located in the sub-directory data
  
  

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.

Load the 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