Problem

According to the SecureTransport Installation Guide, to install SecureTransport with an external PostgreSQL Database we should have an account created with specific privileges and the PostgreSQL should have specific tweaks.

Once a new database is created using the PostgreSQL Database Configuration, how to prepare it for SecureTransport?

Resolution

Database tweaks required for SecureTransport

These tweaks are global and apply to the whole database, not just the SecureTransport user.

postgres=# SET work_mem TO 'half of the available RAM'; 
postgres=# SET maintenance_work_mem TO 'at least 512MB';
postgres=# SET effective_cache_size TO ' 3/4 of the available RAM';
postgres=# SET shared_buffers TO '1/4 of the available RAM'; 
postgres=# SET max_connections=1000;

Add user and set quotas

<STUSER> should be replaced with the username. <PASSWORD> should be replaced with the actual password.

postgres=# CREATE USER <STUSER> WITH ENCRYPTED PASSWORD '<PASSWORD>';
postgres=# ALTER USER <STUSER> CREATEDB;
postgres=# ALTER USER <STUSER> CREATEROLE;

Create Tablespaces:

All three tablespaces should be created on fast disks (preferably SSDs) on location with enough free space to handle at least the predicted SecureTransport workload multiplied by two.

Ex:- postgres=# CREATE TABLESPACE test OWNER test LOCATION '.../tmp/test'; ( <Path> should be pointing to the directory where the tablespace would be created).

postgres=# CREATE TABLESPACE st_data OWNER <STUSER> LOCATION '<Path>';
postgres=# CREATE TABLESPACE st_filetracking OWNER <STUSER> LOCATION '<Path>';
postgres=# CREATE TABLESPACE st_serverlog OWNER <STUSER> LOCATION '<Path>';

Create a Database and attach it to the User:

The database should be created with (default) tablespace st_data.

postgres=# CREATE DATABASE <Database_Name> OWNER <STUSER> TABLESPACE st_data;
postgres=# GRANT ALL PRIVILEGES ON DATABASE <Database_Name> TO <STUSER>;

A restart of the PostgreSQL Database is required to apply the tweaks.