Practical guide: recover a dropped PostgreSQL table with point-in-time recovery without downtime

By Kevin Marcondes · Published on Sep 30, 2026 · Updated on Oct 9, 2026 · 9 min read

How to recover a dropped PostgreSQL table using point-in-time recovery without downtime? The approach is to restore to a temporary server before the DROP, extract only the table and its dependencies, and reintroduce them into production with minimal locks. This lets you validate the data before swapping and avoids stopping the primary environment.

When to use PITR to recover just one table

Use point-in-time recovery when you have a base backup and archived WALs and need to revert to a previous state without interrupting production. The idea is to restore to a temporary server, locate the good version of the table, and import it back.

This is exactly how to recover a dropped PostgreSQL table using point-in-time recovery without downtime when you need to validate data and reduce risk before returning to the main database.

• You know (or can estimate) the time of the DROP.
• There is a recent base backup and continuous WAL archiving.
• The table is not UNLOGGED (those are not written to WAL).
• You can bring up a compatible temporary instance.

Prerequisites and limitations of the method

Without continuous archiving enabled beforehand, a full PITR may not be possible. It's also essential to restore on the same PostgreSQL major version as production to avoid format differences. Never run PITR on the production server: always use a temporary host/container with enough storage.

Regular backups and monitoring drastically reduce the impact of incidents. If you don't have a solid routine yet, consider hardening and backup practices on Linux servers in our hardening, backup & monitoring service.

• wal_level=replica, archive_mode=on and archive_command configured.
• Base backup available from before the incident.
• Disk space to restore data and WALs.
• Access to the archive files (e.g., NFS/S3/local volume).
• Same PostgreSQL major version between production and recovery.

# postgresql.conf (produção)
wal_level = replica
archive_mode = on
archive_command = 'rsync -a %p /mnt/pg_wal_archive/%f'
max_wal_senders = 10
hot_standby = on

Step 1 — Find the exact moment of the DROP

Set a recovery_target_time safely (just before the deletion). Use application logs, auditing, observability tools, and the instance timezone. Preferably choose a few minutes before the incident to ensure the table exists at the chosen point.

Common mistakes here are timezone errors. Confirm the database timezone and convert the incident time if needed.

• Cross-reference app logs, audit logs, and recent deployments.
• Validate the timezone to avoid restoring to the wrong instant.
• Consider a safety margin of 2–5 minutes before the DROP.

-- Confirme o timezone efetivo da instância
SHOW TimeZone;

Step 2 — Restore a temporary server with PITR

Restore a base backup from before the incident into a new data directory. Configure restore_command to fetch archived WALs and set recovery_target_time. When started, Postgres will replay WALs up to that instant and promote the instance.

• Use the same major version as production.
• Consider security: isolate network/credentials of the temporary server.
• Choose fast storage to speed up WAL application.

# Exemplo (PostgreSQL 12+): diretório /pgdata_rec
# 1) Extraia o base backup
sudo -u postgres tar -xvf /backups/base_backup_before_drop.tar -C /pgdata_rec

# 2) Configure parâmetros de recovery
cat >> /pgdata_rec/postgresql.conf <<'EOF'
restore_command = 'cp /mnt/pg_wal_archive/%f %p'
recovery_target_time = '2026-09-30 14:23:00+00'
recovery_target_action = 'promote'
recovery_target_timeline = 'latest'
EOF

# 3) Sinalize o recovery
sudo -u postgres touch /pgdata_rec/recovery.signal

# 4) Inicie a instância temporária
sudo -u postgres pg_ctl -D /pgdata_rec -l /pgdata_rec/logfile start

# Acompanhe os logs até a promoção (instância pronta para leitura)

Step 3 — Extract the table and dependencies from the recovered server

With the temporary instance ready, dump only the table and its related objects (sequences and, if applicable, dependent types and functions). The custom format (-Fc) makes selective restores easier. If the table is partitioned, dump the necessary partitions as well.

• Include related sequences to preserve values.
• Review dependencies (views, materialized views, FKs).
• For very large tables, prefer a high-bandwidth local network.

# Exemplo: recuperar public.pedidos e a sequência de ID
pg_dump -h 10.0.0.50 -U postgres -d appdb -Fc \
  -t public.pedidos -t public.pedidos_id_seq \
  -f /tmp/pedidos.dump

Step 4 — Restore into production with minimal locks

If the table no longer exists, restore directly. If it was recreated after the incident, restore to a temporary name, validate, then perform an atomic rename. Large indexes can be built with CREATE INDEX CONCURRENTLY to reduce locking. Triggers and FKs require special attention.

• Restore to a temporary table when a current table exists.
• Create indexes CONCURRENTLY outside transactions for less impact.
• Renaming tables uses a short (ACCESS EXCLUSIVE) lock, usually imperceptible.
• Adjust sequences after importing data.

# 1) Restaurar para uma tabela temporária
pg_restore -h prod-db -U postgres -d appdb -j 4 \
  -t public.pedidos -t public.pedidos_id_seq /tmp/pedidos.dump \
  --no-owner --no-privileges

# 2) Se a tabela atual existe, faça swap rápido
psql -h prod-db -U postgres -d appdb <<'SQL'
BEGIN;
-- Se necessário, solte chaves estrangeiras que apontam para a tabela antiga aqui, e recrie após o swap.
ALTER TABLE IF EXISTS public.pedidos RENAME TO pedidos_old;
ALTER TABLE public.pedidos RENAME TO pedidos_restaurada; -- caso o dump tenha recriado com o mesmo nome
ALTER TABLE public.pedidos_restaurada RENAME TO pedidos;
COMMIT;

-- 3) Ajuste sequência após a carga
SELECT setval('public.pedidos_id_seq', (SELECT COALESCE(MAX(id),1) FROM public.pedidos)+1, false);
SQL

Post‑restore validation and common risks

Before the swap (or immediately after, if there was no table), validate structure and row counts. Compare basic checks with the temporary instance. Monitor locks and application impact. To investigate locks and contention in production, see best practices for cron jobs and locks in PostgreSQL: audit cron jobs and PostgreSQL locks & deadlocks.

• Compare COUNT(*), MIN/MAX of keys, and random samples.
• Validate constraints and FKs (enable/check if you disabled them).
• Reapply grants and security policies (RLS) if present.
• Check sequences and maintenance routines (VACUUM/ANALYZE).

-- Exemplos de checagens rápidas
SELECT COUNT(*) FROM public.pedidos;
SELECT MIN(id), MAX(id) FROM public.pedidos;
ANALYZE public.pedidos;

What if archiving wasn't enabled? Practical alternatives

Without archive_mode and without base backups, classic PITR may be unfeasible. Consider: filesystem snapshots from before the incident, provider backups, or recovery from a physical replica that hasn't applied the DROP. If possible, restore on a separate instance and follow the same extraction/import process for the table.

• Volume/VM snapshots from before the incident.
• Restores from managed services that offer backups.
• A delayed physical replica (if available) to extract the table.
• Third‑party tools have limitations and risks.

How to avoid the next incident: practices and architecture

Implement continuous archiving, retention policies, and periodic restore tests. For safe schema rollbacks, use database versioning and reliable migration pipelines: see our database versioning guide. To further reduce downtime, consider logical replication/CDC for controlled swaps in production: see zero‑downtime database migration with CDC.

• Regular restore tests (table‑level and cluster‑level).
• Appropriate WAL retention according to business RPO/RTO.
• Alerts for archiving failures and disk usage.
• Migrations with rollback and feature-flagged deployments.

Frequently asked questions

Can I recover just one table with PITR in PostgreSQL?

Yes, but indirectly. You perform PITR to a temporary server up to just before the incident, then export only the table (and dependencies) with pg_dump and import it back into production with minimal locks.

What if I didn't have archive_mode or base backups enabled?

Classic PITR won't be possible. Try restoring a filesystem snapshot, using provider backups, or extracting from a physical replica that hasn't applied the DROP. In the future, enable archiving and tested backup routines.

Is a restore without downtime truly lock‑free?

You avoid stopping the primary database by restoring on a separate instance. The import may require brief locks (for example, during RENAME). With proper planning of indexes and the rename swap, the impact is usually imperceptible.

How do I handle sequences tied to the table?

Include sequences in the dump and, after import, use setval to align the next value with the table's MAX(id). This prevents collisions or primary key violations on future inserts.

Do I need to use the same PostgreSQL version on the temporary instance?

Yes, use the same major version to restore base backups and apply WALs. Major version differences can prevent recovery or introduce incompatibilities in format and collation.

How do I choose the correct recovery_target_time?

Use application and audit logs to identify the DROP time and recover a few minutes before it, considering the server timezone. Validating the chosen point avoids restoring to an still-inconsistent state.

See also

• How to recover corrupted statistics and indexes in PostgreSQL that caused slow queries
• REST API latency spikes from N+1 queries and PHP GC — how to diagnose and fix
• Postgres 'Too many connections' at billing peak — practical diagnosis and remediation guide
• Reduce costs migrating Magento to OpenMage with a rollback plan and acceptance tests