الـ Backups والـ Point-in-Time Recovery: ترجع لأي ثانية

#!/bin/bash
set -e
# 1) logical backup: pg_dump بالـ custom format (مضغوط، وينفع restore لجزء منه)
pg_dump -Fc -f app.dump app
echo "حجم الـ dump: $(du -h app.dump | cut -f1)"

# 2) الـ backup اللي عمرك ما جرّبت ترجّعه، مش backup: نرجّعه في database جديدة
dropdb --if-exists restore_test
createdb restore_test
# -n public: جداولنا بس (الـ extensions زي pg_stat_statements بيعملها superuser)
pg_restore -n public -d restore_test app.dump

# 3) نقارن
for t in users payments; do
  a=$(psql -At -d app -c "SELECT count(*) FROM $t")
  b=$(psql -At -d restore_test -c "SELECT count(*) FROM $t")
  echo "$t: الأصل=$a الـ restore=$b"
done
pg_restore -l app.dump | grep -c 'TABLE DATA' | xargs echo 'جداول بيانات في الـ dump:'

$ psql -q -f seed.sql && bash backup.sh
حجم الـ dump: 36K
users: الأصل=1000 الـ restore=1000
payments: الأصل=5000 الـ restore=5000
جداول بيانات في الـ dump: 2

#!/bin/bash
# تجربة PITR كاملة على cluster تجريبي (ports 5433 و 5434)
set -e
export PATH="$(pg_config --bindir):$PATH"
P="psql -h localhost -p 5433 -U postgres -d postgres -q -X -At"
# لو الـ script وقع في النص، اقفل الـ servers التجريبية
trap 'for d in lab/primary lab/restore; do pg_ctl -D $d -m immediate stop >/dev/null 2>&1 || true; done' EXIT
rm -rf lab && mkdir -p lab/archive

echo "1) primary فيه WAL archiving"
initdb -D lab/primary -U postgres --auth=trust >/dev/null
cat >> lab/primary/postgresql.conf <<CONF
port = 5433
listen_addresses = 'localhost'
unix_socket_directories = '/tmp'
archive_mode = on
archive_command = 'cp %p ../archive/%f'
CONF
pg_ctl -D lab/primary -l lab/primary.log -w start >/dev/null
$P -c "CREATE TABLE orders (id int, total int)"
$P -c "INSERT INTO orders SELECT i, i * 10 FROM generate_series(1, 100) i"

echo "2) base backup: نسخة من الـ data folder وهي شغالة"
pg_basebackup -h localhost -p 5433 -U postgres -D lab/base -X stream -c fast
echo "   orders وقت الـ backup: $($P -c 'SELECT count(*) FROM orders')"

echo "3) شغل بعد الـ backup"
$P -c "INSERT INTO orders SELECT i, i * 10 FROM generate_series(101, 150) i"
sleep 1
TARGET=$($P -c "SELECT now()")
echo "   orders دلوقتي: $($P -c 'SELECT count(*) FROM orders')"
sleep 1

echo "4) الكارثة: حد مسح الجدول"
$P -c "DROP TABLE orders"
$P -c "SELECT pg_switch_wal()" >/dev/null
sleep 1
pg_ctl -D lab/primary -m fast -w stop >/dev/null
echo "   ملفات WAL في الـ archive: $(ls lab/archive | wc -l)"

echo "5) restore: الـ base backup + الـ WAL لحد قبل المسح بالزبط"
cp -r lab/base lab/restore
cat >> lab/restore/postgresql.conf <<CONF
port = 5434
archive_mode = off
restore_command = 'cp ../archive/%f %p'
recovery_target_time = '$TARGET'
recovery_target_action = 'promote'
CONF
touch lab/restore/recovery.signal
pg_ctl -D lab/restore -l lab/restore.log -w start >/dev/null
echo "   orders بعد الـ recovery: $(psql -h localhost -p 5434 -U postgres -d postgres -q -X -At -c 'SELECT count(*) FROM orders')"
grep -o 'recovery stopping before.*' lab/restore.log | head -1 | sed 's/, time.*//'
pg_ctl -D lab/restore -m fast -w stop >/dev/null

$ bash pitr.sh
pitr.sh: line 4: pg_config: command not found
1) primary فيه WAL archiving
2) base backup: نسخة من الـ data folder وهي شغالة
   orders وقت الـ backup: 100
3) شغل بعد الـ backup
   orders دلوقتي: 150
4) الكارثة: حد مسح الجدول
   ملفات WAL في الـ archive: 4
5) restore: الـ base backup + الـ WAL لحد قبل المسح بالزبط
   orders بعد الـ recovery: 150

#!/bin/bash
set -euo pipefail

mkdir -p backups
FILE="backups/app-$(date +%Y%m%d-%H%M%S).dump"
pg_dump -Fc -f "$FILE" app

dropdb --if-exists verify
createdb verify
pg_restore -n public -d verify "$FILE"

for t in $(psql -At -d app -c "SELECT tablename FROM pg_tables WHERE schemaname = 'public' ORDER BY 1"); do
  a=$(psql -At -d app -c "SELECT count(*) FROM $t")
  b=$(psql -At -d verify -c "SELECT count(*) FROM $t")
  if [ "$a" != "$b" ]; then
    echo "MISMATCH $t: $a != $b"
    exit 1
  fi
  echo "$t OK ($a)"
done

ls -1t backups/*.dump | tail -n +4 | xargs -r rm
echo "backup OK: $FILE"
echo "backups: $(ls backups | wc -l)"

$ for i in 1 2 3 4; do bash backup.sh | tail -1; sleep 1; done; bash backup.sh
backups: 1
backups: 2
backups: 3
backups: 3
payments OK (5000)
users OK (1000)
backup OK: backups/app-20261017-175743.dump
backups: 3