Postgres 流式复制让备用节点保持温热

#!/bin/sh
set -eu
umask 077
lab=$(mktemp -d /tmp/xbcatm-streaming.XXXXXX)
mkdir "$lab/socket"
primary=$lab/primary
standby=$lab/standby
cleanup() {
    for cluster in "$standby" "$primary"; do
        if test -f "$cluster/postmaster.pid"; then
            pg_ctl -D "$cluster" -m fast -w stop >/dev/null
        fi
    done
}
trap cleanup EXIT
initdb -D "$primary" --no-locale --encoding=UTF8 --auth-local=trust --auth-host=reject >/dev/null
cat >> "$primary/postgresql.conf" <<CONF
listen_addresses = ''
unix_socket_directories = '$lab/socket'
port = 56432
wal_level = replica
max_wal_senders = 4
wal_keep_size = '64MB'
CONF
pg_ctl -D "$primary" -l "$lab/primary.log" -w -t 15 start >/dev/null
export PGHOST=$lab/socket PGPORT=56432 PGDATABASE=postgres PGUSER=$(id -un)
export PGCONNECT_TIMEOUT=2
psql -X -v ON_ERROR_STOP=1 -qAt <<'SQL'
CREATE TABLE marker (id integer PRIMARY KEY,value text);
INSERT INTO marker VALUES (1,'base');
SQL
pg_basebackup -D "$standby" -R -X stream -c fast -w
cat >> "$standby/postgresql.conf" <<CONF
port = 56433
hot_standby = on
CONF
pg_ctl -D "$standby" -l "$lab/standby.log" -w -t 15 start >/dev/null
PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT pg_wal_replay_pause()' >/dev/null
attempt=0
while test "$(PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT pg_get_wal_replay_pause_state()='paused'")" != t; do
    attempt=$((attempt+1))
    test "$attempt" -lt 100 || { echo 'replay did not pause' >&2; exit 1; }
    sleep 0.1
done
psql -X -v ON_ERROR_STOP=1 -qAt -c "INSERT INTO marker VALUES (2,'later')"
target=$(psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT pg_current_wal_lsn()')
attempt=0
while test "$(PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT pg_last_wal_receive_lsn()>='$target'::pg_lsn")" != t; do
    attempt=$((attempt+1))
    test "$attempt" -lt 150 || { echo 'receiver did not catch up' >&2; exit 1; }
    sleep 0.1
done
psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT state,sync_state FROM pg_stat_replication'
PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt <<SQL
SELECT pg_last_wal_receive_lsn()>='$target'::pg_lsn,
       pg_last_wal_replay_lsn()<'$target'::pg_lsn,
       (SELECT count(*) FROM marker);
DO \$\$ BEGIN
  IF (pg_last_wal_receive_lsn()>='$target'::pg_lsn
      AND pg_last_wal_replay_lsn()<'$target'::pg_lsn
      AND (SELECT string_agg(id||':'||value,',' ORDER BY id) FROM marker)='1:base')
      IS NOT TRUE THEN
    RAISE EXCEPTION 'paused replay result mismatch';
  END IF;
END \$\$;
SQL
echo stale_read_while_receiving
PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT pg_wal_replay_resume()' >/dev/null
attempt=0
while test "$(PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT pg_last_wal_replay_lsn()>='$target'::pg_lsn")" != t; do
    attempt=$((attempt+1))
    test "$attempt" -lt 150 || { echo 'replay did not catch up' >&2; exit 1; }
    sleep 0.1
done
PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt <<'SQL'
SELECT id,value FROM marker ORDER BY id;
DO $$ BEGIN
  IF (SELECT string_agg(id||':'||value,',' ORDER BY id) FROM marker)
     IS DISTINCT FROM '1:base,2:later' THEN
    RAISE EXCEPTION 'resumed replay result mismatch';
  END IF;
END $$;
SQL
pg_ctl -D "$primary" -m fast -w stop >/dev/null
echo old_primary_stopped
pg_ctl -D "$standby" -w -t 15 promote >/dev/null
PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt <<'SQL'
SELECT pg_is_in_recovery();
INSERT INTO marker VALUES (3,'promoted');
SELECT id,value FROM marker ORDER BY id;
DO $$ BEGIN
  IF pg_is_in_recovery() OR (SELECT count(*) FROM marker)<>3 THEN
    RAISE EXCEPTION 'promotion result mismatch';
  END IF;
END $$;
SQL
printf 'lab_directory %s\n' "$lab"