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.

 

Creating the VT Writer and VT Insights Databases

 

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

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

postgres=> \i create-wrier-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

 

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

 

Add the following to the 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

 

2 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

 

 

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 DB, follows the steps below.

 

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

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

 

 

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

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 run the following in the external database you should see the text you just analyzed :   

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