This article describes how to install PostgreSQL 13 on Ubuntu 20.04 and then configure r_keeper connection with a remote PostgreSQL server.

System Update

Update the system with the command:

sudo apt update
sudo apt -y upgrade
BASH

After updating, reboot the system to start the new kernel.

Adding PostgreSQL 13 Repository to Ubuntu

Add PostgreSQL repository

sudo apt -y install vim bash-completion wget
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/` lsb_release -cs`-pgdg main "| sudo tee /etc/apt/sources.list.d/pgdg.list
BASH

Installing PostgreSQL 13

Install PostgreSQL 13

sudo apt update
sudo apt install postgresql-13 postgresql-client-13
BASH

If after installation the database server does not start, then run it with the command:

sudo pg_ctlcluster 13 main start
BASH

Checking PostgreSQL Connection

The postgres user is automatically created during installation. This user has full super administrator access to the entire PostgreSQL instance. The logged-in system user must have sudo privileges before switching to this account.

sudo su - postgres
BASH

To change the user's password to a secure one, enter:

psql -c "alter user postgres with password 'YourSuperPass';"
BASH

If PostgreSQL is already running, then specify the query:

alter user postgres with password 'YourSuperPass';
BASH

Start PostgreSQL command line:

psql
BASH

psql (13.3 (Ubuntu 13.3-1.pgdg20.04 + 1)) 00
Enter help to get the support information.

Connection details:

\conninfo
BASH

You are connected to the postgres database as the postgres user through a socket at /var/run/postgresql on port 5432.

Database Creation

Create a database:

CREATE DATABASE RK765;
BASH

CREATE DATABASE

Create a database user with a password

CREATE USER ucs WITH ENCRYPTED PASSWORD '123';
BASH

CREATE ROLE

Assign rights to the created user within the specified database

GRANT ALL PRIVILEGES ON DATABASE rk765 to ucs;
BASH

GRANT

Assign the rights to create roles to the created user. These rights are required for work with r_keeper:

alter role ucs with createrole;
BASH

ALTER ROLE

List the databases

$body
BASH

postgres=# \l
                                 Database List
   Name    | Owner | Encoding | LC_COLLATE  |  LC_CTYPE   |     Access Rights      
-----------+----------+-----------+-------------+-------------+-----------------------
postgres  | postgres | UTF8      | ru_RU.UTF-8 | ru_RU.UTF-8 |  
rk765     | postgres | UTF8      | ru_RU.UTF-8 | ru_RU.UTF-8 |  
template0 | postgres | UTF8      | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres          +
          |          |           |             |             | postgres=CTc/postgres
template1 | postgres | UTF8      | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres          +
          |          |           |             |             | postgres=CTc/postgres
(4 lines)

Exiting psql

$body
BASH

Logging out of the postgres superuser

exit
BASH

logout

Setting up a remote connection

Installing PostgreSQL 13 on Ubuntu only accepts connections from localhost by default. In production environments, there will be a central database server and remote clients connected to it.

Allowing Remote Connections

To allow remote connections, edit the PostgreSQL configuration file:

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

The configuration file will open. Uncomment or add a line in it at your choice:

  • listen to all interfaces:

    listen_addresses = '*' # Listen on all interfaces
    BASH

  • listen only to given addresses:

    listen_addresses = '192.168.1.101' # Listen on specified private IP address
    BASH

In our case, the selected address is 192.168.1.101, you will have another one.

For NANO text editor:
To save changes in the file, use Ctrl + O
To exit file editing mode, use Ctrl + X

Allowing Remote Connections Accepting

Configure PostgreSQL to accept remote connections from authorized hosts.
Go to the config file:

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

Uncomment or add a new line at your choice:

  • Accepting remote connections from anywhere

    host all all 0.0.0.0/0 md5 # Accept from anywhere
    BASH

  • Accepting remote connections from allowed subnets

    host all all 10.10.10.0/24 md5 # Accept from trusted subnet
    BASH

After the change, restart the postgresql service.

sudo systemctl restart postgresql
BASH

Check the listening address.

netstat -tunelp | grep 5432
BASH

tcp 0  0 0.0.0.0:5432   0.0.0.0:*     LISTEN   123    4698674  -  tcp6  0   0 :::5432    :::*    LISTEN   123     4698675    -

The received information about the listening address confirms that all the settings are correct.