# PostgreSQL

> [HTML Version](postgresql.html)

The article describes how to:

- Install PostgreSQL for Ubuntu Linux 24.04. You can also refer to the [official PostgreSQL documentation](https://www.postgresql.org/download/linux/ubuntu/) for a brief guide.

- [Prepare a PostgreSQL database for restoration](#prepare-to-restore).

Before installing the DBMS:

- Check the supported versions of PostgreSQL for correct system operation in the [System requirements for BRIX On-Premises](elma365-enterprise-on-premises.md#mongo) article.

- Evaluate the planned workload. For large data volumes, it is recommended to install the DBMS on a separate virtual machine. This will prevent resource competition and ensure stable operation of the BRIX system. For more details on infrastructure organization options, refer to [Prepare BRIX On-Premises infrastructure](infrastructure-preparation.md). 

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

````
In this example, the database name is **brix365**, the user is **brix365**, 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](#install-postgresql).

2. [Configure PostgreSQL](#set-up-postgresql).

3. [Configure connection to PostgreSQL](pdf-convert.md).

4. [Configure restrictions and limits for PostgreSQL](#set-limits-to-postgresql).

5. [Prepare PGBouncer (optional)](#prepare-pgbouncer).

6. [Connect to PostgreSQL](#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-18

## ````
Step 2: Configure PostgreSQL

````
Начало примечание

````
**Note**

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 **brix365** 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 brix365 WITH login password 'SecretPassword';"

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

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

3. ````
Add the necessary extensions for the **brix365** database:

````
sudo -u postgres psql -d brix365 -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 \[OBJECT\] in the parameter \[OBJECT\], for example, \[OBJECT\]:

````
sudo nano /etc/postgresql/18/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 \[OBJECT\], you need to:

1. Enable SSL support by setting the value \[OBJECT\] in the parameter \[OBJECT\].

2. Specify the path to the root CA file in the parameter \[OBJECT\].

3. Specify the path to the server certificate file in the parameter \[OBJECT\].

4. Specify the path to the private key file in the parameter \[OBJECT\].

````
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](https://www.postgresql.org/docs/13/ssl-tcp.html).

2. Add the network address of the brix365 nodes to the end of the authentication configuration file \[OBJECT\]:

````
sudo nano /etc/postgresql/18/main/pg\_hba.conf  
host    all             all             192.168.0.0/16              md5

3. ````
Reload the PostgreSQL service:

````
sudo systemctl restart postgresql

## ````
Step 4: Configure restrictions and limits for PostgreSQL

1.  Open the configuration file \[OBJECT\] for editing:

````
sudo nano /etc/postgresql/18/main/postgresql.conf

2. ````
Increase the maximum number of connections by changing the value of the parameter \[OBJECT\] to \[OBJECT\]:

````
max\_connections = 2000

3. ````
Increase the maximum number of locks per transaction by changing the value of the parameter \[OBJECT\] to \[OBJECT\]. Also, uncomment this line (remove the # sign):

````
max\_locks\_per\_transaction = 512

4. ````
Reload the PostgreSQL service:

````
sudo systemctl restart postgresql

5. ````
In some distribution packages of PostgreSQL, the ````
\[OBJECT\]````
 parameter that interprets backslash symbols is disabled. Check and enable the parameter:

````
standard\_conforming\_strings = on

## ````
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](pgbouncer-installation.md).

## Step 6: Connect to PostgreSQL

PostgreSQL connection string:

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

````
PostgreSQL connection string with TLS/SSL:

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

````
Connection to PostgreSQL via PGBouncer.

Connection to PostgreSQL via PGBouncer:

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

````
PGBouncer connection string with TLS/SSL:

````
postgresql://brix365:SecretPassword@<postgresql-server-address>:6432/brix365?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:

- [External BRIX backup](dump_db.md).

- [Backup and recover databases](database-backup-and-recovery.md).

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

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

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

1. ````
Delete the **brix365** database:

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

2. ````
Create an empty **brix365** database to restore the database from backup. To do this, use the commands from [Step 2: Configure PostgreSQL](#create-db).

3. After that, depending on your BRIX On-Premises edition and backup method, perform data restoration:

- [Restore a backup copy using the utility](database-backup-and-recovery.md#recovery-database).

- [Restore a backup copy using WAL-G](backup-and-recovery-with-wal-g-and-rclone.md#walg-pgsql).

- [Restore a backup copy when using external means](dump_db.md#restore).