Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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

...

Panel
titleBefore you start
  • 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 will need to know which operating system you are using before you begin the installation.
  • The server, either directly or indirectly, must be able to access the internet on ports 80 and /or 443 (preferably both)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 .
  • All commands, unless otherwise noted, must be run as root.
Note

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

1. IFI CLAIMS will create a single tar.gz file that includes several bash scripts that are used during implementation and 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 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.

2. If you received the data as a tar.gz file, extract it into your local environment. The receiving drive will require approximately 3TB of free space to download and extract the file.

3. Use yum to install PostgreSQL.

Code Block
yum -y install \
    perl-devel \
    postgresql postgresql-contrib \
    postgresql-odbc postgresql-plperl \
    postgresql-server

4. If you are using CentOS 7, add the epel repository.

Code Block
languagetext
yum -y install epel-release

If you are using RHEL 7, use the following code to add the epel repository. Otherwise, continue to step 5.

Code Block
languagetext
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.

...

  • or the necessary ports must be opened (postgresql: 5432; client tools: 80 and 443).
  • Install the CLAIMS Direct yum repository:

    Distribution

    URL

    CentOS/RHEL 8
    yum -y install \

...

...

...

...

...

...

  • AWS Linux 2
    yum -y install \

...

...

...

...

...

Note

Note:

...

yum -y install \
https://repo.ificlaims.com/ifi-claims-direct/amazon/x86_64/ifi-claims-direct-beta-0.0.2-1.amzn1.x86_64.rpm

...

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 3TB of free space to download and extract the file.


2. Run a yum update to pull in the patched version of libxml2 from the IFI CLAIMS repository and any other pending updates.

Code Block
sudo yum 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

Install:

Code Block
su - postgres

followed by:

Code Block
initdb -A trust \
       -D /var/lib/pgsql/data \
       -E utf8

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

...

languagetext
sudo yum -y install postgresql postgresql-contrib postgresql-plperl postgresql-server

Initialize:

Code Block
languagetext
#sudo Allow-u trustedpostgres 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

...

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
languagetext
sudo systemctl enable postgresql.service ; sudo systemctl restartstart postgresql.service


105. Create the role alexandria and load the SQL via psql into the instance.If you are using PostgreSQL version 10.x, use the following code:

text
Code Block
language
echo "create role alexandria with superuser login;" \
  | psql -Upostgres postgres
 
U postgres postgres

Install the schema and tools.

Code Block
languagetext
sudo yum -y install alexandria-schema-tools
gunzip -c /usr/share/alexandria-schema-tools/alexandria-dbs-10.x.gz \
  | psql -Ualexandria -h <POSTGRESQL-HOST> postgres

...

Create the database.

Code Block
languagetext
echo "create role alexandria with superuser login;" \
  | psql -Upostgres postgres

gunzip -c /usr/share/alexandria-schema-tools/alexandria-dbs-910.x.gz \ 
  | psql -UalexandriaU -h <POSTGRESQL-HOST> alexandria postgres


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

Code Block
psql -U alexandria --list

The results should show the alexandria database.

Code Block
languagetext
                 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


12. Run pgtuneNote that this requires Python. If you are using PostgreSQL version 9.4 and above, you can use the online tool 7. Tune the database before loading.

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

Setting

Recommended

Notes

autovacuum_max_workers5

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

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

autovacuum_vacuum_scale_factor0.02This setting indicates the threshold which determines when autovacuum needs to run per table.
autovacuum_analyze_scale_factor0.01This 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/#/ and fill . Fill in the required values as well as those that correspond to your system. Add or change the appropriate settings and restart PostgreSQL.the suggested changes to the bottom of postgresql.conf.

Note

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

Code Block
languagetext
#
sudo 
For PostgreSQL ≥ 9.4, use the online tool and paste added configuration options at end of postgres.conf # For CentOS 7 and PostgreSQL < 9.4 only: yum -y install pgtune pgtune -i postgresql.conf -T DW -c 500

...

systemctl restart postgresql.service


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

Code Block
cd-pre-flight-check.sh 

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

Code Block
languagetext
# 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.

...

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
languagetext
nohup cd-load.sh &

15. Use the ps command periodically to check whether the loading process has completed.

Note

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


1610. You can monitor the load using pg_stat_activity.

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


11. Once the loading process is complete,  you you can run 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
languagetext
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:

...

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


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

Code Block
languagetextsql
echo "select * from xml.t_patent_document_values where ucid = 'US-5551212-A'" | psql -U alexandria
Panel
titleNext Steps

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

Familiarize yourself with the PostgreSQL Schema and Tools.