Databases

PostgreSQL Enterprise Guide

Deploy enterprise PostgreSQL with high availability, streaming replication, automated backup, monitoring, and performance tuning on Ubuntu 24.04 LTS.

What you'll learn

Install and configure PostgreSQL 16 for production
Tune memory and I/O settings for optimal performance
Set up streaming replication with a hot standby
Configure automated backups with pg_dump and WAL archiving
Implement row-level security and roles
Monitor with pg_stat_* views and Prometheus exporter
Perform zero-downtime upgrades

Installation & Initial Config

Bash

1# Install PostgreSQL 16 from official repo
2sudo apt install -y postgresql-common
3sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
4sudo apt install -y postgresql-16
5
6sudo systemctl enable --now postgresql
7sudo -u postgres psql -c "SELECT version();"
8
9# Create app user and database
10sudo -u postgres psql <<EOF
11CREATE USER appuser WITH PASSWORD 'securepassword';
12CREATE DATABASE appdb OWNER appuser;
13GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
14EOF

Performance Tuning

INI

1# /etc/postgresql/16/main/postgresql.conf
2# For 16 GB RAM, 8 vCPU, SSD server:
3shared_buffers          = 4GB
4effective_cache_size    = 12GB
5maintenance_work_mem    = 1GB
6work_mem                = 20MB
7max_connections         = 200
8random_page_cost        = 1.1
9effective_io_concurrency = 200
10max_wal_size            = 4GB
11checkpoint_completion_target = 0.9
12wal_buffers             = 64MB
13default_statistics_target = 100
14log_min_duration_statement = 500
15
16sudo systemctl reload postgresql

Streaming Replication

Bash

1# On primary: postgresql.conf
2wal_level             = replica
3max_wal_senders       = 5
4wal_keep_size         = 1GB
5hot_standby           = on
6
7# pg_hba.conf — allow replica to connect
8host replication replicator REPLICA_IP/32 scram-sha-256
9
10# Create replication role
11CREATE USER replicator REPLICATION LOGIN PASSWORD 'replpass';
12
13# On replica: clone and start
14pg_basebackup -h PRIMARY_IP -U replicator -D /var/lib/postgresql/16/main   -P -Xs -R
15sudo systemctl start postgresql
16
17# Verify replication lag on primary
18SELECT client_addr, state,
19  pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag
20FROM pg_stat_replication;

Security & Row-Level Security

SQL

1-- Row-level security: users only see their own data
2ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
3
4CREATE POLICY orders_isolation ON orders
5  USING (user_id = current_setting('app.user_id')::uuid);
6
7-- Set context at connection time (application layer)
8SET app.user_id = '123e4567-e89b-12d3-a456-426614174000';
9
10-- Least-privilege roles
11CREATE ROLE app_readonly;
12GRANT CONNECT ON DATABASE appdb TO app_readonly;
13GRANT USAGE ON SCHEMA public TO app_readonly;
14GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

Backup & Recovery

Bash

1# Scheduled logical backup (cron)
20 2 * * * pg_dump -Fc -U postgres appdb > /backups/appdb_$(date +%Y%m%d).dump
3
4# Restore
5pg_restore -U postgres -d appdb_restored /backups/appdb_20250918.dump
6
7# Continuous WAL archiving (PITR)
8# postgresql.conf
9archive_mode    = on
10archive_command = 'cp %p /mnt/wal-archive/%f'
11
12# Restore to point in time
13restore_command = 'cp /mnt/wal-archive/%f %p'
14recovery_target_time = '2025-09-18 14:30:00'

Monitoring

SQL

1-- Cache hit ratio (target > 99%)
2SELECT round(100 * sum(blks_hit) / (sum(blks_hit) + sum(blks_read)), 2) AS cache_hit_pct
3FROM pg_stat_database WHERE datname = 'appdb';
4
5-- Table bloat
6SELECT tablename,
7  pg_size_pretty(pg_total_relation_size(quote_ident(tablename))) AS total_size,
8  n_dead_tup
9FROM pg_stat_user_tables
10ORDER BY n_dead_tup DESC LIMIT 10;
11
12-- Auto-vacuum status
13SELECT relname, last_autovacuum, last_autoanalyze
14FROM pg_stat_user_tables
15ORDER BY last_autovacuum NULLS FIRST LIMIT 10;