Contents

• Assumptions
• Configure a standalone PostgreSQL for Flow Production Tracking
  • Centos 7 installation script
  • Performance tuning
  • Key parameters
  • Allow External Hosts
  • Containers Configuration
  • Firewall Configuration: Database server
  • Firewall Configuration: External hosts
  • Timezone Configuration

Assumptions

• The server hardware is satisfying Flow Production Tracking recommended specifications for a database server.
• The operating system setup has been completed properly.
• The operation system is Centos 7.
• The Postgresql version is 15

Configure a standalone PostgreSQL for Flow Production Tracking

We are providing a script to set up your PostgreSQL server properly for Flow Production Tracking. You don’t have to use it, but it is highly recommended to do so.

Centos 7 installation script

You can use the following script, which will set up PostgreSQL properly for Flow Production Tracking:

Just copy the script on your database server and run it. The script will install PostgreSQL, configure it, start it, and initialize it ready to be used by Flow Production Tracking.

 bash postgres_bootstrap_centos7.sh

You can test your connection by running:

 psql -h localhost -d postgres -U shotgun

Performance tuning

There are no specific requirements for tuning PostgreSQL for Flow Production Tracking.

If PostgreSQL is installed on the same server as other services, we recommend using the default configuration.

If PostgreSQL is running on a dedicated server, we recommend following these generic guidelines from PostgreSQL:

• https://wiki.postgresql.org/wiki/Performance_Optimization
• https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Key parameters

Make sure that the following parameters are properly tuned for your database server. These settings are dependant on the amount of RAM available on your database server. Make sure you understand each setting before changing them.

# Backup current configuration file. Revert to this backup and restart if things go wrong.
sudo cp /var/lib/pgsql/15/data/postgresql.conf /var/lib/pgsql/12/data/postgresql.conf.bak

# Change the values in postgresql.conf
sudo vi /var/lib/pgsql/15/data/postgresql.conf

# Restart postgresql
sudo service postgresql-15 restart

Allow External Hosts

When running PostgreSQL on a dedicated server, you need to allow your other Flow Production Tracking components to connect to it. To allow external connections, first edit PostgreSQL to accept incoming connections from the hosts.

# Edit the PostgreSQL configuration
sudo vi /var/lib/pgsql/15/data/pg_hba.conf

# Allow each host to connect to the database. Be as restrictive as possible.
# Allow a given host
host all all 192.168.2.28/32 md5
# Allow a given subnet
host all all 192.168.2.0/24 md5

# Have postgres listen to all network interface
sudo vi /var/lib/pgsql/15/data/postgresql.conf

# Change listen_addresses to match the following:
    listen_addresses='*'

# Finally, restart Postgres
sudo service postgresql-15 restart

Containers Configuration

Your containers will need to be modified in order to use the new database server instead of the mock-up container. See Standalone PostgreSQL Configuration in the Flow Production Tracking Enterprise Docker User Manual for details.

Firewall Configuration: Database server

Make sure that your firewall allows incoming connection to PostgreSQL port. For example:

# Add a specific IP or a subnet
# NOTE: YOU MAY WANT TO BE MORE RESTRICTIVE AND LIMIT ONLY THE GIVEN HOST TO CONNECT.
sudo firewall-cmd --permanent --add-service=postgresql
sudo firewall-cmd --reload

Firewall Configuration: External hosts

Make sure that the firewall of each host allows outbound connections to the database server.

Make sure Docker containers can reach the database server. For example:

sudo firewall-cmd --permanent --zone=trusted --add-interface=docker0
sudo service docker restart

Timezone Configuration

Timezone consistency between SE Docker components is important to ensure the correct functioning of Flow Production Tracking. Containers are configured to use the UTC timezone by default, whereas PostgreSQL inherits the host timezone by default. If your standalone database server is configured with a timezone other than UTC, you can insure timezone consistency by explicitly setting the PostgreSQL timezone to UTC. Simply modify your postgresql.conf to include timezone = 'UTC' and then reload / restart the daemon.