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