howitworks.io logohowitworks
← Saari reels
DatabaseReel #42 · 9 Nov 2026

Postgres ki time machine: DROP TABLE ke baad bhi data wapas

Point-in-Time Recovery (PITR) PostgreSQL ka built-in tareeka hai jisse database ko kisi bhi second par wapas laaya ja sakta hai. Postgres har change pehle WAL (write-ahead log) mein likhta hai. Continuous archiving on ho (archive_mode = on aur archive_command), aur ek base backup (pg_basebackup) ho, toh recovery ke time Postgres base backup ke upar WAL replay karta hai aur recovery_target_time par ruk jaata hai. Ye tabhi kaam karta hai jab setup galti se pehle ho chuka ho. Neeche wala restore script humne 9 Nov 2026 ko PostgreSQL 16 par test kiya: 50,001 rows, DROP TABLE, restore, aur saari 50,001 rows wapas.

Links

Aaj ye karo

  1. Archive folder banao aur postgres user ko likhne do (jaise /backups/wal). Phir postgresql.conf mein ye teen lines daalo: wal_level = replica, archive_mode = on, archive_command = 'test ! -f /backups/wal/%f && cp %p /backups/wal/%f'. (wal_level = replica Postgres 10 se default hai.)
  2. Postgres restart karo, archive_mode badalne ke liye restart zaroori hai. Check karo ki archive chal raha hai: SELECT archived_count, last_failed_wal FROM pg_stat_archiver;
  3. Base backup lo: pg_basebackup -D /backups/base -Ft -z -X stream. Isse roz cron se naye folder mein lo. Restore hamesha galti se pehle wale base backup se hota hai.
  4. Optional: archive_timeout = 60 rakho, taaki kam traffic par bhi har minute WAL archive ho jaye. Restore script waise bhi purane data folder ke pg_wal se bacha hua naya WAL utha leta hai.
  5. Galti ho gayi? Turant time note karo aur app ko database mein likhne se roko.
  6. Restore script download karo (link neeche) aur postgres user bankar chalao: PGDATA=/path/to/data BASE=/backups/base WAL=/backups/wal ./pitr-restore.sh "2026-11-08 21:02:21". Time galti se thoda pehle ka do, server ke time zone mein.
  7. Script kya karta hai: Postgres band karta hai, abhi ka data folder .before-restore naam se side mein rakhta hai (kuch delete nahi hota), base backup aur WAL kholta hai, restore_command aur recovery_target_time set karke recovery.signal banata hai, Postgres start karta hai, aur recovery khatam hone tak rukta hai.
  8. Check karo: SELECT count(*) FROM users; Script Homebrew PostgreSQL 16 par test hua hai. Debian/Ubuntu par config /etc/postgresql mein hota hai aur service pg_ctlcluster se chalti hai, wahan stop/start lines apne setup ke hisaab se badlo. Pehle test server par ek baar practice zaroor karo.
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backups/wal/%f && cp %p /backups/wal/%f'
pg_basebackup -D /backups/base -Ft -z -X stream
curl -O https://howitworksio.com/files/pitr-restore.sh && chmod +x pitr-restore.sh
./pitr-restore.sh "2026-11-08 21:02:21"

Sources

PostgreSQL 16 official docs; restore script 9 Nov 2026 ko PostgreSQL 16.14 par test kiya.

Aise hi tech cases ke liye follow karo