Domain 8Data InfrastructureINTERMEDIATE

PostgreSQL Production Handbook: Architecture, Operations, Streaming & Logical Replication, and Troubleshooting

A comprehensive production handbook for PostgreSQL covering architecture, connection lifecycle, configuration, streaming and logical replication, point-in-time recovery backups, diagnostic monitoring, and systematic troubleshooting for Linux sysadmins and SREs.

Domain: Domain 8: Data Infrastructure•Updated: 2026-11-03
#linux#sysadmin#postgresql#database#sre

1. Overview

PostgreSQL is an open-source, enterprise-grade object-relational database management system (ORDBMS) engineered for data integrity, extensibility, and standards compliance. In modern production environments, PostgreSQL serves as the primary transactional storage engine for web applications, microservices, analytical systems, and geospatial databases.

Core Purpose and Production Functionality

PostgreSQL stores relational data structured within databases, schemas, tables, indexes, and integrity constraints. It guarantees full ACID (Atomicity, Consistency, Isolation, Durability) transactional semantics, ensuring that database operations remain reliable even during system crashes, network failures, or unexpected power outages. Beyond standard relational features, PostgreSQL natively supports semi-structured JSON/JSONB documents, complex data types, and custom extensions such as PostGIS for geospatial operations and pg_stat_statements for deep query profiling.

Application servers communicate with PostgreSQL over TCP network sockets (listening by default on port 5432). Because each database session requires a dedicated process on the underlying host, enterprise deployments typically deploy connection pooling middleware (such as PgBouncer) or direct connection managers alongside psql administrative sessions.

+-------------------+        TCP:5432        +-----------------------------------+
| Application Server | --------------------> | PostgreSQL Instance               |
| (or PgBouncer)    |  SCRAM-SHA-256 Auth  |  - Postmaster (Parent Process)    |
+-------------------+                        |  - Dedicated Backend Processes    |
                                             |  - Shared Buffers & WAL Engine   |
                                             +-----------------------------------+

Key Advantages and Operational Trade-offs

Aspect Advantages Operational Trade-offs / Limitations
Data Integrity & SQL Standard Strict ANSI SQL compliance; robust ACID transactions; check constraints and foreign keys. Strict typing and constraints require schema discipline and migration planning.
Extensibility Supports rich extensions (PostGIS, timescaledb, pg_stat_statements, pg_trgm). Misconfigured or unvetted extensions can cause backend instability or memory leaks.
Replication & Scaling Built-in physical streaming replication and logical row-level replication. Write operations must target the primary node; multi-master write clustering is complex.
Concurrency Model Multi-Version Concurrency Control (MVCC) enables readers to not block writers and writers to not block readers. MVCC leaves dead tuples (bloat) that require periodic VACUUM cleanup.
Process Architecture Process isolation prevents a failing query from crashing the entire database cluster. High memory overhead per client connection; requires connection poolers at scale.

2. Architecture and Mental Model

To effectively manage and troubleshoot PostgreSQL, a Linux System Administrator or SRE must understand its process hierarchy, memory layout, storage layer, and client connection lifecycle.

Process Model and Shared Memory Architecture

PostgreSQL relies on a multi-process architecture. The primary controlling process is the Postmaster (master daemon). When PostgreSQL boots, the Postmaster initializes shared memory, opens listening sockets, spawns essential background workers, and waits for incoming client connections.

graph TD
    Client[Client Application] -->|TCP Connect :5432| Postmaster[Postmaster Daemon]
    Postmaster -->|Fork Session| Backend[Backend Process PID: 1042]
    
    subgraph Memory Space [Linux Shared Memory Segment]
        SB[Shared Buffers]
        WB[WAL Buffers]
        LM[Lock Manager]
    end

    subgraph Background Workers [PostgreSQL Service Processes]
        Checkpointer[Checkpointer]
        Writer[Background Writer]
        WALWriter[WAL Writer]
        Autovacuum[Autovacuum Launcher / Workers]
        Stats[Stats Collector / Syslogger]
    end

    Backend --> SB
    Backend --> WB
    Backend --> LM
    
    Checkpointer -->|Flush Dirty Buffers| Disk[(Disk Data Directory)]
    WALWriter -->|Flush WAL| WALDisk[(WAL Files pg_wal)]

Core Processes and Background Workers

  1. Postmaster Process: The main supervisor daemon. It listens on TCP port 5432 or Unix domain sockets, validates initial TCP connection requests, checks authentication settings, and forks backend processes for client sessions.
  2. Backend Processes: For every active client connection, Postmaster spawns a dedicated backend process. This process executes incoming SQL commands, handles local memory allocation (work_mem), requests locks, reads data into shared memory buffers, and returns row results to the client.
  3. Background Writer (bgwriter): Periodically flushes modified (“dirty”) shared memory pages to disk in small batches to reduce the I/O load during checkpoint spikes.
  4. Checkpointer: Writes all dirty memory buffers to disk at scheduled intervals or when Write-Ahead Log (WAL) limits are reached, creating consistent restart points.
  5. WAL Writer: Writes and flushes Write-Ahead Log data from shared memory (wal_buffers) to persistent disk storage (pg_wal) when transactions commit.
  6. Autovacuum Launcher & Workers: Spawns background worker processes to clean up dead tuple versions created by updates and deletes, avoiding table bloat and updating statistics.
  7. Stats Collector / Syslogger: Collects activity statistics (pg_stat_activity, table access metrics) and routes system output to configured log destinations.

Memory Architecture

  • Shared Memory: Allocated once during server startup and accessible by all backend processes.

  • Shared Buffers: Caches disk pages (tables and indexes) in memory to reduce disk I/O.

  • WAL Buffers: Holds transaction log entries before writing them to disk.

  • Lock Manager: Shared state holding table, row, and predicate locks across sessions.

  • Process Memory: Allocated independently by each backend worker.

  • work_mem: Memory allocated per sort, hash table, or merge operation in a query execution plan.

  • maintenance_work_mem: Memory used for maintenance commands like VACUUM, CREATE INDEX, and ALTER TABLE.

Detailed Client Connection Lifecycle

When an application initiates a database connection, the process moves through five key operational phases:

sequenceDiagram
    autonumber
    actor Client
    participant PM as Postmaster Daemon
    participant HBA as pg_hba.conf
    participant BE as Dedicated Backend Process
    participant DB as Data Directory & Storage

    Client->>PM: 1. TCP Connection Request (Port 5432)
    PM->>HBA: 2. Check Client IP, User, & Target DB
    HBA-->>PM: 3. Return Authentication Method (e.g., SCRAM-SHA-256)
    Client->>PM: 4. Provide Credentials
    PM->>BE: 5. Authentication OK -> Fork Dedicated Backend Process
    Client->>BE: 6. Transmit SQL Query
    BE->>DB: 7. Parse, Plan, Acquire Locks, Read/Write Data
    DB-->>BE: 8. Execution Results
    BE-->>Client: 9. Return Data Rows / Status
  1. TCP Connection: The client connects to host:5432.
  2. Authentication Check: The server consults pg_hba.conf. It matches the incoming request against rules in sequential top-to-bottom order based on connection type (host/local), target database, database user, and client IP network address. It then executes the specified authentication protocol (scram-sha-256, md5, peer, or trust).
  3. Backend Process Fork: Upon successful authentication, Postmaster forks a dedicated backend process for the connection session.
  4. Query Parsing and Execution: The client sends SQL text. The backend parses the query into a syntax tree, rewrites it according to active rules, generates execution plans using planner statistics, acquires necessary table/row locks, and executes the physical plan.
  5. Result Dispatch & Connection Maintenance: Result sets stream back to the client. The backend remains open for subsequent queries or terminates when the client disconnects.

3. Core Concepts and Terminology

Key Structural & Operational Concepts

  • Databases and Schemas: A database cluster contains one or more named databases. Each database contains named schemas, which isolate tables, views, functions, and data types.
  • Roles and Permissions: PostgreSQL uses unified Roles to handle authentication and authorization. A role can act as a single user or a permission group. Privileges are granted on databases, schemas, or tables using the GRANT statement. The postgres account is a superuser that bypasses permission checks.
  • Multi-Version Concurrency Control (MVCC): Instead of locking data rows during reads, PostgreSQL keeps multiple physical versions of rows (tuples) simultaneously. UPDATE commands mark the existing tuple as expired and insert a new tuple; DELETE commands mark the tuple as dead. Old versions remain visible to concurrent transactions until no active transaction needs them.
  • Write-Ahead Logging (WAL): To guarantee durability, PostgreSQL records all data page changes to sequential log files (pg_wal) on disk before writing the changes to main table data files. If the host crashes, PostgreSQL replays the WAL to recover to a consistent state. WAL records also feed streaming physical replication and logical replication pipelines.
  • **VACUUM and ANALYZE**:
  • VACUUM: Scans table pages to reclaim space occupied by dead tuple versions, updates the visibility map, and prevents transaction ID wraparound. The autovacuum daemon handles this task in the background.
  • ANALYZE: Scans table data to compute distribution statistics for table columns. The query planner uses these statistics to choose between sequential scans, index scans, or nested loop joins.

Operational Glossary

Term Definition
Cluster A collection of databases managed by a single running PostgreSQL instance.
Tuple A single physical row version inside a table file.
Dead Tuple An outdated row version that is no longer visible to any open transaction.
LSN (Log Sequence Number) A 64-bit integer pointing to a specific byte location in the Write-Ahead Log.
Replication Slot A primary server tracking mechanism ensuring WAL segments are not deleted until standbys consume them.
PgBouncer A lightweight connection pooler for PostgreSQL that manages client connection limits.

4. Installation and Version Management

Package Installation Across Linux Distributions

PostgreSQL can be installed from native distribution repositories or the official PostgreSQL Global Development Group (PGDG) repositories for the latest features.

Debian / Ubuntu (APT)

# Import PostgreSQL official repository key and list
sudo apt-get install -y curl ca-certificates lsb-release gnupg
sudo install -d /etc/apt/keyrings
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/keyrings/postgresql.gpg

echo "deb [signed-by=/etc/apt/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# Update package lists and install PostgreSQL 16
sudo apt-get update
sudo apt-get install -y postgresql-16 postgresql-contrib-16

RHEL / Rocky Linux / AlmaLinux (DNF)

# Install PGDG repository RPM
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable built-in PostgreSQL module
sudo dnf -qy module disable postgresql

# Install PostgreSQL 16 Server
sudo dnf install -y postgresql16-server postgresql16-contrib

# Initialize database cluster on RHEL
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable --now postgresql-16

File Layout Differences Across Linux Distributions

Default paths for PostgreSQL binaries, data directories, and configuration files differ across Linux distributions:

Component Debian / Ubuntu RHEL / Rocky / AlmaLinux
Data Directory /var/lib/postgresql/<version>/main/ /var/lib/pgsql/<version>/data/ or /var/lib/pgsql/data/
Configuration Dir /etc/postgresql/<version>/main/ /var/lib/pgsql/<version>/data/
Log Directory /var/log/postgresql/ /var/lib/pgsql/<version>/data/log/
Binary Directory /usr/lib/postgresql/<version>/bin/ /usr/pgsql-<version>/bin/

Version Verification and Health Checks

Verify the running PostgreSQL version and operational status using system utilities and psql meta-commands:

# Check version using command-line binary
psql --version

# Check service readiness via TCP socket
pg_isready -h localhost -p 5432

# SQL query inside psql session
psql -U postgres -c "SELECT version();"

5. Configuration Files and Directory Structure

Configuration Reference Matrix

File / Directory Purpose Important Details
postgresql.conf Primary server parameters. Controls memory allocation, connection limits, logging, WAL levels, and replication rules.
pg_hba.conf Host-Based Authentication configuration. Defines allowed connection types, client IP ranges, target databases, users, and authentication methods.
pg_ident.conf OS-to-database user mapping file. Maps system usernames to PostgreSQL database role names when using peer authentication.
Data Directory Root file directory containing physical database files. Stores tables, indexes, transaction logs (pg_wal), system catalogs, and database state.
pg_wal/ Write-Ahead Log directory inside data path. Holds sequential transaction logs used for durability, crash recovery, and replication.
Log Directory Output path for server log files. Configured via log_directory; stores query logs, operational warnings, and error traces.

Detailed Parameter Reference: postgresql.conf

postgresql.conf governs server behavior. Settings are updated by editing the file or using ALTER SYSTEM SET parameter = 'value'; (which writes to postgresql.auto.conf).

Parameter Reload Requirements

Settings fall into two categories:

  1. Dynamic Parameters (SIGHUP): Can be updated without stopping the database cluster. Reload via systemctl reload postgresql or SELECT pg_reload_conf();.
  2. Static Parameters (Postmaster Restart): Require a complete database service restart because they allocate shared memory blocks at startup.
# ===================================================================
# PostgreSQL 16 Production Configuration Baseline (64GB RAM Server)
# ===================================================================

# --- Connections & Networking ---
listen_addresses = '*'                  # SIGHUP: Interfaces to listen on ('*' or specific IP)
port = 5432                             # Postmaster restart: Port to listen on
max_connections = 200                   # Postmaster restart: Max concurrent backends
superuser_reserved_connections = 3     # Postmaster restart: Reserved slots for postgres admin

# --- Shared & Session Memory ---
shared_buffers = 16GB                   # Postmaster restart: 25% of total system RAM
work_mem = 32MB                         # SIGHUP: Memory per sort/hash operation
maintenance_work_mem = 2GB              # SIGHUP: Memory for VACUUM, CREATE INDEX
effective_cache_size = 48GB             # SIGHUP: Planner estimate of OS cache (75% RAM)

# --- Write-Ahead Log (WAL) & Archiving ---
wal_level = replica                     # Postmaster restart: Enables streaming replication & PITR
max_wal_size = 16GB                     # SIGHUP: Soft limit for WAL size before checkpointing
min_wal_size = 2GB                      # SIGHUP: Minimum WAL footprint preserved
checkpoint_completion_target = 0.9      # SIGHUP: Spreads checkpoint I/O over 90% of interval
archive_mode = on                       # Postmaster restart: Enables continuous WAL archiving
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f' # SIGHUP: Command to save WAL segments

# --- Logging & Diagnostics ---
logging_collector = on                  # Postmaster restart: Captures stdout/stderr into log files
log_directory = 'log'                   # SIGHUP: Directory relative to data dir
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # SIGHUP: Log rotation file format
log_min_duration_statement = 250        # SIGHUP: Log statements taking longer than 250ms (in ms)
log_line_prefix = '%m [%p] %q%u@%d '    # SIGHUP: Time, PID, user, database prefix
log_statement = 'ddl'                   # SIGHUP: Log all schema modifications

# --- Autovacuum Tuning ---
autovacuum = on                         # SIGHUP: Enable autovacuum daemon
autovacuum_max_workers = 4              # Postmaster restart: Max autovacuum worker processes
autovacuum_vacuum_scale_factor = 0.05   # SIGHUP: Trigger vacuum when 5% of tuples are dead
autovacuum_analyze_scale_factor = 0.02  # SIGHUP: Trigger analyze when 2% of tuples change

Client Authentication: pg_hba.conf

The pg_hba.conf (Host-Based Authentication) file controls client access. Rules are read sequentially top-to-bottom; the first matching rule is applied.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# ----------------------------------------------------------------------
# Local Unix socket access for system administrative tasks
local   all             postgres                                peer

# Local Unix socket access for all other database users
local   all             all                                     scram-sha-256

# IPv4 local connections from loopback interface
host    all             all             127.0.0.1/32            scram-sha-256

# IPv4 application subnet access using secure SCRAM authentication
host    production_db   app_user        10.0.10.0/24            scram-sha-256

# Streaming replication user access from standby host IP address
host    replication     replicator      10.0.10.50/32           scram-sha-256

Authentication Method Reference

  • peer: Obtains the client’s OS username from the kernel and verifies it matches the requested database username. Valid for local Unix socket connections.
  • scram-sha-256: Password-based authentication using dynamic cryptographic hashing. Recommended for production network traffic.
  • md5: Older password hashing protocol. Vulnerable to replay attacks; upgrade to scram-sha-256.
  • trust: Allows connections without password verification. Never use for network addresses in production!

6. Service Management and Common Operations

Service Control Commands

Manage the PostgreSQL service using systemd or distribution-specific utilities:

Systemd (Standard Linux)

# View service operational status
systemctl status postgresql

# Start PostgreSQL service
sudo systemctl start postgresql

# Reload configuration files (postgresql.conf and pg_hba.conf) without downtime
sudo systemctl reload postgresql

# Fully restart Postmaster daemon (causes brief connection interruption)
sudo systemctl restart postgresql

# Enable automatic startup on host boot
sudo systemctl enable postgresql

Debian / Ubuntu Wrapper Commands (pg_ctlcluster)

# Check status of cluster version 16 named main
pg_ctlcluster 16 main status

# Reload configuration without restarting
sudo pg_ctlcluster 16 main reload

# Restart cluster 16 main
sudo pg_ctlcluster 16 main restart

Service Verification Commands

Confirm that PostgreSQL is active and accepting traffic:

# Test connection readiness on local host
pg_isready -h 127.0.0.1 -p 5432

# Inspect active TCP listening sockets
ss -tlnp | grep 5432

# Follow live system log output
sudo tail -f /var/log/postgresql/postgresql-16-main.log

7. Common Administrative Tasks and Workflows

Task 1: Setting Up Least-Privilege Database Roles

Create a dedicated application user and schema permissions without granting risky SUPERUSER privileges.

-- Step 1: Connect as superuser
-- psql -U postgres

-- Step 2: Create a restricted application database role with a SCRAM password
CREATE ROLE app_user WITH LOGIN PASSWORD 'SecurePassphrase2026!';

-- Step 3: Create target production database owned by app_user
CREATE DATABASE production_db OWNER app_user;

-- Step 4: Connect to target database and configure schema isolation
\c production_db

-- Revoke default public schema access to maintain tight security
REVOKE ALL ON SCHEMA public FROM PUBLIC;
CREATE SCHEMA app_schema AUTHORIZATION app_user;

-- Step 5: Grant controlled execution privileges
GRANT USAGE, CREATE ON SCHEMA app_schema TO app_user;
ALTER ROLE app_user SET search_path TO app_schema, public;

Task 2: Implementing Connection Pooling with PgBouncer

Because each PostgreSQL backend process consumes ~2–10MB of memory and causes process contention under high concurrency, deploying PgBouncer is standard operational practice.

Applications ---> PgBouncer Pooler (Port 6432) ---> PostgreSQL Server (Port 5432)
  [1000 Client Sessions]  == Pooling ==> [50 Backend Processes]
  1. Install PgBouncer:
sudo apt-get install -y pgbouncer
  1. Edit /etc/pgbouncer/pgbouncer.ini:
[databases]
production_db = host=127.0.0.1 port=5432 dbname=production_db

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 40
  1. Populate /etc/pgbouncer/userlist.txt:
"app_user" "SCRAM-SHA-256$4096:..."
  1. Start PgBouncer service:
sudo systemctl enable --now pgbouncer

Task 3: Enabling Performance Profiling with pg_stat_statements

The pg_stat_statements module tracks execution statistics for all SQL statements executed across the server.

  1. Add module to postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top
pg_stat_statements.max = 10000
  1. Restart PostgreSQL:
sudo systemctl restart postgresql
  1. Enable the extension in your database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
  1. Retrieve top slow queries ordered by total execution time:
SELECT 
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    query 
FROM pg_stat_statements 
ORDER BY total_exec_time DESC 
LIMIT 5;

8. CLI Command Reference and Cheatsheet

Terminal and Service Management

Command Purpose Important Flags / Arguments Practical Example Explanation
pg_isready Check database cluster availability. -h (host), -p (port), -U (user), -t (timeout). pg_isready -h 127.0.0.1 -p 5432 Returns zero status if PostgreSQL is running and accepting TCP connections.
systemctl Control Linux systemd services. status, start, stop, reload, restart. sudo systemctl reload postgresql Reloads postgresql.conf and pg_hba.conf without terminating active backends.
pg_ctlcluster Manage Debian/Ubuntu PostgreSQL clusters. <version> <cluster> <action>. pg_ctlcluster 16 main status Debian-specific wrapper for managing distinct cluster instances.
ss Utility to investigate network sockets. -t (tcp), -l (listen), -n (numeric), -p (processes). `ss -tlnp grep 5432`

psql Interactive Shell Meta-Commands

Meta-Command Description Example
\l List all databases inside the cluster. \l
\du List all existing database roles and granted attributes. \du
\dt List all tables within the current active schema. \dt
\d+ <table_name> Show full table structural schema, indexes, constraints, and total size. \d+ orders
\conninfo Print detailed info about the current active connection (user, host, port, socket). \conninfo
\q Cleanly terminate the interactive psql session. \q

Diagnostic SQL Commands & Introspection Views

Task / Query Purpose SQL Command Syntax
Cancel Hanging Query Intercepts running statement while keeping TCP session connected. SELECT pg_cancel_backend(<pid>);
Terminate Session Immediately kills the backend process and severs the TCP connection. SELECT pg_terminate_backend(<pid>);
Check Data Directory Path Displays current physical data directory location. SELECT current_setting('data_directory');
Query Standby Recovery Mode Returns true if node is a read-only standby server. SELECT pg_is_in_recovery();
Trigger Standby Promotion Promotes physical standby to active read-write primary. SELECT pg_promote();
Force WAL Segment Switch Forces immediate WAL roll to test archiving pipelines. SELECT pg_switch_wal();

9. Logs, Monitoring and Diagnostics

Log File Locations and Systemd Integration

  • Debian / Ubuntu: /var/log/postgresql/postgresql-<version>-main.log
  • RHEL / Rocky Linux: /var/lib/pgsql/<version>/data/log/postgresql-<day>.log
  • Systemd Journal Direct Access:
sudo journalctl -u postgresql -n 100 --no-pager

Key Administrative Monitoring Views

1. Session Activity Monitoring (pg_stat_activity)

Inspect active, non-idle backend sessions:

SELECT 
    pid, 
    usename, 
    datname, 
    state, 
    wait_event_type, 
    wait_event, 
    now() - query_start AS duration, 
    left(query, 60) AS query_snippet
FROM pg_stat_activity 
WHERE state != 'idle' 
ORDER BY duration DESC;

2. Lock Contention and Blocked Queries (pg_locks)

Identify queries blocked by other transactions holding table or row locks:

SELECT 
    blocked.pid     AS blocked_pid,
    blocked.usename AS blocked_user,
    blocking.pid    AS blocking_pid,
    blocking.usename AS blocking_user,
    blocked.query   AS blocked_statement,
    blocking.query  AS blocking_statement
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks blk ON blk.locktype = bl.locktype 
  AND blk.database IS NOT DISTINCT FROM bl.database
  AND blk.relation IS NOT DISTINCT FROM bl.relation
  AND blk.page IS NOT DISTINCT FROM bl.page
  AND blk.tuple IS NOT DISTINCT FROM bl.tuple
  AND blk.transactionid IS NOT DISTINCT FROM bl.transactionid
  AND blk.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = blk.pid AND blk.granted;

3. Database Sizes and Disk Footprint

Evaluate physical disk usage per database:

SELECT 
    datname, 
    pg_size_pretty(pg_database_size(datname)) AS db_size 
FROM pg_database 
ORDER BY pg_database_size(datname) DESC;

4. Table Bloat and Vacuum Health (pg_stat_user_tables)

Locate tables with large dead tuple counts requiring autovacuum intervention:

SELECT 
    schemaname, 
    relname, 
    n_dead_tup, 
    n_live_tup, 
    round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 2) AS dead_ratio,
    last_vacuum, 
    last_autovacuum 
FROM pg_stat_user_tables 
ORDER BY n_dead_tup DESC 
LIMIT 10;

10. Troubleshooting Guide and Common Failure Scenarios

Problem 1: Connection Refused

Symptoms

Applications report psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused.

Possible Causes

  1. The PostgreSQL service process is stopped or failed to boot.
  2. listen_addresses in postgresql.conf is bound strictly to localhost, blocking external IP traffic.
  3. Linux firewall (ufw or firewalld) is dropping TCP packets on port 5432.

Investigation and Diagnosis

  1. Verify if PostgreSQL service is active:
systemctl status postgresql
pg_isready -h localhost -p 5432
  1. Confirm socket listener binding:
ss -tlnp | grep 5432
  1. Read the end of system log to detect crash loops:
tail -n 50 /var/log/postgresql/postgresql-16-main.log

Root Cause Identification

  • If ss -tlnp shows 127.0.0.1:5432 but external applications fail, listen_addresses is restricted.
  • If ss -tlnp shows no listener output, PostgreSQL is stopped or failing during initialization.

Resolution

  • Edit /etc/postgresql/16/main/postgresql.conf:
listen_addresses = '*'
  • Reload or restart service:
sudo systemctl restart postgresql
  • Open firewall rule (for firewalld):
sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reload

Verification

Confirm connection from remote host: pg_isready -h 10.0.10.15 -p 5432.

Prevention

Automate service monitoring with systemd auto-restart policies and monitor TCP listeners with Prometheus blackbox_exporter.


Problem 2: Password Authentication Failed

Symptoms

Clients receive FATAL: password authentication failed for user "app_user".

Possible Causes

  1. Invalid password provided by application.
  2. pg_hba.conf rule matching order routes request to an incorrect auth method.
  3. For local Unix sockets, system OS user does not match database user under peer auth.

Investigation and Diagnosis

  1. Inspect live logs during authentication attempts:
sudo tail -f /var/log/postgresql/postgresql-16-main.log | grep -i "authentication"
  1. Review matching lines in pg_hba.conf top-to-bottom.

Root Cause Identification

Log entries like no pg_hba.conf entry for host "10.0.10.5", user "app_user" indicate missing host authorization. Logs stating password authentication failed indicate credential mismatch or method issue.

Resolution

  • Fix pg_hba.conf entry:
host  production_db  app_user  10.0.10.0/24  scram-sha-256
  • Reload server configuration without restarting:
sudo systemctl reload postgresql
  • Reset database user password if needed:
ALTER ROLE app_user WITH PASSWORD 'NewVerifiedPassword2026!';

Verification

Test authentication manually:

psql -h 127.0.0.1 -U app_user -d production_db -W

Prevention

Store database secrets in secure vaults (e.g., HashiCorp Vault) and manage pg_hba.conf via Ansible templates.


Problem 3: Too Many Connections

Symptoms

Application logs report FATAL: sorry, too many clients already.

Possible Causes

  1. Connection count reached the configured max_connections limit.
  2. Application connection leaks (opening sessions without closing them).
  3. Absence of a connection pooler like PgBouncer.

Investigation and Diagnosis

  1. Connect via superuser (uses superuser_reserved_connections reserved slots):
psql -U postgres
  1. Count active sessions per state:
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

Root Cause Identification

If hundreds of connections display state = 'idle', the application server is creating unpooled connections and leaking sessions.

Resolution

  • Terminate idle sessions immediately to restore service:
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'idle' 
  AND state_change < now() - INTERVAL '15 minutes';
  • Long-term solution: Deploy PgBouncer in front of PostgreSQL rather than arbitrarily inflating max_connections (which exhausts RAM).

Verification

Confirm free connection pool slots:

SELECT count(*) FROM pg_stat_activity;
SHOW max_connections;

Prevention

Configure application-side connection pooling limits and enforce transaction-level pooling via PgBouncer.


Problem 4: Queries Hanging / Lock Waits

Symptoms

API endpoints time out; application threads hang indefinitely waiting on database responses.

Possible Causes

Long-running transactions holding exclusive schema or row locks while other sessions attempt conflicting operations (e.g., ALTER TABLE or update of identical rows).

Investigation and Diagnosis

  1. Execute the lock blocker diagnostic query:
SELECT 
    blocked.pid AS blocked_pid, 
    blocking.pid AS blocking_pid,
    blocked.query AS blocked_query, 
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks blk ON blk.locktype = bl.locktype 
  AND blk.database IS NOT DISTINCT FROM bl.database
  AND blk.relation IS NOT DISTINCT FROM bl.relation
  AND blk.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = blk.pid AND blk.granted;

Root Cause Identification

The blocking_pid identifies the session causing the queue. Inspect its duration and query text.

Resolution

  • Cancel the blocking statement non-destructively:
SELECT pg_cancel_backend(<blocking_pid>);
  • If unresponsive, forcefully terminate the session:
SELECT pg_terminate_backend(<blocking_pid>);

Verification

Verify no blocked queries remain:

SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock';

Prevention

Set explicit lock and statement timeouts in postgresql.conf:

lock_timeout = '5s'
statement_timeout = '30s'
idle_in_transaction_session_timeout = '60s'

Problem 5: Disk Full / No Space Left on Device

Symptoms

PostgreSQL stops writing data; logs report PANIC: could not write to file "pg_wal/...": No space left on device.

Possible Causes

  1. Unused replication slots holding back WAL segment recycling.
  2. Rapid table bloat or failure to auto-vacuum.
  3. Database partition filled by unrotated system logs.

Investigation and Diagnosis

  1. Inspect OS partition space:
df -h /var/lib/postgresql
  1. Check database sizes:
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;
  1. Check for inactive replication slots accumulating WAL:
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_kept
FROM pg_replication_slots;

Root Cause Identification

If pg_replication_slots shows active = false with multiple gigabytes held in wal_kept, an inactive standby is exhausting disk space.

Resolution

  • Do not run VACUUM on a completely full disk (it requires temporary disk workspace).
  • Free space by clearing old logs in /var/log/postgresql/.
  • Drop inactive replication slots:
SELECT pg_drop_replication_slot('stale_standby_slot');

Verification

Confirm available disk space: df -h /var/lib/postgresql.

Prevention

Set up Prometheus alerts for partition space (<20% free) and configure WAL max limits:

max_slot_wal_keep_size = 10GB

Problem 6: Server Won’t Start After Configuration Change

Symptoms

Running systemctl start postgresql fails with Job for postgresql.service failed.

Possible Causes

Invalid parameter name, syntax error, or out-of-range memory value in postgresql.conf.

Investigation and Diagnosis

Check log output directly:

sudo tail -n 50 /var/log/postgresql/postgresql-16-main.log

Root Cause Identification

Log files explicitly cite the syntax failure (e.g., FATAL: configuration file "/etc/postgresql/16/main/postgresql.conf" contains errors: line 140: bad value for memory parameter).

Resolution

  1. Open configuration file and revert erroneous setting.
  2. Start service: sudo systemctl start postgresql.

Verification

Ensure service is active: systemctl status postgresql.

Prevention

Keep postgresql.conf under Git version control before performing operational edits.


Problem 7: Streaming Replication Lag Growing

Symptoms

Read-only standbys return outdated data; primary logs report growing WAL retention.

Possible Causes

  1. High write volume on primary exceeding standby network or disk I/O capabilities.
  2. Long-running analytical queries on standby blocking WAL replay (max_standby_streaming_delay).

Investigation and Diagnosis

  1. Query streaming status on Primary:
SELECT 
    application_name, 
    client_addr, 
    state, 
    sync_state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;
  1. Check position on Standby:
SELECT 
    pg_last_wal_receive_lsn(), 
    pg_last_wal_replay_lsn(),
    pg_last_wal_receive_lsn() - pg_last_wal_replay_lsn() AS unapplied_bytes;

Root Cause Identification

If pg_last_wal_receive_lsn() matches primary’s current LSN but pg_last_wal_replay_lsn() lags behind, the standby is bottlenecked on local CPU/disk replay or blocked by long queries.

Resolution

  • Enable hot standby feedback in postgresql.conf on standby:
hot_standby_feedback = on
  • Terminate long analytical queries on standby if blocking recovery.

Verification

Re-run lag query on primary to verify replay_lag_bytes decreases toward 0.

Prevention

Isolate heavy analytical reporting workloads to dedicated non-HA read standbys.


Problem 8: Logical Subscription Not Replicating

Symptoms

Data inserted on the publisher table does not appear on subscriber table.

Possible Causes

  1. wal_level is not set to logical on publisher host.
  2. Schema structure mismatch (missing table or column on subscriber).
  3. Missing Primary Key on table without REPLICA IDENTITY FULL during UPDATE/DELETE.

Investigation and Diagnosis

  1. Query subscription status on Subscriber:
SELECT subname, pid, received_lsn, last_msg_receipt FROM pg_stat_subscription;
SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel WHERE srsubstate != 'r';
  1. Check subscriber system logs for error details.

Root Cause Identification

Log entry ERROR: logical replication target relation "orders" is missing primary key indicates replica identity issues.

Resolution

  • Alter table identity on publisher:
ALTER TABLE orders SET REPLICA IDENTITY FULL;
  • Resynchronize subscription:
ALTER SUBSCRIPTION my_sub REFRESH PUBLICATION;

Verification

Verify all tables show state r (ready) in pg_subscription_rel.

Prevention

Ensure schema migrations are executed on both publisher and subscriber prior to logical publication updates.


11. Security and Best Practices

System and Access Security Baseline

  1. File System Permissions: Ensure database files are strictly accessible by the postgres system user:
sudo chmod 0700 /var/lib/postgresql/16/main
sudo chown -R postgres:postgres /var/lib/postgresql/16/main
  1. Network Encryption (TLS/SSL): Encrypt all network connections in postgresql.conf:
ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
  1. Restricted Superuser Usage: Never connect production web applications using the postgres superuser account. Use role-based privileges (GRANT SELECT, INSERT, UPDATE).
  2. Strong Authentication: Force scram-sha-256 authentication in pg_hba.conf and reject legacy trust or md5 methods.

12. Performance and Resource Optimisation

Critical Parameters for Memory and I/O Tuning

+-------------------------------------------------------------------------+
| SYSTEM RAM Allocation Strategy (e.g., 64 GB Total System Memory)        |
+-------------------------------------------------------------------------+
| Shared Buffers (25%)          | OS Page Cache & Planner Estimate (75%)  |
| =====================         | ======================================= |
|  16 GB Allocated to           |  48 GB Reserved for OS Page Caches      |
|  PostgreSQL Shared Buffers    |  (effective_cache_size)                 |
+-------------------------------------------------------------------------+
| Process Session Memory: work_mem (32MB) * max_connections (200)         |
+-------------------------------------------------------------------------+
  1. shared_buffers: Recommended starting value is 25% of total system RAM on dedicated servers.
  2. work_mem: Set conservatively (e.g., 32MB to 64MB). Because work_mem can be allocated multiple times per complex query, setting this excessively high causes Out-Of-Memory (OOM) killer crashes under concurrency.
  3. effective_cache_size: Set to 75% of total system RAM. Informs the planner of available Linux page cache.
  4. checkpoint_completion_target: Set to 0.9 to smooth out disk write spikes across checkpoint intervals.

Query Plan Analysis with EXPLAIN ANALYZE

Use EXPLAIN ANALYZE to inspect actual execution times and physical scan paths:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT customer_id, count(*) 
FROM orders 
WHERE order_date >= '2026-01-01' 
GROUP BY customer_id;

Diagnostic Execution Plan Interpretation

  • Sequential Scan (Seq Scan): The engine scans every page of the table from disk. Indicates a missing index or un-updated statistics.
  • Index Scan: The engine uses a B-Tree index to look up matching rows.
  • Buffers: shared hit=42 read=5: hit indicates pages retrieved directly from RAM (shared_buffers), while read indicates physical disk fetches.

13. Backup, Recovery and Maintenance

Replication is a high-availability mechanism, not a backup solution. An accidental DROP TABLE executed on a primary node immediately replicates to all standbys. Independent backups are essential.

Backup Strategy Overview

graph LR
    subgraph Logical Backups
        A[pg_dump] -->|SQL/Custom Format| B(Single Database Snapshot)
        C[pg_dumpall] -->|SQL Format| D(All Databases + Global Roles)
    end
    
    subgraph Physical Point-In-Time Recovery PITR
        E[pg_basebackup] -->|Filesystem Snapshot| F(Base Backup)
        G[WAL Archiving] -->|Continuous Stream| H(WAL Archive Storage)
        F + H --> I[Complete Server Recovery to any Second]
    end

1. Logical Backups (pg_dump & pg_dumpall)

Logical backups generate SQL scripts or compressed archives representing database structures and rows. Best for smaller databases, schema exports, or cross-version upgrades.

# Export single database to compressed custom format (recommended)
pg_dump -h localhost -U postgres -Fc production_db > production_db_$(date +%F).dump

# Export global cluster items (roles, tablespaces, permissions)
pg_dumpall -h localhost -U postgres --globals-only > global_roles.sql

# Restore database from custom format using pg_restore
pg_restore -h localhost -U postgres -d production_db production_db_2026-11-03.dump

2. Physical Backups (pg_basebackup)

pg_basebackup takes a consistent, point-in-time physical copy of the data directory over the PostgreSQL streaming protocol while the server is actively running.

# Take a physical base backup with progress output and stream WAL segments
pg_basebackup -h 10.0.10.15 -D /var/lib/postgresql/backups/base_backup_01 -U replicator -Fp -Xs -P

3. Continuous Archiving and Point-In-Time Recovery (PITR)

PITR allows an administrator to restore a database cluster to its state at any precise timestamp.

Configuration for PITR (postgresql.conf)

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'

PITR Restoration Procedure

  1. Stop the PostgreSQL service:
sudo systemctl stop postgresql
  1. Clear the crashed data directory and restore physical base backup:
rm -rf /var/lib/postgresql/16/main/*
cp -r /var/lib/postgresql/backups/base_backup_01/* /var/lib/postgresql/16/main/
  1. Create recovery.signal file in data directory:
touch /var/lib/postgresql/16/main/recovery.signal
  1. Configure recovery target parameters in postgresql.conf:
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-11-03 14:30:00'
recovery_target_action = 'promote'
  1. Start PostgreSQL service:
sudo systemctl start postgresql

Enterprise Backup Tooling

For production workloads, use dedicated enterprise tools rather than manual scripts:

  • pgBackRest: Supports parallel backups, differential and incremental backups, compression, and automated retention policies.
  • Barman: Backup and Recovery Manager developed for enterprise PostgreSQL environments.

14. Automation and Integration

Systemd Timer for Automated Logical Backups

Automate daily backups cleanly using standard systemd primitives instead of cron.

1. Service File (/etc/systemd/system/pg-backup.service)

[Unit]
Description=Automated PostgreSQL Logical Database Backup
After=postgresql.service

[Service]
Type=oneshot
User=postgres
ExecStart=/usr/bin/pg_dump -Fc production_db -f /var/lib/postgresql/backups/prod_db_%Y-%m-%d.dump

2. Timer File (/etc/systemd/system/pg-backup.timer)

[Unit]
Description=Run PostgreSQL Backup Daily at 2:00 AM

[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true

[Install]
WantedBy=timers.target

Enable timer:

sudo systemctl daemon-reload
sudo systemctl enable --now pg-backup.timer

15. Practical Labs and Exercises

Lab 1: System Inspection, Role Creation, and Connection Auditing

Objective: Verify database cluster configuration, create an operational role, and validate connection logs.

  1. Inspect database processes and data directory:
ss -tlnp | grep 5432
psql -U postgres -c "SELECT current_setting('data_directory');"
  1. Create role lab_user and test database lab_db:
CREATE ROLE lab_user WITH LOGIN PASSWORD 'LabPassword2026!';
CREATE DATABASE lab_db OWNER lab_user;
  1. Test authentication and verify session details:
psql -h 127.0.0.1 -U lab_user -d lab_db -c "\conninfo"

Lab 2: Physical Streaming Replication Setup

Objective: Configure a primary server and build a standby node using pg_basebackup.

graph LR
    PrimaryNode[Primary Node IP: 10.0.10.15] -- Streaming WAL --> StandbyNode[Standby Node IP: 10.0.10.50]
  1. On Primary Node (10.0.10.15):
  • Configure postgresql.conf:
wal_level = replica
max_wal_senders = 10
  • Add access rule in pg_hba.conf:
host replication replicator 10.0.10.50/32 scram-sha-256
  • Create replication role:
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'Replicate2026!';
  • Reload primary: sudo systemctl reload postgresql
  1. On Standby Node (10.0.10.50):
  • Stop default service and clear data directory:
sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main/*
  • Clone primary data cluster using pg_basebackup:
sudo -u postgres pg_basebackup -h 10.0.10.15 -D /var/lib/postgresql/16/main -U replicator -P -R

(Note: The -R flag writes primary_conninfo into configuration and creates standby.signal).

  • Start standby service:
sudo systemctl start postgresql
  1. Validation:
  • Verify recovery state on standby (must return true):
SELECT pg_is_in_recovery();
  • Query replication status on primary:
SELECT application_name, client_addr, state, sync_state FROM pg_stat_replication;

Lab 3: Troubleshooting Lock Contention

Objective: Identify and clear a blocked transaction holding database locks.

  1. Open Terminal 1 and start a transaction holding a table lock:
psql -U postgres -d production_db
BEGIN;
LOCK TABLE orders IN EXCLUSIVE MODE;
-- Keep session open without committing!
  1. Open Terminal 2 and attempt an update:
psql -U postgres -d production_db
UPDATE orders SET status = 'shipped' WHERE id = 1;
-- Query hangs waiting for lock...
  1. Open Terminal 3 to diagnose and resolve:
  • Execute lock detection query.
  • Identify blocking_pid holding the lock.
  • Cancel blocking backend:
SELECT pg_cancel_backend(<blocking_pid>);
  • Confirm Terminal 2 unblocks.

16. Quick Reference and Cheat Sheet

Configuration Files Quick Reference

File Default Location (Debian/Ubuntu) Key Purpose
postgresql.conf /etc/postgresql/<ver>/main/ Core server settings (memory, WAL, logging).
pg_hba.conf /etc/postgresql/<ver>/main/ Host-Based Client Authentication rules.
pg_ident.conf /etc/postgresql/<ver>/main/ Maps OS usernames to database roles.

Log File Quick Reference

System Default Path
Debian / Ubuntu /var/log/postgresql/postgresql-<ver>-main.log
RHEL / Rocky /var/lib/pgsql/<ver>/data/log/

Operational CLI Commands Quick Reference

Task Command
Check TCP Listener `ss -tlnp
Test Service Readiness pg_isready -h localhost -p 5432
Reload Configuration sudo systemctl reload postgresql
Clone Standby Server pg_basebackup -h <primary> -D <path> -U replicator -P -R
Take Logical Dump pg_dump -Fc dbname > db.dump
Restore Logical Dump pg_restore -d dbname db.dump

Common Diagnostic SQL Statements

-- View non-idle session activity
SELECT pid, usename, state, wait_event_type, query FROM pg_stat_activity WHERE state != 'idle';

-- Terminate backend session
SELECT pg_terminate_backend(<pid>);

-- Check streaming replication lag on primary
SELECT application_name, state, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes FROM pg_stat_replication;

-- Check database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;

Network Ports and Security Rules

Port Protocol Purpose Access Boundaries
5432 TCP PostgreSQL Primary Listener Restrict to application subnets & poolers via firewall.
6432 TCP PgBouncer Middleware Pooler Expose to application servers; keep 5432 local.

17. Further Reading and References

  1. PostgreSQL Official Documentation https://www.postgresql.org/docs/
  2. Client Authentication Reference (pg_hba.conf) https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
  3. Server Configuration Tuning Guide https://www.postgresql.org/docs/current/runtime-config.html
  4. Monitoring System Statistics and Views https://www.postgresql.org/docs/current/monitoring-stats.html
  5. Streaming Physical Replication Setup https://www.postgresql.org/docs/current/warm-standby.html
  6. Logical Replication Architecture https://www.postgresql.org/docs/current/logical-replication.html
  7. Backup and Point-in-Time Recovery (PITR) https://www.postgresql.org/docs/current/backup.html