PostgreSQL

This article describes how to install PostgreSQL for Ubuntu Linux 20.04. Also, you can find a short user guide here: https://www.postgresql.org/download/linux/ubuntu/.

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

In this examaple we use elma365 as database name, elma365 as user, and SecretPassword as password.

When doing your settings, specify these data according to your company's security policies.

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

 

Install PostgreSQL

For correct operation, you will need PostgreSQL version 10.

To install PostgreSQL, add the postgresql 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

Next, install PostgreSQL:

sudo apt install postgresql-10

 

PostgreSQL settings

To set up PostgreSQL use the following commands:

  1. Create the new `elma365` role with the password `SecretPassword`:

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

 

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

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

 

  1. Add necessary extension for the elma365 database:

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

 

Connection permissions for PostgreSQL

  1. Add the IP address of the server where PostgreSQL is running in listen_addresses, for example 192.168.100.101:

sudo nano /etc/postgresql/10/main/postgresql.conf
listen_addresses = 'localhost, 10.0.1.1, 192.168.100.101'

 

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

 

  1. In the end of the authentication configuration file `/etc/postgresql/10/main/pg_hba.conf` add the address of the network where elma354 nodes are working:

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

 

  1. Restart the PostgreSQL server:

sudo systemctl restart postgresql

Connect to PostgreSQL

Use this command to connect PostgreSQL:

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

Back up the PostgreSQL data using elma365ctl dump

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

While creating a backup, elma365 services will be discontinued and the app will become unavailable.

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

  1. To back up the postgresql data, use the following command:

elma365ctl dump --parts=postgres --consistent 

  1. The dump will be saved to the /backup folder with a Time Stamp Protocol.

Restore the PostgreSQL data using elma365ctl restore

  1. Discontinue the app services and prepare the system for the data recovery using the command:

elma365ctl restore –prepare

After that, you need to recreate the PostgreSQL database.

  1. Delete the elma365 database with the command:

DROP DATABASE elma365;

  1. Create a database and assign the selected user as its owner:

CREATE DATABASE elma365 WITH OWNER elma365;

  1. Enable the required extensions in the database:

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

  1. Restore the data with the command:

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

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

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