Skip to content
Notifly

PostgreSQL replication lag notification

PostgreSQL replication lag is treacherous: while the lag is small everything looks fine, and then the replica starts serving stale data, and if you fail over to it you lose the latest transactions. It’s even worse when a standby silently disconnects and stops receiving WAL altogether. Better to learn about it from a push notification than during an emergency failover.

What we’ll do

Section titled “What we’ll do”

The script checks replication status every minute and:

  • if lag is ≥ 30 s (or WAL byte lag is large) — sends a normal warning;
  • if lag is ≥ 300 s — sends a high-priority message;
  • if a replica has no active connection in pg_stat_replication on the PRIMARY — sends a high-priority “replica disconnected” message.

The script detects its own role via pg_is_in_recovery(): on the PRIMARY it looks at pg_stat_replication (replay_lsn, byte lag via pg_wal_lsn_diff(sent_lsn, replay_lsn) and the write_lag/replay_lag intervals), and on a REPLICA it computes lag in seconds via EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp()).

To avoid receiving the same message every minute, we store a three-level state (ok / warn / crit) in /var/lib/notifly-pgrepl/.

Script

Section titled “Script”

Save as /usr/local/bin/notifly-pgrepl-check:

#!/usr/bin/env bash
set -eu
set -a; source /etc/notifly.env; set +a


WARN=30       # seconds of lag — warning
CRIT=300      # seconds of lag — critical
WARN_BYTES=$((64 * 1024 * 1024))    # 64 MB of WAL lag — warning
STATE_DIR=/var/lib/notifly-pgrepl
mkdir -p "$STATE_DIR"


HOST=$(hostname -s)


# Helper: run SQL and return a single value (empty on NULL)
pg() { sudo -u postgres psql -tAc "$1" 2>/dev/null | head -n1; }


# notify LEVEL KEY "Title" "Message" PRIORITY
# Sends only when the level changes relative to the stored state.
notify() {
    local level="$1" key="$2" title="$3" msg="$4" prio="$5"
    local state_file="$STATE_DIR/$key"
    local last
    last=$(cat "$state_file" 2>/dev/null || echo ok)
    if [ "$level" != "$last" ]; then
        /usr/local/bin/notifly-send "$title" "$msg" "$prio"
        echo "$level" > "$state_file"
    fi
}


# Detect the role: PRIMARY (t=false) or REPLICA (t=true)
IN_RECOVERY=$(pg "SELECT pg_is_in_recovery();")


if [ "$IN_RECOVERY" = "f" ]; then
    # ---------- We are on the PRIMARY: watch all connected replicas ----------
    # Expected number of replicas (0 = skip the "replica disconnected" check)
    EXPECTED_REPLICAS=${EXPECTED_REPLICAS:-1}
    ACTIVE=$(pg "SELECT count(*) FROM pg_stat_replication;")
    ACTIVE=${ACTIVE:-0}


    if [ "$EXPECTED_REPLICAS" -gt 0 ] && [ "$ACTIVE" -lt "$EXPECTED_REPLICAS" ]; then
        notify crit "primary-conn" \
            "🔥 Replica disconnected — $HOST" \
            "PRIMARY $HOST has $ACTIVE of $EXPECTED_REPLICAS replicas active in pg_stat_replication. Standby is not receiving WAL!" 9
    else
        notify ok "primary-conn" \
            "✅ Replicas connected — $HOST" \
            "All $ACTIVE replicas are connected to PRIMARY $HOST again." 3
    fi


    # Iterate over each replica: application name, byte lag, intervals
    pg "SELECT coalesce(application_name,client_addr::text,'?')
             || '|' || pg_wal_lsn_diff(sent_lsn, replay_lsn)::bigint
             || '|' || coalesce(extract(epoch FROM replay_lag)::int, -1)
        FROM pg_stat_replication;" | while IFS='|' read -r NAME BYTES RLAG; do
        [ -n "$NAME" ] || continue
        KEY="primary-$(echo "$NAME" | tr -c 'A-Za-z0-9_.-' '_')"


        HR=$(numfmt --to=iec "$BYTES" 2>/dev/null || echo "$BYTES B")
        LEVEL=ok
        if [ "$BYTES" -ge "$WARN_BYTES" ] || { [ "$RLAG" -ge 0 ] && [ "$RLAG" -ge "$WARN" ]; }; then
            LEVEL=warn
        fi
        if [ "$RLAG" -ge 0 ] && [ "$RLAG" -ge "$CRIT" ]; then
            LEVEL=crit
        fi


        case "$LEVEL" in
            warn) notify warn "$KEY" \
                    "⚠️ Replication lag — $HOST → $NAME" \
                    "Replica $NAME: WAL lag $HR, replay_lag ${RLAG}s." 5 ;;
            crit) notify crit "$KEY" \
                    "🔥 CRITICAL: replication to $NAME is lagging — $HOST" \
                    "Replica $NAME: replay_lag ${RLAG}s (WAL $HR). Data on the standby is stale!" 9 ;;
            ok)   notify ok "$KEY" \
                    "✅ Replication OK — $HOST → $NAME" \
                    "Replica $NAME has caught up with PRIMARY again (WAL $HR)." 3 ;;
        esac
    done
else
    # ---------- We are on a REPLICA/standby: compute replay lag in seconds ----------
    LAG=$(pg "SELECT coalesce(round(extract(epoch FROM now() - pg_last_xact_replay_timestamp()))::bigint, 0);")
    LAG=${LAG:-0}


    LEVEL=ok
    [ "$LAG" -ge "$WARN" ] && LEVEL=warn
    [ "$LAG" -ge "$CRIT" ] && LEVEL=crit


    case "$LEVEL" in
        warn) notify warn "replica-lag" \
                "⚠️ Replica is lagging — $HOST" \
                "Standby $HOST is ${LAG}s behind PRIMARY (threshold ${WARN}s)." 5 ;;
        crit) notify crit "replica-lag" \
                "🔥 CRITICAL: replica is lagging — $HOST" \
                "Standby $HOST is ${LAG}s behind! Read queries are returning stale data." 9 ;;
        ok)   notify ok "replica-lag" \
                "✅ Replica caught up with PRIMARY — $HOST" \
                "Standby $HOST is back to normal (lag ${LAG}s)." 3 ;;
    esac
fi

Make it executable and test it manually:

Окно терминала
sudo chmod +x /usr/local/bin/notifly-pgrepl-check
sudo /usr/local/bin/notifly-pgrepl-check

Run on schedule

Section titled “Run on schedule”

Using cron

Section titled “Using cron”
Окно терминала
sudo crontab -e

Add the line:

* * * * * /usr/local/bin/notifly-pgrepl-check >/dev/null 2>&1
Section titled “Using systemd timer (recommended)”

/etc/systemd/system/notifly-pgrepl.service:

[Unit]
Description=Notifly PostgreSQL replication lag check


[Service]
Type=oneshot
ExecStart=/usr/local/bin/notifly-pgrepl-check

/etc/systemd/system/notifly-pgrepl.timer:

[Unit]
Description=Run notifly-pgrepl every minute


[Timer]
OnBootSec=2min
OnUnitActiveSec=1min


[Install]
WantedBy=timers.target

Activate it:

Окно терминала
sudo systemctl daemon-reload
sudo systemctl enable --now notifly-pgrepl.timer
sudo systemctl list-timers notifly-pgrepl.timer

Windows: PowerShell + Task Scheduler

Section titled “Windows: PowerShell + Task Scheduler”

Equivalent script for PostgreSQL on a Windows server. The SQL is the same, but we run it via psql.exe, parse the number, and call the common function Send-Notifly from the sysadmin template/index.

C:\scripts\Notifly-PgRepl-Check.ps1
. C:\scripts\Notifly.ps1


$Warn = 30
$Crit = 300
$Psql = "C:\Program Files\PostgreSQL\16\bin\psql.exe"
$env:PGPASSWORD = "postgres"      # or use pgpass.conf / trusted authentication
$Conn = @("-h", "localhost", "-U", "postgres", "-d", "postgres")
$StateDir = "C:\ProgramData\Notifly\pgrepl-state"
New-Item -ItemType Directory -Path $StateDir -Force | Out-Null
$Host = $env:COMPUTERNAME


# Helper: run SQL and return the first line of the result
function Invoke-Psql([string]$Sql) {
    (& $Psql @Conn -tAc $Sql 2>$null | Select-Object -First 1).Trim()
}


# Send a notification only when the level changes
function Notify([string]$Level, [string]$Key, [string]$Title, [string]$Msg, [int]$Prio) {
    $stateFile = Join-Path $StateDir "$Key.txt"
    $last = if (Test-Path $stateFile) { (Get-Content $stateFile -Raw).Trim() } else { "ok" }
    if ($Level -ne $last) {
        Send-Notifly -Title $Title -Message $Msg -Priority $Prio
        $Level | Set-Content $stateFile
    }
}


$inRecovery = Invoke-Psql "SELECT pg_is_in_recovery();"


if ($inRecovery -eq "f") {
    # We are on the PRIMARY: check connected replicas
    $active = [int](Invoke-Psql "SELECT count(*) FROM pg_stat_replication;")
    if ($active -lt 1) {
        Notify "crit" "primary-conn" "🔥 Replica disconnected — $Host" `
            "PRIMARY $Host has no active replicas in pg_stat_replication. Standby is not receiving WAL!" 9
    } else {
        Notify "ok" "primary-conn" "✅ Replicas connected — $Host" `
            "Active replicas on PRIMARY $Host: $active." 3
    }
} else {
    # We are on a REPLICA: compute lag in seconds
    $lag = [int](Invoke-Psql "SELECT coalesce(round(extract(epoch FROM now() - pg_last_xact_replay_timestamp()))::bigint, 0);")


    $level = "ok"
    if ($lag -ge $Warn) { $level = "warn" }
    if ($lag -ge $Crit) { $level = "crit" }


    switch ($level) {
        "warn" { Notify "warn" "replica-lag" "⚠️ Replica is lagging — $Host" `
                    "Standby $Host is ${lag}s behind PRIMARY (threshold ${Warn}s)." 5 }
        "crit" { Notify "crit" "replica-lag" "🔥 CRITICAL: replica is lagging — $Host" `
                    "Standby $Host is ${lag}s behind! Read queries are returning stale data." 9 }
        "ok"   { Notify "ok" "replica-lag" "✅ Replica caught up with PRIMARY — $Host" `
                    "Standby $Host is back to normal (lag ${lag}s)." 3 }
    }
}

Register in Task Scheduler (as administrator, every minute as SYSTEM):

Окно терминала
$Action  = New-ScheduledTaskAction `
    -Execute "powershell.exe" `
    -Argument "-NoProfile -ExecutionPolicy Bypass -File C:\scripts\Notifly-PgRepl-Check.ps1"
$Trigger = New-ScheduledTaskTrigger -Once -At (Get-Date) `
    -RepetitionInterval (New-TimeSpan -Minutes 1)
$Princ   = New-ScheduledTaskPrincipal -UserId "SYSTEM" -LogonType ServiceAccount -RunLevel Highest
Register-ScheduledTask -TaskName "Notifly PgRepl Check" `
    -Action $Action -Trigger $Trigger -Principal $Princ -Description "Notifly: PostgreSQL replication lag"

Benefits

Section titled “Benefits”
  • No silent, stale replicas — you learn about lag before the standby starts serving stale reads or a failover loses transactions.
  • One script for the whole cluster — it detects its role via pg_is_in_recovery(), so it deploys identically on the PRIMARY and on the replicas. The title contains the hostname; in Notifly’s admin it’s easy to gather every node of the cluster into one channel.
  • Three-level logic (ok → warn → crit) plus a separate “replica disconnected” check prevent spam: as long as the state doesn’t change, there are no notifications.

What to improve next

Section titled “What to improve next”
  • Attach the full output of SELECT * FROM pg_stat_replication via the extras field to see all replicas and their LSNs at once.
  • Configure priorities for your cluster: on an analytics replica a lag of minutes is fine, while on a synchronous one it’s critical.
  • Combine it with the service-down recipe and group all database checks into a dedicated channel via Notifly Monitor.