View Source

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. We will provide you with a link 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.

Provision the necessary hardware to support your implementation plan.
Select one of the recommended operating systems from the options listed on the server recommendations page. You need to know which operating system you are using before you begin the installation. We do not support Ubuntu or any operating system not explicitly listed.
The server, either directly or indirectly, must be able to access the internet on ports 80 and 443.
Ensure that the machine and the data directory location of the PostgreSQL installation have sufficient disk space by running the df -h command. Requirements and recommendations for disk layout can be seen here: PostgreSQL.
Ensure that SELinux is disabled.
Ensure that firewalld is disabled or the necessary ports must be opened (postgresql: 5432; client tools: 80 and 443).
We recommend restricting the root alexandria user to the database administrator only. For all other uses, we suggest creating a separate user which has only SELECT permissions in the core schema.

Install the CLAIMS Direct yum repository:

Distribution

URL

RHEL/CentOS 7

sudo yum -y install \
  https://repo.ificlaims.com/ifi-claims-direct/centos/7/x86_64/ifi-claims-direct-1.0-1.el7.x86_64.rpm

RHEL/Rocky 8

sudo dnf -y install \
  https://repo.ificlaims.com/ifi-claims-direct/rocky/8/x86_64/ifi-claims-direct-1.0-1.el8.x86_64.rpm

Amazon Linux 2

sudo yum -y install \
  https://repo.ificlaims.com/ifi-claims-direct/amzn2/x86_64/ifi-claims-direct-1.0-1.amzn2.x86_64.rpm

Note: It is recommended to copy and paste the code provided in these instructions.

1. If you received the data as a tar.gz file, extract it into your local environment. The receiving drive requires approximately 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. 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

Note: Reboot if kernel was upgraded.

3. CLAIMS Direct requires a working PostgreSQL cluster. If you have a working cluster, skip to step 5. If you do not have an initialized cluster, the following commands install PostgreSQL and initialize the cluster. The initdb command has to be run by the user who owns PostgreSQL (user postgres).

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

Note: PostgreSQL, by default, only allows local connections. If you would like to open access to network clients or are installing the Client Tools on a separate server, please see Allowing Remote Access to PostgreSQL Alexandria.

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

The results should show the alexandria database.

                 List of databases      
Name       |  Owner    | Encoding |   Collate  |    Ctype    |  Access privileges
-----------------+------------+----------+-------------+-------------+-----------------------
alexandria |alexandria | UTF8     | en_US.UTF-8| en_US.UTF-8 |                                		                                                                                                 
postgres   |postgres   | UTF8     | en_US.UTF-8| en_US.UTF-8 |  
template0  |postgres   | UTF8     | en_US.UTF-8| en_US.UTF-8 |=c/postgres          +      
 		   |           |          |            |             | postgres=CTc/postgres
template1  |postgres   | UTF8     | en_US.UTF-8| en_US.UTF-8 |=c/postgres          +       
           |           |          |            |             | postgres=CTc/postgres

7. Tune the database before loading.

In postgresql.conf, adjust the autovacuum settings as follows:

Setting

Recommended

Notes

autovacuum_max_workers

5

This is the recommended setting for a 16-core machine.

For a 4-core machine, a setting of 2 is recommended.

autovacuum_vacuum_scale_factor 0.02 This setting indicates the threshold which determines when autovacuum needs to run per table.

autovacuum_analyze_scale_factor 0.01 This setting tells the autovacuum process to analyze the table (i.e., update query planner statistics) when the fragmentation percentage reaches 1% (the default is 10%).

For other performance tuning, use the online tool https://pgtune.leopard.in.ua/#/. Fill in the required values that correspond to your system. Add the suggested changes to the bottom of postgresql.conf.

Note: For changes to be applied, PostgreSQL needs to be restarted:

sudo systemctl restart postgresql.service

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:

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

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.

select query_start, state, query from pg_stat_activity where datname = 'alexandria';

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:

cdws.t_class_hierarchies
xml.t_revision_history
xml.t_rule_47_flag
xml.t_technical_data
xml.t_us_sir_flag

The following tables will be populated if you have a Premium Plus subscription. For Basic and Premium subscriptions, they will show a count of 0:

xml.t_keywords
xml.t_statistical_information

More information about the tables can be seen in Data Warehouse Design.

12. Optional: you may want to run a simple SQL query as an additional test to confirm that the data is present.

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

Note: For new installations as well as any changes to your subscription, it may take 24 hours for full synchronization of your on-site instance.

Once the data has been loaded, proceed to Client Tools Installation.

Familiarize yourself with the PostgreSQL Schema and Tools.