PostgreSQL

The article describes the installation of PostgreSQL for Ubuntu Linux 20.04. You can also refer to the official PostgreSQL documentation for a brief guide. ELMA365 requires PostgreSQL version 10-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-13

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';"

2. 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;"

3. 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/13/main/postgresql.conf in the parameter listen_addresses, for example, 192.168.10.10:

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

Configuration of TLS/SSL in PostgreSQL

2. Add the network address of the elma365 nodes to the end of the authentication configuration file /etc/postgresql/13/main/pg_hba.conf:

sudo nano /etc/postgresql/13/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 /etc/postgresql/13/main/postgresql.conf for editing:

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

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

max_connections = 2000

3. 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

4. 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.

Was this helpful?

Thanks for your feedback!

Please specify why:

Recommendations did not help meArticle is hard to understandDidn`t answer my questionContent does not match the topicOther

How we can improve it?

YesNo

Please specify whyRecommendations did not help meArticle is hard to understandDidn`t answer my questionContent does not match the topicOther

Found a typo? Highlight the text, press ctrl + enter and notify us