postgresql-devsec – Writing & Content skill for AI agents

PostgreSQL

Administer, optimize, and secure PostgreSQL databases in development and production environments.

When to Use

• You need a reliable, ACID-compliant relational database.
• Your application requires advanced features such as JSONB, full-text search, or CTEs.
• You are setting up streaming replication or point-in-time recovery.
• You need to tune an existing PostgreSQL deployment for better throughput.

Prerequisites

• Linux server (Debian/Ubuntu or RHEL-based) or Docker.
• Root or sudo access for package installation.
• Familiarity with SQL fundamentals.

Database, retention and recovery scope

Confirm exact host/database/version/role and authorized maintenance window. Restore examples with --clean destroy existing objects; restore to a new disposable database and verify data before any approved replacement. Do not delete a replica data directory merely to follow the example: identify it via server configuration, stop only the intended instance, retain a tested backup/snapshot and recovery procedure. Use protected password files/secret management instead of real passwords in SQL, conninfo or logs. Restrict remote addresses and require TLS with certificate verification; broad CIDRs and listen_addresses=* are not secure defaults. Query text and logs can contain confidential values.

Installation and Setup

# Debian / Ubuntu
sudo apt update
sudo apt install -y postgresql postgresql-contrib

# RHEL / Amazon Linux
sudo dnf install -y postgresql15-server postgresql15-contrib
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

# Verify
psql --version
sudo systemctl status postgresql

Initial User and Database Setup

# Switch to the postgres system user
sudo -u postgres psql

-- Create an application user
CREATE USER myapp WITH PASSWORD 'strong_password_here';

-- Create the database owned by that user
CREATE DATABASE mydb OWNER myapp;

-- Grant connection privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp;

-- Connect to the database and set default privileges
\c mydb
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myapp;

psql Commands Reference

\l              -- list databases
\dt             -- list tables in current database
\d+ tablename   -- describe table with storage info
\du             -- list roles
\x              -- toggle expanded output
\timing on      -- show query execution time
\i file.sql     -- execute SQL from file
\copy           -- fast client-side COPY

Configuration Tuning

Edit /etc/postgresql/15/main/postgresql.conf (path varies by OS and version).

# Connection settings
listen_addresses = 'localhost' # add only the required private interface after access/TLS review
max_connections = 200

# Memory — adjust to ~25% of total RAM for shared_buffers
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 16MB
maintenance_work_mem = 512MB

# WAL / write performance
wal_buffers = 64MB
checkpoint_completion_target = 0.9
min_wal_size = 1GB
max_wal_size = 4GB

# Planner
random_page_cost = 1.1          # lower for SSD
effective_io_concurrency = 200  # for SSD

# Logging
log_min_duration_statement = 250   # log queries slower than 250 ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

# Reload configuration without restart
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# Some settings (shared_buffers, max_connections) require a full restart
sudo systemctl restart postgresql

pg_hba.conf — Client Authentication

# /etc/postgresql/15/main/pg_hba.conf
# TYPE  DATABASE  USER      ADDRESS         METHOD
local   all       postgres                  peer
host    mydb      myapp     10.0.0.0/8      scram-sha-256
host    all       all       0.0.0.0/0       reject

sudo systemctl reload postgresql

Backup and Restore

Logical Backups with pg_dump

# Plain SQL backup
pg_dump -U myapp -h localhost mydb > /backups/mydb_$(date +%F).sql

# Custom compressed format (recommended)
pg_dump -U myapp -h localhost -Fc mydb > /backups/mydb_$(date +%F).dump

# Backup a single table
pg_dump -U myapp -h localhost -t orders -Fc mydb > /backups/orders.dump

# Restore from custom format
pg_restore -U myapp -h localhost -d mydb --clean --if-exists /backups/mydb_2025-01-15.dump

# Restore plain SQL
psql -U myapp -h localhost -d mydb < /backups/mydb_2025-01-15.sql

Physical Backups with pg_basebackup

# Full base backup (used for PITR and replica seeding)
pg_basebackup -h localhost -U replicator -D /backups/base_$(date +%F) \
  --wal-method=stream --checkpoint=fast --progress --verbose

# Verify the backup
pg_verifybackup /backups/base_2025-01-15

Streaming Replication

Primary Server

-- Create replication user
CREATE USER replicator WITH REPLICATION LOGIN PASSWORD 'repl_secret';

# postgresql.conf on primary
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB

# pg_hba.conf on primary
host replication replicator 10.0.0.0/8 scram-sha-256

Replica Server

# Stop PostgreSQL on the replica
sudo systemctl stop postgresql

# Remove existing data directory
ls /var/lib/postgresql/15/main/*  # verify this is the correct data dir first
sudo find /var/lib/postgresql/15/main -mindepth 1 -delete  # empty the data dir; snapshots must exist (see Limitations)

# Base backup from primary
sudo -u postgres pg_basebackup \
  -h 10.0.0.1 -U replicator \
  -D /var/lib/postgresql/15/main \
  --wal-method=stream --checkpoint=fast --progress

# Create standby signal file
sudo -u postgres touch /var/lib/postgresql/15/main/standby.signal

# postgresql.conf on replica
primary_conninfo = 'host=10.0.0.1 port=5432 user=replicator password=repl_secret'
hot_standby = on

sudo systemctl start postgresql

Verify Replication

-- On primary
SELECT client_addr, state, sent_lsn, replay_lsn
FROM pg_stat_replication;

-- On replica
SELECT pg_is_in_recovery();           -- should return true
SELECT pg_last_wal_receive_lsn();
SELECT pg_last_wal_replay_lsn();

The dead-tuple ratio below is a statistics estimate, not measured on-disk bloat. Zero index scans may reflect a short/reset observation window or rare critical/constraint use; never drop indexes from that metric alone. Base backup plus a finite wal_keep_size does not guarantee indefinite replica catch-up or PITR: plan WAL retention/archive, monitor lag and test restoration. See [EXPLAIN execution semantics](https://www.postgresql.org/docs/current/sql-explain.html).

Monitoring Queries

-- Active connections by state
SELECT state, COUNT(*)
FROM pg_stat_activity
GROUP BY state;

-- Long-running queries (> 30 seconds)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '30 seconds'
ORDER BY duration DESC;

-- Table bloat and dead tuples
SELECT relname,
       n_live_tup,
       n_dead_tup,
       ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Index usage statistics
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 10;

-- Cache hit ratio (should be > 99%)
SELECT ROUND(
  100.0 * sum(blks_hit) / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2
) AS cache_hit_pct
FROM pg_stat_database;

-- Database size
SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

Docker Compose Setup

# docker-compose.yml
version: "3.9"

services:
  postgres:
    image: postgres:16-alpine
    restart: unless-stopped
    ports:
      - "127.0.0.1:5432:5432"
    environment:
      POSTGRES_USER: myapp
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:?Set a unique local password}
      POSTGRES_DB: mydb
    volumes:
      - pg_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    command: >
      postgres
        -c shared_buffers=256MB
        -c work_mem=8MB
        -c maintenance_work_mem=128MB
        -c effective_cache_size=768MB
        -c log_min_duration_statement=250
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U myapp -d mydb"]
      interval: 10s
      timeout: 5s
      retries: 5

  pgbouncer:
    image: edoburu/pgbouncer:latest
    restart: unless-stopped
    ports:
      - "127.0.0.1:6432:6432"
    environment:
      DATABASE_URL: postgres://myapp:${POSTGRES_PASSWORD:?Set a unique URL-encoded password}@postgres:5432/mydb
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 500
      DEFAULT_POOL_SIZE: 40
    depends_on:
      postgres:
        condition: service_healthy

volumes:
  pg_data:

docker compose up -d
psql -h 127.0.0.1 -p 6432 -U myapp my

… (full content is in the download package)