VisibleThread -
Help Center Find helpful articles on different VisibleThread Products

Follow

How to configure VT Writer on Red Hat to connect to an external database

The default configuration when VT Writer is installed uses a local Postgres database. This guide shows you how to connect your VT Writer instance (installed on Red Hat) to an external Postgres database.

Note: in all snippets below replace <external-postgres-server-or-ip> with your external Postgres server hostname or IP.

 

Prerequisites

Your external Postgres version must be version 10.x and must have the postgres-contrib module installed.

You must be running with VT Writer v 2.6.1 or later.

 

Setup external VisibleThread database

VT Writer uses 3 databases. The VT Readability database is used to store details of user accounts, the documents they have processed and the document analysis. 

The VT Insights database is used to track analysis and usage statistics over time. This is a reporting database. 

The third database, referred to as the VT API database is used as temporary storage for each analysis job. As a document is being analyzed, data relating to the analysis job and progress is placed in this database. Once the analysis job is complete, the data is removed.

 

If you have a new VT Writer install, please follow Creating the VT Writer and VT Insights Databases. If you have an existing VT Writer install and would like to migrate your data to the external database, please follow Migrating Existing Data to the External Database.  After either, please continue to Change VT Writer configuration to use external database.

 

Creating the VT Writer and VT Insights Databases

If you have a new VT Writer deployment to connect to an external database, please follow the steps below.

1. On the external database server, edit the postgres.conf file to listen for connections.

Under the Connections and Authentication section, remove the front # (hashtag) from listen_addresses and in single quotes enter the ip address of the postgres server.

Example:

mceclip0.png

 

2. Configure PostgreSQL to allow access to the VT Insights, VT Writer and VT API DB

Add the following to the bottom of pg_hba.conf:

host      vtreadability      vtreadability       <vt-writer-server-ip/32>      md5
host vtinsights vtinsights <vt-writer-server-ip/32> md5
host vtapi vtapi <vt-writer-server-ip/32> md5
host all postgres <vt-writer-server-ip/32> trust

Restart the postgres service:

systemctl restart *postgresql*

 

3. Connect to your external PostgreSQL database and run the following SQL script to setup the VT Writer user and database in PostgreSQL:  create-writer-and-insights-db.sql

psql -h <external-postgres-server-or-ip> -U postgres

postgres=> \i create-writer-and-insights-db.sql

postgres=> \q 

 

Run the following SQL script to setup the VT API user and database in PostgreSQL:   createAPIDB.sql

psql -h <external-postgres-server-or-ip> -U postgres 

postgres=> \i createAPIDB.sql

postgres=> \q

 

 

Next we need to create the empty table schema for our three databases:

 

VTWriter:

 

Download the attached create-writer-tables.sql  and run as the vtreadability user (the password is vtreadability2017)

 

#psql -h <postgresql server name> -U vtreadability

postgres=> \i create-writer-tables.sql

postgres=> \q

 

VT Insights

 

Download the attached create-insights-tables.sql and run as the vtinsights user (the password is vtinsights2018)

 

#psql -h <postgresql server name> -U vtinsights

postgres=> \i create-insights-tables.sql

postgres=> \q

 

 

VT Api

 

Download the attached createApiTables.sql and run as the vtapi user (the password is visiblethread7)

 

#psql -h <postgresql server name> -U vtapi

postgres=> \i createApiTables.sql

postgres=> \q

Please continue to Change VT Writer configuration to use external database.

 

Migrating existing data to the external database

If you have an existing VT Writer deployment and you wish to migrate the data to your new external database, follow the steps below.

 

1. On the external database server, edit the postgres.conf file to listen for connections.

Under the Connections and Authentication section, remove the front # (hashtag) from listen_addresses and in single quotes enter the ip address of the postgres server.

Example:

mceclip0.png

 

2. Configure PostgreSQL to allow access to the VT Insights, VT Writer and VT API DB

Add the following to the bottom of pg_hba.conf:

host      vtreadability      vtreadability       <vt-writer-server-ip/32>      md5
host vtinsights vtinsights <vt-writer-server-ip/32> md5
host vtapi vtapi <vt-writer-server-ip/32> md5
host all postgres <vt-writer-server-ip/32> trust

Restart the postgres service:

systemctl restart <enter your postgres service>

 

3. Connect to your external PostgreSQL database and run the following SQL script to setup the VT Writer user and database in PostgreSQL:  create-writer-and-insights-db.sql

psql -h <external-postgres-server-or-ip> -U postgres

postgres=> \i create-wrier-and-insights-db.sql

postgres=> \q 

 

4. Run the following SQL script to setup the VT API user and database in PostgreSQL:   createAPIDB.sql

psql -h <external-postgres-server-or-ip> -U postgres 

postgres=> \i createAPIDB.sql

postgres=> \q

5. On the existing deployment, backup the DBs :

cd /opt/visiblethread
sudo su postgres
pg_dump vtreadability --no-owner > /tmp/vtreadabilitybackup.sql
pg_dump vtinsights --no-owner > /tmp/vtinsightsbackup.sql
pg_dump vtapi --no-owner > /tmp/vtapibackup.sql

6. On the local box, restore the backups to the external DB box :

psql -h <external-postgres-server-or-ip> -U vtinsights -f /tmp/vtinsightsbackup.sql vtinsights
psql -h <external-postgres-server-or-ip> -U vtreadability -f /tmp/vtreadabilitybackup.sql vtreadability
psql -h <external-postgres-server-or-ip> -U vtapi -f /tmp/vtapibackup.sql vtapi

 

Change VT Writer configuration to use external database

Modify the settings in the file /etc/sysconfig/visiblethread.env 

 

# Environment File
DB_VTREAD_URL="jdbc:postgresql://external-Database:5432/vtreadability"
DB_INSIGHTS_URL="jdbc:postgresql://external-Database:5432/vtinsights"
DB_API_WORKER_URL="jdbc:postgresql://external-Database:5432/vtapi"


# Custom Database User
DB_VTREAD_USER="vtreadability"
DB_INSIGHTS_USER="vtinsights"
DB_API_WORKER_USER="vtapi"


# Custom Database Password
DB_VTREAD_PASS="vtreadability2017"
DB_INSIGHTS_PASS="vtinsights2018"
DB_API_WORKER_PASS="visiblethread7"

 

 

 

Restart all Services on the VT Writer server

/opt/visiblethread/VisibleThreadTools/vt-services.sh restart

or

sudo systemctl restart vt-readability
sudo systemctl restart vtapi-doc-worker
sudo systemctl restart vtapi-web-worker
sudo systemctl restart vtapi-text-worker
sudo systemctl restart vtapi-text-worker2
sudo systemctl restart vtapi-scan-worker

 

 

Verify that VT Writer is using the external DB

In your web browser, login to VT Writer and run a text scan.  If you migrated data to the external server, look at the Analysis History to ensure that you can view the previous scans.

If you run the following in the external database you should see the text you just analyzed, if you migrated data you should also see previous scans that were restored into the database:   

psql
\c vtreadability
select title, started_date from scans order by id desc;

Recommended config for external Postgres

We recommend that you make this change to your external postgres.conf :

max_connections = 200
max_locks_per_transaction=500
Was this article helpful?
0 out of 0 found this helpful

Get Additional Help

Visit our Helpdesk for additional help and support.