This guide explains how to configure  VT Docs installed on an Azure instance to use Azure PostgreSQL as an external database.

 

Note: This guide assumes you running VT Docs on either RHEL and you are running VT Docs 3.4.2 or greater.

 

Install on Azure PostgreSQL

For this, you will need  Postgresql 12/13. When creating the Postgresql server you will be asked to provide a master username and password for the database. In the instructions below replace <master user> with the master username you have chosen.

The PostgreSQL server should be a part of the same virtual network as the VT Docs application server so they can communicate via port 5432. If that's not doable you can configure your Azure PostgreSQL server to allow public IP and using the firewall rules only allow your VT Docs application server access to the database.

The Azure PostgreSQL should have the following parameters set:

max_locks_per_transaction=500

The above can be done by clicking on "Server Parameters" in the Azure UI for your PostgreSQL server. When in Server Parameters search "max_locks_per_transaction" and change it's value to "500".

In the drop down menu to the right of "azure.extensions" select "PGCRYPTO"

You can now save the changes.

 

Creating the VT Docs user and Schema

The first step to complete is to create the VT Docs user and database in Postgres. Once that is complete we can bootstrap the database with an empty VT Docs schema.

Creating the VT Docs database and user

Download the sql file listed below to create VT Docs user and database structure on your external PostgreSQL:

https://visiblethread.s3.amazonaws.com/public/download/createDocsDB.sql

On your VT Docs application server, use psql to run the file 'createDocsDB.sql' as the master user. 

This will create a database called “vtdocs” with a database user called “vtdocs”. You must specify a password you wish to use for this database user.

mkdir vtsetuptmp

cd vtsetuptmp

curl -O https://visiblethread.s3.amazonaws.com/public/download/createDocsDB.sql

psql -h <postgresql endpoint name> -p 5432 -d postgres -U <master user> -f createDocsDB.sql -v pwd="'a secure password'"

 

Create the empty VT Docs Schema

Download the SQL file from here:

https://visiblethread.s3.amazonaws.com/public/download/vtdocs-tables.sql

Run the attached file 'vtdocs-tables.sql' as the vtdocs user (you will be prompted for a password. Enter the password you specified above). The exact format for connecting with a psql client will depend on your system. An example is listed below:

curl -O https://visiblethread.s3.amazonaws.com/public/download/vtdocs-tables.sql

psql -h <postgresql endpoint name> -p 5432 -U vtdocs -f vtdocs-tables.sql

 

Modify the VT Docs application config to point to your Database server

On your VT Docs application server, navigate to the following folder:

/opt/visiblethread/tomcat/conf/ 

Open the ‘context.xml’ file for editing:

sudo pico context.xml

Update the line that contains url="jdbc:postgresql://127.0.0.1:5432/visiblethread"  with url="jdbc:postgresql://<database-endpoint-address>:5432/vtdocs?sslmode=require"

Update the line that contains username="visiblethread" with username="vtdocs"

Update password="password" with the password you have specified for the vtdocs user above.

Save and exit pico with ctrl + o, enter then ctrl + x, enter.

Restart the VT Docs services:

sudo service visiblethread-docs restart

When complete, restart the Apache service

sudo systemctl restart httpd