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.
Panel | ||
---|---|---|
| ||
|
Note |
---|
Note: It is recommended to copy and paste the code provided in these instructions. |
1. Notify support@ificlaims.com when your equipment is prepared. Please provide IFI with the name, address, and telephone number of the person who is to receive a copy of the compressed PostgreSQL database. IFI will replicate a copy of the PostgreSQL database on a USB drive and ship it to you.
2. When you receive the USB, connect it to your intended PostgreSQL machine and mount the drive so that it can be read.
3. Use yum to install PostgreSQL.
Code Block |
---|
yum -y install \
postgresql postgresql-contrib \
postgresql-odbc postgresql-plperl \
postgresql-server |
4. If you are using CentOS, add the epel repository.
Code Block | ||
---|---|---|
| ||
yum -y install epel-release |
If you are using RHEL, use the following code to add the epel repository. Otherwise, continue to step 5.
Code Block | ||
---|---|---|
| ||
yum -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-$(rpm -E '%{rhel}').noarch.rpm |
5. Install the appropriate repository for your operating system using the command listed in the Repository column below. If necessary, adjust the code to reflect the version you are using.
Warning | ||
---|---|---|
| ||
If you are using Fedora, you must use version 27 or below. Installing Fedora v28 and above will install a version of PostgreSQL that is incompatible with CLAIMS Direct. |
...
|
...
...
...
...
|
...
...
...
...
...
|
...
...
...
...
...
Note |
---|
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 .
If you are using Fedora, enter the following code:
Code Block |
---|
yum --best --allowerasing update |
If you are using AWS Linux, enter the following code:
Code Block | ||
---|---|---|
| ||
# yum shell <<EOF
remove libxml2.x86_64 libxml2-python27.x86_64
install http://alexandria.fairviewresearch.com/software/libxml2/f20/libxml2-2.9.2-1.fc20.x86_64.rpm
run
y
EOF |
For other operating systems, enter the following code:
...
and any other pending updates. Adjust the code if you are using a different version of PostgreSQL.
Code Block | ||
---|---|---|
| ||
# 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 |
---|
Note: Reboot if kernel was upgraded. |
73. 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 will install PostgreSQL and initialize the cluster and give you rudimentary authentication and access levels needed to run CLAIMS Direct. Note that the initdb
. The initdb
command has to be run by the user who owns PostgreSQL (user postgres). Enter:
Code Block |
---|
su - postgres |
followed by:
Code Block |
---|
initdb -A trust \
-D /var/lib/pgsql/data \
-E utf8 |
8. As root, restart postgresql.
Install:
Code Block | ||
---|---|---|
| ||
systemctl restart postgresql.service |
9. Using a text editor, modify the IP addresses in the following configuration files.
Note |
---|
Note: If you are installing the client tools on a separate machine, other hosts will be required. Be sure to remove the hash (#) at the start of the ‘other hosts’ entry if you need to enable access for other hosts or subnets. |
/var/lib/pgsql/data/pg_hba.conf
Code Block | ||
---|---|---|
| ||
# Allow trusted local access
local all all trust
# IPv4 local connections
host all all 127.0.0.1/32 trust
# Other hosts on subnets that may require access, for example
# host all all xxx.xxx.xxx.xxx/xx trust |
# 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:
Code Block | ||
---|---|---|
| ||
sudo -u postgres initdb -A trust -D /var/lib/pgsql/data |
...
Code Block |
---|
listen_addresses = '*' # what IP address(es) to listen on; -E utf8 |
Note |
---|
Note: 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. |
...
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.
Code Block | ||
---|---|---|
| ||
sudo systemctl enable postgresql.service ; sudo systemctl restartstart postgresql.service |
115. Create the role alexandria and load the SQL via psql
into the instance.
Code Block | ||
---|---|---|
| ||
echo "create role alexandria with superuser login;" \ | psql -U postgres postgres |
12. Change the directory to the mounted USB file system and create the database. If desired, you can redirect errors (if any) to LOG.2.Install the schema and tools.
Code Block | ||
---|---|---|
| ||
sudo yum -y install alexandria-schema-tools |
Create the database.
Code Block | ||
---|---|---|
| ||
cat /usr/share/alexandria/alexandria-schema-xml/alexandria-dwhschema-xml.sql | psql -U alexandria postgres 2>/tmp/LOG.2 |
...
|
6. To ensure that the database has been created, run:
Code Block | ||
---|---|---|
| ||
psql -U alexandria --list |
The results should show the alexandria database.
Code Block | ||
---|---|---|
| ||
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 | |||||
---|---|---|---|---|---|
Note: For changes to be applied, PostgreSQL needs to be restarted:
|
8. Run the pre-flight check script to confirm that your system is properly configured to load the data.
Note |
---|
Note: The scripts used in these instructions are located on the mounted USB file system. |
Code Block | ||
---|---|---|
Code Block | ||
| ||
cd-pre-flight-check.sh |
The sample output of a properly configured system would look looks like this:
Code Block | ||
---|---|---|
| ||
# 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
...
.
Code Block | ||
---|---|---|
| ||
nohup bash ./cd-load.sh xxx.xxx.xxx.xxx alexandria & |
16. Use the ps
command periodically to check whether the load.sh
process has completed.
...
Note: If you want to check on the process while it is running, use the following command to show the progress of the tables which are being copied:
Code Block |
---|
ps -eaf |grep COPY |
1710. You can monitor the load using pg_stat_activity.
Code Block | ||
---|---|---|
| ||
select query_start, state, query from pg_stat_activity where datname = 'alexandria'; |
11. Once the loading process is complete, you you can run the the cd-count.sh
script script, a simple QA of table counts, to ensure that the tables have loaded correctly. Modify the IP address to reflect the postgresql server. This may take an hour or more to run.
Code Block | ||||
---|---|---|---|---|
| ./||||
cd-count.sh xxx.xxx.xxx.xxx alexandria |
The results should show that 37 39 xml tables and 4 cdws tables have loaded. The following tables are deprecated and 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.
1812. Optional: you may want to run a simple SQL query as an additional test to confirm that the data is present.
Code Block | ||
---|---|---|
| ||
echo "select * from xml.t_patent_document_values where ucid = 'US-5551212-A'" | psql -U alexandria |
Note |
---|
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. |
Panel | ||
---|---|---|
| ||
Once the data has been loaded, proceed to Client Tools Installation. Familiarize yourself with the PostgreSQL Schema and Tools. |