Skip to main content
Version: 5.0

PostgreSQL Configuration

Installing and Initial Setup of PostgreSQL

Note

Before installation, make sure the server meets the hardware requirements.

If PostgreSQL is not yet installed, follow these steps:

Installing PostgreSQL

sudo apt update
sudo apt install postgresql postgresql-contrib

Check the service status:

systemctl status 'postgresql*'

Creating a User and Database

sudo su - postgres -c "createuser user_name"
sudo su - postgres -c "createdb db_name"

Configuring Access

Open the postgresql.conf configuration file and modify the following parameter:

listen_addresses = '*'

In the pg_hba.conf file, add the following line:

host all all 0.0.0.0/0 password

After making the changes, restart PostgreSQL:

sudo systemctl restart postgresql

Setting User Password and Permissions

Note

In all commands below, replace user_name, db_name и password with values appropriate for your system:

  • user_name — your PostgreSQL username
  • db_name — your database name
  • password — the user's password

Run psql as the postgres user:

sudo -u postgres psql

Within the interactive shell, execute the following commands in sequence:

\c db_name
ALTER USER user_name WITH PASSWORD 'password';
GRANT CREATE ON SCHEMA public TO user_name;
ALTER DATABASE db_name OWNER TO user_name;
Important!

If you encounter the error relation does not exist, it may be necessary to manually prepare the tables:

sudo -u postgres psql

\c db_name
CALL public.preparetables('assets_name');
ALTER TABLE assets_name OWNER TO user_name;

To test the connection:

psql -U user_name -h 127.0.0.1 -p 5432 -d db_name

Configuring SSL/TLS

Enabling SSL on the PostgreSQL Server

In the postgresql.conf file, enable SSL and specify the certificate paths:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

In the pg_hba.conf file, add the following line to require SSL connections:

hostssl all all 0.0.0.0/0 md5

The server.crt and server.key files must be placed in the PostgreSQL data directory ($PGDATA). Set the appropriate permissions:

chmod 600 server.key
chown postgres:postgres server.*
Important!

The certificate files must be in PEM format. PostgreSQL only supports PEM.

Note

Generating self-signed certificates

If you don't have existing certificates, you can generate a self-signed SSL certificate using OpenSSL:

openssl req -new -x509 -days 365 -nodes \
-out server.crt \
-keyout server.key \
-subj "/CN=your_domain_or_ip"

For more details, refer to the official PostgreSQL.

Restart PostgreSQL to apply the changes:

sudo systemctl restart postgresql

Connection Failover (High Availability)

PostgreSQL, via the libpq driver, supports specifying multiple host addresses in the connection string. This allows automatic failover—if one server is unavailable, the next one will be used.

Example connection string:

host=host1,host2 port=5432 dbname=db_name sslmode=verify-full