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