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
- 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.)
- 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;
- 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.
- 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.
- Galti ho gayi? Turant time note karo aur app ko database mein likhne se roko.
- 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.
- 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.
- 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 = replicaarchive_mode = onarchive_command = 'test ! -f /backups/wal/%f && cp %p /backups/wal/%f'pg_basebackup -D /backups/base -Ft -z -X streamcurl -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.
howitworks