没验证的备份只是空想

#!/bin/sh
set -eu
umask 077
lab=$(mktemp -d /tmp/xbcatm-restore.XXXXXX)
mkdir "$lab/socket"
source=$lab/source
target=$lab/target
cleanup() {
    for cluster in "$target" "$source"; do
        if test -f "$cluster/postmaster.pid"; then
            pg_ctl -D "$cluster" -m fast -w stop >/dev/null
        fi
    done
}
trap cleanup EXIT
for cluster in "$source" "$target"; do
    initdb -D "$cluster" --no-locale --encoding=UTF8 --auth-local=trust --auth-host=reject >/dev/null
    cat >> "$cluster/postgresql.conf" <<CONF
listen_addresses = ''
unix_socket_directories = '$lab/socket'
CONF
done
printf 'port = 56432\n' >> "$source/postgresql.conf"
printf 'port = 56433\n' >> "$target/postgresql.conf"
pg_ctl -D "$source" -l "$lab/source.log" -w -t 15 start >/dev/null
pg_ctl -D "$target" -l "$lab/target.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 ROLE backup_reader NOLOGIN;
CREATE TABLE customers (id integer PRIMARY KEY,name text NOT NULL);
CREATE TABLE orders (id integer PRIMARY KEY,customer_id integer REFERENCES customers,amount_cents integer CHECK (amount_cents>0));
INSERT INTO customers VALUES (1,'Ada');
INSERT INTO orders VALUES (10,1,4210),(11,1,1865);
CREATE VIEW order_totals AS SELECT c.name,sum(o.amount_cents) AS total_cents FROM customers c JOIN orders o ON o.customer_id=c.id GROUP BY c.id,c.name;
GRANT SELECT ON customers,orders,order_totals TO backup_reader;
SQL
pg_dump -Fc -w -f "$lab/snapshot.dump" postgres
sha256sum "$lab/snapshot.dump" > "$lab/snapshot.sha256"
psql -X -v ON_ERROR_STOP=1 -qAt -c 'INSERT INTO orders VALUES (12,1,1000)'
printf 'source_after_backup_orders '
psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT count(*) FROM orders'
sha256sum -c "$lab/snapshot.sha256"
if PGPORT=56433 pg_restore --single-transaction --exit-on-error -w -d postgres "$lab/snapshot.dump" >"$lab/missing-role.log" 2>&1; then
    echo 'restore unexpectedly ignored a missing role' >&2
    exit 1
fi
grep -F 'role "backup_reader" does not exist' "$lab/missing-role.log"
test "$(PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT to_regclass('public.orders') IS NULL")" = t
echo failed_restore_rolled_back
PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c 'CREATE ROLE backup_reader NOLOGIN'
PGPORT=56433 pg_restore --single-transaction --exit-on-error -w -d postgres "$lab/snapshot.dump"
PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt <<'SQL'
SELECT count(*) FROM orders;
SELECT name,total_cents FROM order_totals;
SELECT has_table_privilege('backup_reader','orders','SELECT'),has_table_privilege('backup_reader','orders','INSERT');
DO $$ BEGIN
  IF (SELECT count(*) FROM orders)<>2 OR
     (SELECT string_agg(id||':'||amount_cents,',' ORDER BY id) FROM orders) IS DISTINCT FROM '10:4210,11:1865' OR
     (SELECT total_cents FROM order_totals WHERE name='Ada') IS DISTINCT FROM 6075 OR
     (SELECT count(*) FROM orders o LEFT JOIN customers c ON c.id=o.customer_id WHERE c.id IS NULL)<>0 THEN
    RAISE EXCEPTION 'restored business data mismatch';
  END IF;
END $$;
DO $$ BEGIN
  BEGIN
    INSERT INTO orders VALUES (98,999,100);
    RAISE EXCEPTION 'restored foreign key unexpectedly accepted an orphan';
  EXCEPTION WHEN foreign_key_violation THEN
    RAISE NOTICE 'restored foreign key rejected orphan';
  END;
END $$;
SET ROLE backup_reader;
SELECT id,amount_cents FROM orders ORDER BY id;
DO $$ BEGIN
  BEGIN
    INSERT INTO orders VALUES (99,1,100);
    RAISE EXCEPTION 'read-only role unexpectedly wrote data';
  EXCEPTION WHEN insufficient_privilege THEN
    RAISE NOTICE 'read-only role rejected write';
  END;
END $$;
SQL
printf 'lab_directory %s\n' "$lab"