PostgreSQL Configuration
Installing and Initial Setup of PostgreSQL
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
In all commands below, replace user_name
, db_name
и password
with values appropriate for your system:
user_name
— your PostgreSQL usernamedb_name
— your database namepassword
— 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;
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.*
The certificate files must be in PEM format. PostgreSQL only supports PEM.
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