5 min read Advanced 6.2k

PostgreSQL: install and tuning

Published on May 17, 2026 at 04:02 PM

Install PostgreSQL and configure it for production: memory tuning, vacuum, connections, indexes. The reference open-source RDBMS, robust for heavy loads.

Introduction

PostgreSQL is the most serious open-source RDBMS: strict ACID, MVCC, JSONB, full-text search, extensions, replication, partitioning. Used by Apple, Instagram, Reddit, Spotify.

This tutorial covers install and basic tuning for a dedicated server.

Prerequisites

Linux VPS Debian 12 / Ubuntu 24.04
2 vCPU, 4 GB RAM minimum (8 GB recommended for prod)
50 GB SSD/NVMe
Root access

Step 1: Installation

sudo apt update
sudo apt install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql
sudo -u postgres psql -c "SELECT version();"

Step 2: First user and database

sudo -u postgres psql

CREATE USER myapp WITH ENCRYPTED PASSWORD 'strong-pass';
CREATE DATABASE myapp_db OWNER myapp;
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp;
\q

Test:

psql -U myapp -h 127.0.0.1 -d myapp_db

Step 3: Network access

/etc/postgresql/16/main/postgresql.conf:

listen_addresses = 'localhost'   # or '*' for external

/etc/postgresql/16/main/pg_hba.conf:

local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    myapp_db        myapp           10.0.0.0/8              scram-sha-256

scram-sha-256 > md5 (more secure, PG 14+).

sudo systemctl restart postgresql

Step 4: Memory tuning

postgresql.conf for 4 GB RAM:

shared_buffers = 1GB                  # 25% RAM
effective_cache_size = 3GB            # 75% RAM
work_mem = 16MB                       # per sort/join
maintenance_work_mem = 256MB          # VACUUM, CREATE INDEX
wal_buffers = 16MB

Use pgtune: https://pgtune.leopard.in.ua

Step 5: Connections

max_connections = 100

Need more? Use pgbouncer instead (each PG connection costs ~10 MB).

Step 6: WAL and checkpoints

wal_level = replica
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min

For SSD/NVMe:

random_page_cost = 1.1            # default 4.0 for HDD
effective_io_concurrency = 200    # default 1 for HDD

Step 7: Autovacuum

autovacuum prevents table bloat. Never disable it.

autovacuum = on
autovacuum_naptime = 30s
autovacuum_max_workers = 4
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.025

Step 8: Logging

log_min_duration_statement = 500    # log queries > 500ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_line_prefix = '%t [%p]: db=%d,user=%u,client=%h '

Logs in /var/log/postgresql/postgresql-16-main.log.

Step 9: Backup with pg_dump

Single database:

sudo -u postgres pg_dump myapp_db | gzip > myapp_db-$(date +%F).sql.gz

Global:

sudo -u postgres pg_dumpall | gzip > all-$(date +%F).sql.gz

Restore:

gunzip -c myapp_db-2026-05-17.sql.gz | sudo -u postgres psql myapp_db

Custom format for large databases:

sudo -u postgres pg_dump -Fc myapp_db > myapp.dump
sudo -u postgres pg_restore -d myapp_db myapp.dump

Step 10: Advanced backup with pgBackRest

For PITR (point-in-time recovery), see the dedicated pgBackRest tutorial. The reference tool for serious backups.

Step 11: Performance

Indexes

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders(created_at) WHERE status = 'pending';

Slow queries:

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Enable pg_stat_statements:

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION pg_stat_statements;

EXPLAIN

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Watch for Seq Scan (bad) vs Index Scan (good) on big tables.

Step 12: Security

Strong passwords (scram-sha-256)
pg_hba.conf restricted to authorized IPs
TLS via ssl = on and certs
No direct internet access (VPN or bastion)
Encrypted and offsite backups

Enable SSL:

ssl = on
ssl_cert_file = '/etc/ssl/certs/postgresql.crt'
ssl_key_file = '/etc/ssl/private/postgresql.key'

Troubleshooting

"FATAL: password authentication failed"

Check pg_hba.conf: does the line match your client and method?

"too many connections"

SELECT count(*) FROM pg_stat_activity;

Increase max_connections or add pgbouncer.

Slow queries

SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 seconds';

Kill if needed:

SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);

Disk full

sudo du -sh /var/lib/postgresql/16/main/pg_wal

If replication lags, WAL accumulates. Check slots:

SELECT * FROM pg_replication_slots;

Useful commands

sudo systemctl status postgresql
sudo systemctl restart postgresql

sudo -u postgres psql -c "SELECT pg_reload_conf();"
sudo -u postgres psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;"
sudo -u postgres psql -c "SELECT pid, usename, datname, state, query FROM pg_stat_activity;"

sudo -u postgres vacuumdb -d myapp_db -z
sudo -u postgres reindexdb myapp_db

sudo -u postgres psql -c "SHOW config_file;"
sudo -u postgres psql -c "SHOW all;"

Conclusion

Well-tuned PostgreSQL gives you:

Excellent performance up to several TB
High concurrency (MVCC)
Rich extensions (PostGIS, TimescaleDB, pgvector)
ACID robustness

Going further:

Configure streaming replication for HA
Use pgBackRest for serious backups
For massive OLTP, consider CitusDB (sharding) or TimescaleDB (time series)

Resources

Official docs: https://www.postgresql.org/docs/current/
pgtune: https://pgtune.leopard.in.ua
pg_stat_statements: https://www.postgresql.org/docs/current/pgstatstatements.html
PostgreSQL Wiki: https://wiki.postgresql.org