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
- 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.
- 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. - Background Writer (bgwriter): Periodically flushes modified (“dirty”) shared memory pages to disk in small batches to reduce the I/O load during checkpoint spikes.
- Checkpointer: Writes all dirty memory buffers to disk at scheduled intervals or when Write-Ahead Log (WAL) limits are reached, creating consistent restart points.
- WAL Writer: Writes and flushes Write-Ahead Log data from shared memory (
wal_buffers) to persistent disk storage (pg_wal) when transactions commit. - Autovacuum Launcher & Workers: Spawns background worker processes to clean up dead tuple versions created by updates and deletes, avoiding table bloat and updating statistics.
- 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 likeVACUUM,CREATE INDEX, andALTER 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
- TCP Connection: The client connects to
host:5432. - 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, ortrust). - Backend Process Fork: Upon successful authentication, Postmaster forks a dedicated backend process for the connection session.
- 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.
- 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
GRANTstatement. Thepostgresaccount 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.
UPDATEcommands mark the existing tuple as expired and insert a new tuple;DELETEcommands 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. - **
VACUUMandANALYZE**: VACUUM: Scans table pages to reclaim space occupied by dead tuple versions, updates the visibility map, and prevents transaction ID wraparound. Theautovacuumdaemon 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:
- Dynamic Parameters (SIGHUP): Can be updated without stopping the database cluster. Reload via
systemctl reload postgresqlorSELECT pg_reload_conf();. - 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 toscram-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]
- Install PgBouncer:
sudo apt-get install -y pgbouncer
- 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
- Populate
/etc/pgbouncer/userlist.txt:
"app_user" "SCRAM-SHA-256$4096:..."
- 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.
- Add module to
postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top
pg_stat_statements.max = 10000
- Restart PostgreSQL:
sudo systemctl restart postgresql
- Enable the extension in your database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
- 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
- The PostgreSQL service process is stopped or failed to boot.
listen_addressesinpostgresql.confis bound strictly tolocalhost, blocking external IP traffic.- Linux firewall (
ufworfirewalld) is dropping TCP packets on port 5432.
Investigation and Diagnosis
- Verify if PostgreSQL service is active:
systemctl status postgresql
pg_isready -h localhost -p 5432
- Confirm socket listener binding:
ss -tlnp | grep 5432
- 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 -tlnpshows127.0.0.1:5432but external applications fail,listen_addressesis restricted. - If
ss -tlnpshows 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
- Invalid password provided by application.
pg_hba.confrule matching order routes request to an incorrect auth method.- For local Unix sockets, system OS user does not match database user under
peerauth.
Investigation and Diagnosis
- Inspect live logs during authentication attempts:
sudo tail -f /var/log/postgresql/postgresql-16-main.log | grep -i "authentication"
- Review matching lines in
pg_hba.conftop-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.confentry:
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
- Connection count reached the configured
max_connectionslimit. - Application connection leaks (opening sessions without closing them).
- Absence of a connection pooler like PgBouncer.
Investigation and Diagnosis
- Connect via superuser (uses
superuser_reserved_connectionsreserved slots):
psql -U postgres
- 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
- 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
- Unused replication slots holding back WAL segment recycling.
- Rapid table bloat or failure to auto-vacuum.
- Database partition filled by unrotated system logs.
Investigation and Diagnosis
- Inspect OS partition space:
df -h /var/lib/postgresql
- Check database sizes:
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;
- 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
VACUUMon 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
- Open configuration file and revert erroneous setting.
- 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
- High write volume on primary exceeding standby network or disk I/O capabilities.
- Long-running analytical queries on standby blocking WAL replay (
max_standby_streaming_delay).
Investigation and Diagnosis
- 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;
- 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.confon 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
wal_levelis not set tologicalon publisher host.- Schema structure mismatch (missing table or column on subscriber).
- Missing Primary Key on table without
REPLICA IDENTITY FULLduringUPDATE/DELETE.
Investigation and Diagnosis
- 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';
- 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
- File System Permissions: Ensure database files are strictly accessible by the
postgressystem user:
sudo chmod 0700 /var/lib/postgresql/16/main
sudo chown -R postgres:postgres /var/lib/postgresql/16/main
- 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'
- Restricted Superuser Usage: Never connect production web applications using the
postgressuperuser account. Use role-based privileges (GRANT SELECT, INSERT, UPDATE). - Strong Authentication: Force
scram-sha-256authentication inpg_hba.confand reject legacytrustormd5methods.
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) |
+-------------------------------------------------------------------------+
shared_buffers: Recommended starting value is 25% of total system RAM on dedicated servers.work_mem: Set conservatively (e.g.,32MBto64MB). Becausework_memcan be allocated multiple times per complex query, setting this excessively high causes Out-Of-Memory (OOM) killer crashes under concurrency.effective_cache_size: Set to 75% of total system RAM. Informs the planner of available Linux page cache.checkpoint_completion_target: Set to0.9to 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:
hitindicates pages retrieved directly from RAM (shared_buffers), whilereadindicates 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
- Stop the PostgreSQL service:
sudo systemctl stop postgresql
- 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/
- Create
recovery.signalfile in data directory:
touch /var/lib/postgresql/16/main/recovery.signal
- 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'
- 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.
- Inspect database processes and data directory:
ss -tlnp | grep 5432
psql -U postgres -c "SELECT current_setting('data_directory');"
- Create role
lab_userand test databaselab_db:
CREATE ROLE lab_user WITH LOGIN PASSWORD 'LabPassword2026!';
CREATE DATABASE lab_db OWNER lab_user;
- 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]
- 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
- 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
- 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.
- 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!
- 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...
- Open Terminal 3 to diagnose and resolve:
- Execute lock detection query.
- Identify
blocking_pidholding 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
- PostgreSQL Official Documentation https://www.postgresql.org/docs/
- Client Authentication Reference (
pg_hba.conf) https://www.postgresql.org/docs/current/auth-pg-hba-conf.html - Server Configuration Tuning Guide https://www.postgresql.org/docs/current/runtime-config.html
- Monitoring System Statistics and Views https://www.postgresql.org/docs/current/monitoring-stats.html
- Streaming Physical Replication Setup https://www.postgresql.org/docs/current/warm-standby.html
- Logical Replication Architecture https://www.postgresql.org/docs/current/logical-replication.html
- Backup and Point-in-Time Recovery (PITR) https://www.postgresql.org/docs/current/backup.html