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