Page tree
Skip to end of metadata
Go to start of metadata

Related Pages

For step-by-step instructions, see the PostgreSQL Installation Instructions.

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

System Memory24GB
Storage Capacity4TB (SSD preferred)

Software Requirements

Minimum Version
Operating SystemRHEL 7, CentOS 7, Amazon Linux AMI


yum -y install /

    postgresql postgresql-contrib \
    postgresql-odbc postgresql-plperl \

Note: Versions 10 and above are incompatible with CLAIMS Direct.

IFI CLAIMS Repository
yum -y install \

Note: CentOS 7 needs an additional repository: yum -y install epel-release

Amazon Linux
yum -y install \


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

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.

work indexAll indices pertaining to loading30GB (variable)
work textAll raw table data queued for loading100GB (variable)
xml indexAll permanent indices for the data warehouse400GB
xml textAll permanent text for the data warehouse1TB
pg dataThe cluster metadata, reporting, and logging directory5GB (variable)
pg xlogLog shipping for replication50GB (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:

-- TABLESPACE definition for work text

-- TABLESPACE definition for xml text
-- TABLESPACE definition for work index
-- TABLESPACE definition for xml index

-- insert your TABLESPACE name in the appropriate section
set default_tablespace = ''

tblspc_wrkidxwork index/wrkidx/_pg30GB
tblspc_wrktxtwork text/wrktxt/_pg100GB
tblspc_xmlidxxml index/xmlidx/_pg400GB
tblspc_xmltxtxml text/xmltxt/_pg2000GB

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.

su - postgres

# -D, --pgdata: specify location of pg data segment
# -X, --xlogdir: specify location of pg xlog segment
initdb -A trust \
       -D /var/lib/pgsql/data \
       -E utf8 \
       -X /pgxlog

The following configuration files are to be edited:


# Allow trusted local access
local   all             all                                     trust

# IPv4 local connections
host    all             all               trust

# Other hosts on subnets that may require access, for example
# host    all             all            trust


listen_addresses = '*'                  # what IP address(es) to listen on;

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:

# on systemd based systems:
systemctl enable postgresql.service

systemctl start postgresql.service

CLAIMS Direct PostgreSQL Role

CLAIMS Direct requires the role alexandria:

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

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
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:


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
Next Steps

Once the data has been loaded, proceed to:

  • No labels