Updating PostgreSQL from 9.x to 10.x

Blog post by dsantamauro

As of June 2020, CLAIMS Direct added support for PostgreSQL 10.x. The changes required to support 10.x unfortunately broke backwards compatibility to 9.x. Therefore, to migrate from 9.x to 10.x, one needs to use the new 10.x schema delivered in the package alexandria-schema-tools. This new package, available through the IFI CLAIMS Direct yum repository, also provides tools for quality control ( cd-count.sh ) as well as tools for bulk extraction and loading ( cd-extract.sh  and cd-load.sh ). What follows will be a guide to migrate CLAIMS Direct from 9.x to 10.x.

Please note, updates should be stopped (or paused) before proceeding. See apgupd for instructions.


In order to migrate, the entire contents of the alexandria database must be extracted to disk.


  1. Minimum of 1.8TB free space on the extract destination drive. You don't need a full database footprint as the extract files are compressed.
  2. Network connectivity between PostgreSQL 9 and the new PostgreSQL 10. It is also possible to use a separate server either as an intermediary or to share the extract destination drive, e.g., via NFS between the current and new instance.


The first step is to extract the data from the xml and cdws schema.

The following script must be run on a drive with at least 1.8TB free.

First, the script:

cd-extract -h

Usage: cd-extract.sh OPTIONS

  Option        Description                     Default
  -H|--host     specify host                    localhost
  -P|--port     specify port                    5432
  -d|--database specify database                alexandria
  -u|--user     specify database user name      alexandria
  -h|--help     print this usage and exit

By default, this script will use the alexandria database located on localhost and output the compressed table data to ./data. If storage is tight on the CLAIMS Direct instance, you can also use an intermediary server to extract and load, or use a storage server to share disk to both the old and new CLAIMS Direct instances via NFS, e.g.,


For the sake of simplicity, we'll use the 9.x CLAIMS Direct instance for extraction and loading. If using a separate server, you will need to install PostgreSQL (the default version is sufficient: yum -y install postgresql ), then replace localhost with the IP if you are using the 9.x instance.

# Create a location for the extract

cd-extract.sh -H localhost -d alexandria -u alexandria

# Please note, this process can take up to 3 days to complete,
# therefore it is recommended to detach process using nohup
nohup cd-extract.sh -H localhost -d alexandria -u alexandria &

# Progress will be output to nohup.out (standard out for terminal invocation), e.g.,
Checking ./data ... not present, creating ... ok
Extracting data ........ done



  1. A new CLAIMS Direct instance. Please see https://docs.ificlaims.com/display/CDVDP/PostgreSQL+Installation+Instructions for step-by-step instructions.
  2. Access to the extract directory which contains the data directory of the files created above. This could be the local CLAIMS Direct 9.x instance, an intermediate server, or via a shared drive.


Loading the data is accomplished in much the same way you loaded the original CLAIMS Direct instance: cd-load.sh or load.sh for earlier installs.

# move into the directory created above

# Load the data
cd-load.sh -H IP -d alexandria -u alexandria

# As above, this is a multi-day process and
# it is therefore recommended to detach the process
nohup cd-load.sh -H [IP-of-CD-10x-instance] -d alexandria -u alexandria &