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:
- 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';"
- 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;"
- 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
- In the
/etc/postgresql/13/main/postgresql.conf
configuration file, add the IP address of the server where PostgreSQL is running to thelisten_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.
- 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
- Restart the PostgreSQL service:
sudo systemctl restart postgresql
Configure restrictions and limits of PostgreSQL
- Open the
/etc/postgresql/13/main/postgresql.conf
configuration file for editing:
sudo nano /etc/postgresql/13/main/postgresql.conf
- Increase the maximum number of connections by changing the
max_connections
parameter to2000
:
max_connections = 2000
- Increase the maximum number of object locks per transaction by changing the
max_locks_per_transaction
parameter’s value to512
. You also need to uncomment this line (remove the # symbol):
max_locks_per_transaction = 512
- 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.
конец внимание
- To make a backup of the PostgreSQL data, use the following command:
elma365ctl dump --parts=postgres --consistent
- The dump will be saved to the
/backup
folder with a timestamp.
Restore the PostgreSQL data using elma365ctl restore
- 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.
- Delete the elma365 database with the following command:
DROP DATABASE elma365;
- Create a database and assign the dedicated user as its owner:
CREATE DATABASE elma365 WITH OWNER elma365;
- Enable the required extensions in the database:
CREATE EXTENSION "uuid-ossp"; CREATE EXTENSION pg_trgm;
- 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.
Found a typo? Highlight the text, press ctrl + enter and notify us