search string side menu

BRIX On-Premises > Prepare infrastructure > Databases > Prepare external databases / PostgreSQL

PostgreSQL

The article describes how to:

You can also refer to the official PostgreSQL documentation for a brief guide. BRIX requires PostgreSQL version 11-15 for proper operation.  

начало внимание 

In this example, the database name is elma365, the user is elma365, and the password is SecretPassword. When configuring, set these data according to the security policy adopted in your organization.

конец внимание

Installation consists of six steps:

  1. Install PostgreSQL.
  2. Configure PostgreSQL.
  3. Configure connection to PostgreSQL.
  4. Configure restrictions and limits for PostgreSQL.
  5. Prepare PGBouncer (optional).
  6. Connect to PostgreSQL.

Step 1: Install PostgreSQL

To install PostgreSQL, add the official repository:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update

Then install PostgreSQL:

sudo apt install postgresql-15

Step 2: Configure PostgreSQL

Начало внимание

The password can contain the following characters:

  • Uppercase Latin letters: A to Z
  • Lowercase Latin letters: a to z
  • Digits: 0 to 9
  • Symbols: -_

Reserved (invalid) characters: ! * ' ( ) ; : @ & = + $ , / ? % # [ ]

Конец внимание

To configure PostgreSQL, use the following commands:

  1. Create a separate elma365 user with the SecretPassword password for working in the database. The username and password are given as an example:

sudo -u postgres psql -c "CREATE ROLE elma365 WITH login password 'SecretPassword';"

  1. Create a separate elma365 database and assign the dedicated user elma365 as its owner. Do not use an existing database, as the system creates tables and schemas:

sudo -u postgres psql -c "CREATE DATABASE elma365 WITH OWNER elma365;"

  1. Add the necessary extensions for the elma365 database:

sudo -u postgres psql -d elma365 -c "CREATE EXTENSION \"uuid-ossp\"; CREATE EXTENSION pg_trgm;"

Step 3: Configure connection to PostgreSQL

  1. Add the IP address of the server where PostgreSQL is running to the configuration file /etc/postgresql/15/main/postgresql.conf in the parameter listen_addresses, for example, 192.168.10.10:

sudo nano /etc/postgresql/15/main/postgresql.conf
listen_addresses = 'localhost, 192.168.10.10'

Configuration of TLS/SSL in PostgreSQL

To enable TLS/SSL support in PostgreSQL in the configuration file postgresql.conf, you need to:

  1. Enable SSL support by setting the value on in the parameter ssl.
  1. Specify the path to the root CA file in the parameter ssl_ca_file.
  1. Specify the path to the server certificate file in the parameter ssl_cert_file.
  1. Specify the path to the private key file in the parameter ssl_key_file.

ssl = on
ssl_ca_file = '/etc/ssl/certs/CA.pem'
ssl_cert_file = '/etc/ssl/certs/ssl-cert.pem'
ssl_key_file = '/etc/ssl/private/server.key'

For more details on TLS/SSL configuration in PostgreSQL, refer to the official PostgreSQL documentation.
  1. Add the network address of the elma365 nodes to the end of the authentication configuration file /etc/postgresql/15/main/pg_hba.conf:

sudo nano /etc/postgresql/15/main/pg_hba.conf
host    all             all             192.168.0.0/16              md5

  1. Reload the PostgreSQL service:

sudo systemctl restart postgresql

Step 4: Configure restrictions and limits for PostgreSQL

  1. Open the configuration file /etc/postgresql/15/main/postgresql.conf for editing:

sudo nano /etc/postgresql/15/main/postgresql.conf

  1. Increase the maximum number of connections by changing the value of the parameter max_connections to 2000:

max_connections = 2000

  1. Increase the maximum number of locks per transaction by changing the value of the parametermax_locks_per_transaction to 512. Also, uncomment this line (remove the # sign):

max_locks_per_transaction = 512

  1. Reload the PostgreSQL service:

sudo systemctl restart postgresql

Step 5: Prepare PGBouncer (optional)

PGBouncer is designed to manage the connection pool to PostgreSQL and helps minimize the costs associated with establishing new connections to PostgreSQL. PGBouncer, refer to the Install PGBouncer.

Step 6: Connect to PostgreSQL

PostgreSQL connection string:

postgresql://elma365:SecretPassword@<postgresql-server-address>:5432/elma365?sslmode=disable

PostgreSQL connection string with TLS/SSL:

postgresql://elma365:SecretPassword@<postgresql-server-address>:5432/elma365?sslmode=require

Connection to PostgreSQL via PGBouncer.

Connection to PostgreSQL via PGBouncer:

postgresql://elma365:SecretPassword@<postgresql-server-address>:6432/elma365?sslmode=disable

PGBouncer connection string with TLS/SSL:

postgresql://elma365:SecretPassword@<postgresql-server-address>:6432/elma365?sslmode=require

Prepare a PostgreSQL database for restoration

If you need to restore a database from backup, first prepare the database for restoration.

For more details on backup, refer to the following articles:

Начало внимание

Ensure you have a backup copy of the database you are deleting. Only after that, proceed with deleting the database.

Конец внимание

  1. Delete the elma365 database:

sudo -u postgres psql -c "DROP DATABASE elma365 WITH (FORCE);"

  1. Create an empty elma365 database to restore the database from backup. To do this, use the commands from Step 2: Configure PostgreSQL.
  1. After that, depending on your BRIX On-Premises edition and backup method, perform data restoration:
embedded-databases-settings.html mongodb.html