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

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

Installing PostgreSQL 13

Install PostgreSQL 13

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

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

sudo pg_ctlcluster 13 main start

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

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

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

If PostgreSQL is already running, then specify the query:

alter user postgres with password 'YourSuperPass';

Start PostgreSQL command line:

psql

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

Connection details:

\conninfo

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;

CREATE DATABASE

Create a database user with a password

CREATE USER ucs WITH ENCRYPTED PASSWORD '123';

CREATE ROLE

Assign rights to the created user within the specified database

GRANT ALL PRIVILEGES ON DATABASE rk765 to ucs;

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;

ALTER ROLE

List the databases



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




Logging out of the postgres superuser


exit


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


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
    

  • listen only to given addresses:
    

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

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


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
    

  • Accepting remote connections from allowed subnets
    

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

After the change, restart the postgresql service.


sudo systemctl restart postgresql


Check the listening address.


netstat -tunelp | grep 5432


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.


                
        
    
        


                        
    





    

	

		
    
    
            
  • 
            No labels
        
    •