PostgreSQL

This article describes how to install PostgreSQL for Ubuntu Linux 20.04. You can also find a short user guide in the official documentation.

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

In this article, we show an example with elma365 as the database name, elma365 as the user, and SecretPassword as the password.
When setting up PostgreSQL, follow your company’s security policies.

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

Install PostgreSQL

For correct operation, you will need PostgreSQL version 10–13.

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

Configure PostgreSQL

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

You can use the following characters in a password:

• Capital Latin letters (A to Z)
• Lowercase Latin letters (a to z)
• Digits (0 to 9)
• Symbols: !()-_+=$

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

To set up PostgreSQL, use the following commands:

1. To work with the database, create a new user. In this example, we are creating a user with elma365 as the username and SecretPassword as the password. These username and password are only given as an example and can be changed.

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

2. Create a separate elma365 database and assign the dedicated elma365 user as its owner. You cannot use an existing database, as the system creates tables and charts:

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

Connection permissions for PostgreSQL

1. In the /etc/postgresql/13/main/postgresql.confconfiguration file, add the IP address of the server where PostgreSQL is running to the listen_addresses parameter (for example, 192.168.10.10):

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

10.0.1.1 is the default IP address when allowing access to the host from MicroK8s by using the sudo microk8s enable host-access command.

2. Open the authentication configuration file /etc/postgresql/13/main/pg_hba.conf and add the address of the network where elma365 nodes are working at the end of the file:

sudo nano /etc/postgresql/13/main/pg_hba.conf
host    all             all             10.0.0.0/8              md5
host    all             all             192.168.0.0/16              md5

3. Restart the PostgreSQL service:

sudo systemctl restart postgresql

Configure restrictions and limits of PostgreSQL

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

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

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

max_connections = 2000

3. Increase the maximum number of object locks per transaction by changing the max_locks_per_transaction parameter’s value to 512. You also need to uncomment this line (remove the # symbol):

max_locks_per_transaction = 512

4. Restart the PostgreSQL service:

sudo systemctl restart postgresql

Connect to PostgreSQL

Use this command to connect to PostgreSQL:

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

Back up the PostgreSQL data using elma365ctl dump

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

While a backup is being made, elma365 services are stopped, and the application becomes unavailable.

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

1. To make a backup of the PostgreSQL data, use the following command:

elma365ctl dump --parts=postgres --consistent 

2. The dump will be saved to the /backup folder with a timestamp.

Restore the PostgreSQL data using elma365ctl restore

1. Stop the application’s services and prepare the system for the data recovery using the following command:

elma365ctl restore --prepare

After that, you need to recreate the PostgreSQL database.

2. Delete the elma365 database with the following command:

DROP DATABASE elma365;

3. Create a database and assign the dedicated user as its owner:

CREATE DATABASE elma365 WITH OWNER elma365;

4. Enable the required extensions in the database:

CREATE EXTENSION "uuid-ossp"; CREATE EXTENSION pg_trgm;

5. Restore the data with the following command:

elma365ctl restore --parts=postgres --path=/backup/<dump_folder>

The system will be available again within a few minutes after the command is completed.

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