Sev1: a production PostgreSQL table was dropped or truncated. Point-in-time restore and copy it back

#Symptoms

• Application errors: relation "orders" does not exist, or the table is suddenly empty.
• Someone confesses, or the log shows DROP TABLE / TRUNCATE.

PostgreSQL has no recycle bin. Recovery means restoring a separate copy to a time before the statement, then moving the data back. Never restore over the running production cluster for one table.

#Step 1: stop further damage (2 minutes)

• Stop the job, migration or person that ran it.
• If application writes depend on the table (foreign keys, triggers), consider putting the app into maintenance mode so it does not create inconsistent data.

#Step 2: find the exact time

grep -nE "DROP TABLE|TRUNCATE" /var/log/postgresql/postgresql-*.log | tail

That works only with log_statement = 'ddl' (or all). Otherwise:

• Check the migration tool's logs or the CI job time.
• pg_stat_activity will not help after the fact.
• On managed services, use their log viewer (CloudWatch Logs for RDS, Log Analytics for Azure) if DDL logging was enabled.

Pick a target a few seconds before the statement. If you are unsure, choose earlier: you can always reapply later rows, but you cannot recover from a target after the drop.

#Step 3: restore a copy to that time

#pgBackRest

On a separate host (or the same host, different data directory and port):

sudo -u postgres pgbackrest --stanza=main --delta \
  --type=time --target="2026-10-29 09:41:55+00" --target-action=promote \
  --pg1-path=/var/lib/postgresql/16/restore restore

Edit /var/lib/postgresql/16/restore/postgresql.conf (or pass options) so the copy uses another port, no archiving, and no replication connections to production:

port = 5433
archive_mode = off

Start it and wait for recovery:

sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/16/restore -l /tmp/restore.log start
tail -f /tmp/restore.log    # "recovery stopping before commit of transaction ..." then "database system is ready"

#pg_basebackup + archived WAL

Unpack the last base backup before the target into a new directory, create recovery.signal, and set:

restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-10-29 09:41:55+00'
recovery_target_action = 'promote'

#Amazon RDS / Aurora

aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier shop-prod --target-db-instance-identifier shop-pitr-0941 \
  --restore-time 2026-10-29T09:41:55Z --db-instance-class db.r6g.large --no-multi-az
# Aurora: aws rds restore-db-cluster-to-point-in-time ... then create an instance in the new cluster

#Azure Database for PostgreSQL Flexible Server

az postgres flexible-server restore -g rg-db --name shop-pitr-0941 \
  --source-server shop-prod --restore-time "2026-10-29T09:41:55Z"

Warning

Restore time depends on database size and WAL volume, often tens of minutes to hours. Start it immediately, then plan the copy-back while it runs.

#Step 4: verify the copy

psql -p 5433 -d shop -c "SELECT count(*), max(created_at) FROM sales.orders;"

The table exists and the newest rows are just before the incident.

#Step 5: copy the table back

Dump just that table (structure and data, with indexes and constraints):

pg_dump -h restore-host -p 5433 -d shop -t sales.orders -Fc -f /tmp/orders.dump

If the table was dropped, restore it whole:

pg_restore -h prod-host -d shop --no-owner --role=app_owner /tmp/orders.dump

If it was truncated (table still exists, possibly with new rows since), load data only into a staging table and merge:

pg_restore -h prod-host -d shop --data-only -t orders --no-owner -f /tmp/orders_data.sql /tmp/orders.dump

Simpler for the truncate case, copy into a side table:

-- on production
CREATE TABLE sales.orders_recovered (LIKE sales.orders INCLUDING ALL);

psql -h restore-host -p 5433 -d shop -c "\copy sales.orders TO STDOUT WITH (FORMAT binary)" \
 | psql -h prod-host -d shop -c "\copy sales.orders_recovered FROM STDIN WITH (FORMAT binary)"

INSERT INTO sales.orders
SELECT r.* FROM sales.orders_recovered r
WHERE NOT EXISTS (SELECT 1 FROM sales.orders o WHERE o.order_id = r.order_id);

Caution

Dropping a table also drops dependent views, foreign keys from other tables pointing at it (with CASCADE) and grants. Check pg_dump output for these and recreate them; compare with the copy using \d+ and \dp.

Reset sequences after reloading:

SELECT setval(pg_get_serial_sequence('sales.orders', 'order_id'), (SELECT max(order_id) FROM sales.orders));

#Step 6: confirm and clean up

• Application works; row counts match expectations.
• Foreign keys, grants and dependent views restored.
• Delete the restored copy (and stop paying for a managed PITR instance) after sign-off.

#Prevent it next time

• log_statement = 'ddl' on production so you always know when DDL ran.
• Humans connect with roles that cannot DROP or TRUNCATE production tables; migrations run with an owner role from CI.
• Test a PITR restore quarterly and record how long it takes.
• An event trigger that blocks DROP TABLE outside migration sessions is a cheap extra guard.