Page History

IFI CLAIMS will create a single tar.gz file that includes a subdirectory of tar.gz files for each of the tables in the PostgreSQL data warehouse. This file will be placed into an S3 bucket and we We will provide you with a link for you to access and download the file. Alternately, if you have received the data on a USB, connect it to your intended PostgreSQL machine and mount the drive so that it can be read.

1. If you received the data as a tar.gz file, extract it into your local environment. The receiving drive requires approximately 3TB 4TB of free space to download and extract the file. The extracted file will consist of a number of smaller tar.gz files. There is no need to extract each of these smaller files.

2. Run Prepare repositories and run a yum update to pull in the patched version of libxml2 from the IFI CLAIMS repository and any other pending updates. Adjust the code if you are using a different version of PostgreSQL.

# Amazon Linux 2
sudo amazon-linux-extras install epel
sudo amazon-linux-extras enable postgresql14
sudo yum clean all
sudo yum update

# RHEL/CentOS 7
sudo yum -y install epel-release
sudo yum clean all
sudo yum update

# Rocky
sudo dnf -y install epel-release
sudo dnf config-manager --set-enabled powertools
sudo dnf clean all
sudo dnf update

# RHEL
sudo subscription-manager repos --enable codeready-builder-for-rhel-8-x86_64-rpms
sudo dnf install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm
sudo dnf clean all
sudo dnf update

...

Install:

# Note: this installs the default version of your distribution. If you would like a higher version,
#       please see: https://www.postgresql.org/download/linux/redhat/
sudo yum -y install postgresql postgresql-contrib postgresql-plperl postgresql-server

Initialize:

sudo -u postgres initdb -A trust -D /var/lib/pgsql/data -E utf8

...

4. Enable and restart the PostgreSQL cluster.

sudo systemctl enable postgresql.service ; sudo systemctl start postgresql.service

...

5. Create the role alexandria and load the SQL via psql into the instance.

echo "create role alexandria with superuser login;" \
  | psql -U postgres postgres

Install the schema and tools.

...

sudo yum -y install alexandria-data-warehouse-pg11

...

sudo yum -y install alexandria-schema-tools

Create the database.

...

cat /usr/share/alexandria

...

/alexandria-

...

schema-xml/alexandria-schema-

...

xml.sql | psql -U alexandria postgres

6. To ensure that the database has been created, run:

psql -U alexandria --list

...

8. Run the pre-flight check script to confirm that your system is properly configured to load the data.

cd-pre-flight-check.sh 

The sample output of a properly configured system looks like this:

...

Resolve any recognized errors. For unfamiliar errors, contact support@ificlaims.com.

9. Switch to the directory which holds the backfile you extracted in step 1. Use the load script to load the CLAIMS Direct data into PostgreSQL tables. Since the loading process will take 1-2 days, we recommend that you use the nohup command to detach the script from the terminal and allow it to run in the background.

nohup cd-load.sh &

10. You can monitor the load using pg_stat_activity.

...

11. Once the loading process is complete, you can run the cd-count.sh script, a simple QA of table counts, to ensure that the tables have loaded correctly. This may take an hour or more to run.

cd-count.sh

The results should show that 39 xml tables and 4 cdws tables have loaded. The following tables will show a count of 0:

...

echo "select * from xml.t_patent_document_values where ucid = 'US-5551212-A'" | psql -U alexandria