PostgreSQL Replication & Observability: Building It Before Production Breaks

When running multi-tenant self-hosted infrastructure with production workloads like Authentik, Harbor, Outline, and Semaphore, database outages aren't just minor hiccups, they take down your entire ecosystem.

Most PostgreSQL replication tutorials show you how to start streaming data, but they skip what happens when things go wrong in production:

1. The Primary Disk Fills Up: A disconnected replica causes unmonitored Write-Ahead Logs (WAL) to pile up on the primary host until the disk hits 100% and Postgres crashes.
2. Silent Replication Lag: Replicas fall minutes behind without anyone noticing until a failover leads to silent data loss.
3. Connection & I/O Starvation: Slow queries lock up backend connections (max_connections), thrashing disk I/O and causing application timeouts.

In this guide, we'll build a production-hardened PostgreSQL Hot Standby Streaming Replication setup and Grafana Observability Dashboard using prometheuscommunity/postgres-exporter and Grafana Alloy, stopping these exact failure modes before they break production.

1. Architecture Overview

The database topology consists of a Primary Node (postgres-0) running PostgreSQL 16 on port 5432 and a Hot Standby Replica Node (last-labs) running on port 5433.

Highlights of the Architecture:

• Physical Streaming Replication: Uses PostgreSQL's built-in Write-Ahead Log (WAL) streaming for block-level synchronization.
• Replication Slots (replica1_slot): Guarantees that the primary node retains necessary WAL segments even if the replica experiences network latency or temporary downtime.
• Sidecar Exporter Pattern: Both primary and replica run postgres-exporter on port 9187.
• Grafana Alloy Scraper: Grafana Alloy scrapes metrics every 15 seconds and pushes them to Prometheus for centralized Grafana visualization.

2. Step-by-Step Setup Guide

Follow these exact steps to configure streaming replication from scratch.

Step 1: Configure the Primary Database (postgres-0)

On the Primary Node, ensure PostgreSQL is configured to allow replication connections and WAL streaming.

1. Enable WAL & Stat Statements (postgresql.conf)

Add or update the following settings in your primary database postgresql.conf:

# Replication Settings
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
hot_standby_feedback = on

# Performance Tracking & Exporter Extensions
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

2. Allow Replica Connection (pg_hba.conf)

Allow the replication user to connect over the network from the replica's IP address (replace 10.1.1.0/24 with your network subnet):

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     postgres        10.1.1.0/24             md5
host    all             postgres        10.1.1.0/24             md5

3. Create Replication Slot & User (SQL)

Run the following SQL commands on the primary node:

-- Ensure replication user has appropriate privileges
ALTER USER replica WITH REPLICATION;

-- Create physical replication slot manually (or let the replica script create it)
SELECT pg_create_physical_replication_slot('replica1_slot');

Step 2: Configure the Replica Bootstrap Script (setup-replica.sh)

On the Replica Node, create an entrypoint script setup-replica.sh. This script handles container startup, verifies volume permissions, waits for the primary node to accept connections, creates the replication slot if missing, and runs pg_basebackup:

#!/bin/bash
set -e

MASTER_HOST=${MASTER_HOST:-10.1.1.57}
MASTER_PORT=${MASTER_PORT:-5432}
REPLICATION_USER=${REPLICATION_USER:-replica}
REPLICATION_PASSWORD=${REPLICATION_PASSWORD:-your_secure_password}
REPLICA_SLOT_NAME=${REPLICA_SLOT_NAME:-replica1_slot}

PGDATA="${PGDATA:-/var/lib/postgresql/data}"
export PGPASSWORD=${REPLICATION_PASSWORD}

echo "==> [Postgres Replica] Starting bootstrap from primary: ${MASTER_HOST}:${MASTER_PORT}"

# Fix ownership on mounted volume
echo "==> Fixing ownership of /var/lib/postgresql..."
chown -R postgres:postgres /var/lib/postgresql

# Wait for primary node readiness
echo "==> Waiting for primary at ${MASTER_HOST}:${MASTER_PORT}..."
until psql -h "${MASTER_HOST}" -p "${MASTER_PORT}" -U "${REPLICATION_USER}" -d postgres -c '\q' 2>/dev/null; do
  sleep 3
done
echo "==> Primary is reachable."

# Helper function to create replication slot on primary if missing
ensure_replication_slot() {
    echo "==> Ensuring replication slot '${REPLICA_SLOT_NAME}' exists on primary..."
    local exists
    exists=$(psql -h "${MASTER_HOST}" -p "${MASTER_PORT}" -U "${REPLICATION_USER}" -d postgres -tAc \
      "SELECT 1 FROM pg_replication_slots WHERE slot_name='${REPLICA_SLOT_NAME}'")
    if [ "$exists" != "1" ]; then
        echo "==> Slot not found, creating physical slot..."
        psql -h "${MASTER_HOST}" -p "${MASTER_PORT}" -U "${REPLICATION_USER}" -d postgres -c \
          "SELECT pg_create_physical_replication_slot('${REPLICA_SLOT_NAME}');"
    else
        echo "==> Slot already exists, reusing it."
    fi
}

run_base_backup() {
    ensure_replication_slot

    # -R creates standby.signal and writes primary_conninfo into postgresql.auto.conf
    # -S attaches the physical replication slot
    pg_basebackup -h "${MASTER_HOST}" -p "${MASTER_PORT}" -D "${PGDATA}" \
      -U "${REPLICATION_USER}" -vP -R -S "${REPLICA_SLOT_NAME}"
}

# Bootstrap: Run base backup if data directory is uninitialized
if [ -f "${PGDATA}/standby.signal" ]; then
    echo "==> standby.signal found. Replica already initialized."
elif [ -z "$(ls -A "${PGDATA}" 2>/dev/null)" ]; then
    echo "==> Data directory empty. Taking base backup..."
    run_base_backup
else
    echo "==> Partial/failed backup detected. Cleaning up and retrying..."
    rm -rf "${PGDATA:?}"/*
    run_base_backup
fi

# Hand over to Postgres entrypoint with pg_stat_statements enabled
exec docker-entrypoint.sh postgres -c shared_preload_libraries=pg_stat_statements

Make sure to mark the script as executable:

chmod +x setup-replica.sh

Step 3: Deploy the Replica & Exporter (docker-compose.yml)

Create the docker-compose.yml file on the replica host:

services:
  postgres:
    image: postgres:16-alpine
    container_name: postgres-replica
    restart: unless-stopped
    environment:
      - MASTER_HOST=10.1.1.57
      - MASTER_PORT=5432
      - REPLICATION_USER=replica
      - REPLICATION_PASSWORD=your_secure_password
      - POSTGRES_PASSWORD=your_secure_password
      - POSTGRES_USER=postgres
    entrypoint: ["bash", "/setup-replica.sh"]
    ports:
      - "5433:5432"
    volumes:
      - postgres_data:/var/lib/postgresql
      - ./setup-replica.sh:/setup-replica.sh:ro
    logging:
      driver: "json-file"
      options:
        max-size: "10m"
        max-file: "3"

  postgres-exporter:
    image: prometheuscommunity/postgres-exporter:latest
    container_name: postgres-exporter
    restart: unless-stopped
    command: ["--auto-discover-databases", "--collector.stat_statements"]
    environment:
      - DATA_SOURCE_NAME=postgresql://postgres:your_secure_password@postgres:5432/postgres?sslmode=disable
    ports:
      - "9187:9187"
    depends_on:
      - postgres
    logging:
      driver: "json-file"
      options:
        max-size: "10m"
        max-file: "3"

volumes:
  postgres_data:
    name: postgres_data

Start the replica:

docker compose up -d

Step 4: Configure Metrics Scraping with Grafana Alloy

Add a scrape job target inside your Grafana Alloy configuration file (config.alloy):

prometheus.scrape "postgres_exporter" {
  targets = [
    { "__address__" = "postgres-0:9187", "instance" = "postgres-0" },
    { "__address__" = "last-labs:9187",  "instance" = "last-labs" },
  ]
  forward_to = [prometheus.remote_write.default.receiver]
  scrape_interval = "15s"
}

Step 5: Verify Replication Status

On the Primary Node (postgres-0):

Execute this query to confirm active streaming:

SELECT client_addr, application_name, state, sync_state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

Expected Output:

 client_addr | application_name |   state   | sync_state | write_lag | flush_lag | replay_lag 
-------------+------------------+-----------+------------+-----------+-----------+------------
 10.1.1.88   | walreceiver      | streaming | async      | 00:00:00  | 00:00:00  | 00:00:00

On the Replica Node (last-labs):

Execute this query to verify lag timestamp:

SELECT 
  pg_is_in_recovery() AS is_replica,
  now() - pg_last_xact_replay_timestamp() AS replication_lag;

3. The Grafana Observability Dashboard

With the cluster up and streaming, I designed a multi-panel PostgreSQL Overviews dashboard in Grafana divided into 5 critical operational domains:

1. Cluster Overview & Core Health
2. Throughput & Database Sizes
3. Replication & WAL Lag
4. WAL Disk Usage & Background Writer Activity
5. Slow Queries & IO Diagnostics (pg_stat_statements)

Domains 1 & 2: Cluster Health, Core Metrics & Database Sizes

The top section monitors immediate system status across all database instances (postgres-0 and last-labs):

• PostgreSQL Status: UP status for both primary (postgres-0) and replica (last-labs).
• Active Backends vs max_connections: Tracks connection pool usage percentage.
• Buffer Cache Hit Ratio: Displays 100.0% hit ratio, confirming reads are served directly from RAM.
• Deadlocks & Recovery Conflicts: Instant alerts if lock contention occurs or if replica queries conflict with primary WAL replay .
• Throughput & Row Activity: Visualizes commit rates and fetch operations.
• Database Sizes: Highlights individual database sizes.

Domains 3 & 4: Replication Telemetry & WAL Health

This is the central panel for monitoring multi-node state and replication health.

• Server Roles: postgres-0 is auto-detected as Primary, and last-labs as Replica.
• Real-Time Replication Lag: Monitored live at 0 seconds, with historical graphs capturing catch-up times during heavy write batches.
• Replication Slots Active: Displays active streaming slot.
• Slot WAL Lag (Bytes): Real-time monitoring comparing sender, receiver, and unreceived log volume.
• Slot Safe WAL Size Remaining: Tracks remaining safety buffer before max WAL retention limit.
• WAL Size on Disk & Background Writer: Tracks physical WAL volume and background dirty buffer clean rates.

Domain 5: Slow Queries & IO Diagnostics (pg_stat_statements)

By enabling PostgreSQL's pg_stat_statements extension, the bottom panel pinpoints query performance bottlenecks:

• Top 10 Queries by Execution Time (5m Rate): Highlights cumulative time hogging database resources.
• Top 10 Queries by Mean Latency: Identifies single-call latency bottlenecks.
• Top 10 Queries by Calls/sec: Shows top callers.
• Block I/O Time by Database: Breaks down time spent waiting on disk read/write calls.

4. Key Takeaways & Operational Learnings

1. Always set max_slot_wal_keep_size: A disconnected replica using replication slots will cause the primary to hold onto WAL files indefinitely. Setting a safe limit (e.g., 50 GiB) protects the primary node from disk full outages.
2. Monitor buffers clean/sec vs alloc/sec: If backend processes perform direct writes, your disk I/O latency will spike. Tuning bgwriter_lru_maxpages based on dashboard telemetry keeps database response times consistent.
3. Combined Observability: Combining replica lag metrics with pg_stat_statements latency metrics gives immediate clarity when a burst of heavy writes on primary affects replica streaming throughput.

Conclusion

Replication alone isn't resilience. A hot standby with no visibility into lag, WAL growth, or slow queries just delays the outage instead of preventing it. The dashboard is what turns "the replica is probably fine" into "the replica is 0 seconds behind and I have the graph to prove it."

This setup runs on plain Postgres tooling: pg_basebackup, a physical replication slot, and a few docker-compose files. No operator, no CRDs, nothing to patch or upgrade beyond Postgres itself. For a self-hosted homelab running Authentik, Harbor, Outline, and Semaphore, that tradeoff makes sense. At a larger scale, or with a team that needs automated failover, tools like CloudNativePG start pulling their weight.

For now, this gets the job done: real replication, real alerting thresholds, and a dashboard that tells me about disk pressure before Postgres does.