Object-Level Backup

Object-level backup recovers specific schemas, tables, or partitions without restoring the entire PostgreSQL data directory. Use pg_dump filters and targeted pg_restore.

Recipe

Nightly table-level dump for critical ledger tables.

pg_dump -h prod.internal -U backup -Fc \
  -t public.orders -t public.payments \
  -f /backup/ledger_$(date +%F).dump appdb

# Restore one table to recovery schema for investigation
pg_restore -h prod.internal -U dba -d appdb \
  -t public.orders --schema=recovery_orders /backup/ledger_2026-07-09.dump

When to reach for this: Operator error (DROP TABLE), partial corruption, or legal hold on subset of data.

Working Example

Schema-level backup and partition-level export:

# Entire schema
pg_dump -h prod.internal -U backup -Fc -n reporting \
  -f /backup/reporting_schema.dump appdb
 
# Single partition (declarative partitioning)
pg_dump -h prod.internal -U backup -Fc \
  -t 'public.events_2026_07' \
  -f /backup/events_2026_07.dump appdb

-- In-database safety net before risky migration
CREATE TABLE orders_backup_20260709 AS TABLE orders;
 
-- Or clone structure only
CREATE TABLE orders_restored (LIKE orders INCLUDING ALL);

# Restore data into new table, swap with rename (expand-contract)
pg_restore -h prod.internal -d appdb --data-only \
  -t public.orders /backup/ledger.dump

What this demonstrates:

-t and -n limit dump scope for faster dumps and smaller files.
Restore into alternate schema for validation before cutover.
In-DB CREATE TABLE AS is a quick object snapshot without filesystem dump.

Deep Dive

When Object-Level Wins

Scenario	Approach
Dropped table	`pg_restore -t` from last nightly dump
Bad migration on one schema	Restore `-n` to temp DB, diff and merge
Legal export	Dump specific tables with column list (views)
Large DB, small mistake	Avoid multi-hour full restore

Swap-Back Pattern

Restore object to recovery_* schema or database.
Validate row counts and checksums.
Rename production table to *_broken.
Rename recovery table to production name in transaction.
Recreate indexes and foreign keys if needed.

Limitations

Foreign keys from other tables may block restore order.
Sequences must be reset after data-only restore.
Logical dumps do not capture uncommitted transactions at crash instant.

Gotchas

No recent table dump - Full cluster PITR is only path. Fix: Tier-0 tables in dedicated nightly -t dumps.
Restore over live table without rename - Duplicate key chaos. Fix: Restore to side table; swap in maintenance window.
Missing dependent objects - Restore table without indexes. Fix: Restore --section=post-data or full table definition.
Partition dump parent vs child - Dump correct partition relation name. Fix: \d+ parent to list child tables.
RLS blocks restore role - Partial data in dump. Fix: Backup role with BYPASSRLS or superuser for regulated dumps.

Alternatives

Alternative	Use When	Don't Use When
PITR to new cluster + copy table	No object dump exists	Fast table-only fix needed
`CREATE TABLE AS` snapshot	Same-DB quick copy	Off-site disaster recovery
Logical replication	Continuous copy to warehouse	One-off mistake recovery

FAQs

Object dump from standby?

Yes. pg_dump -h standby reduces primary load; snapshot is as of standby replay position.

Can I restore only indexes?

Use pg_restore --section=post-data with -t filter after data loaded.

Multiple tables with FK order?

pg_restore handles dependency order in custom format; use single dump file for related tables.

pgvector / PostGIS tables?

Include extension objects in dump; ensure target has pgvector 0.8+ and PostGIS 3.5+ installed before restore.

How long retain object dumps?

Align with legal retention; tier-0 tables often 30-90 days minimum beyond PITR window.

pg_dump & pg_restore - format details
Backup Basics - strategy overview
PITR Basics - when object dump is too old
Rollback Strategy - migration safety

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Object-Level Backup

Object-level backup recovers specific schemas, tables, or partitions without restoring the entire PostgreSQL data directory. Use pg_dump filters and targeted pg_restore.

Recipe

Nightly table-level dump for critical ledger tables.

pg_dump -h prod.internal -U backup -Fc \
  -t public.orders -t public.payments \
  -f /backup/ledger_$(date +%F).dump appdb

# Restore one table to recovery schema for investigation
pg_restore -h prod.internal -U dba -d appdb \
  -t public.orders --schema=recovery_orders /backup/ledger_2026-07-09.dump

When to reach for this: Operator error (DROP TABLE), partial corruption, or legal hold on subset of data.

Working Example

Schema-level backup and partition-level export:

# Entire schema
pg_dump -h prod.internal -U backup -Fc -n reporting \
  -f /backup/reporting_schema.dump appdb
 
# Single partition (declarative partitioning)
pg_dump -h prod.internal -U backup -Fc \
  -t 'public.events_2026_07' \
  -f /backup/events_2026_07.dump appdb

-- In-database safety net before risky migration
CREATE TABLE orders_backup_20260709 AS TABLE orders;
 
-- Or clone structure only
CREATE TABLE orders_restored (LIKE orders INCLUDING ALL);

# Restore data into new table, swap with rename (expand-contract)
pg_restore -h prod.internal -d appdb --data-only \
  -t public.orders /backup/ledger.dump

What this demonstrates:

-t and -n limit dump scope for faster dumps and smaller files.
Restore into alternate schema for validation before cutover.
In-DB CREATE TABLE AS is a quick object snapshot without filesystem dump.

Deep Dive

When Object-Level Wins

Scenario	Approach
Dropped table	`pg_restore -t` from last nightly dump
Bad migration on one schema	Restore `-n` to temp DB, diff and merge
Legal export	Dump specific tables with column list (views)
Large DB, small mistake	Avoid multi-hour full restore

Swap-Back Pattern

Restore object to recovery_* schema or database.
Validate row counts and checksums.
Rename production table to *_broken.
Rename recovery table to production name in transaction.
Recreate indexes and foreign keys if needed.

Limitations

Foreign keys from other tables may block restore order.
Sequences must be reset after data-only restore.
Logical dumps do not capture uncommitted transactions at crash instant.

Gotchas

No recent table dump - Full cluster PITR is only path. Fix: Tier-0 tables in dedicated nightly -t dumps.
Restore over live table without rename - Duplicate key chaos. Fix: Restore to side table; swap in maintenance window.
Missing dependent objects - Restore table without indexes. Fix: Restore --section=post-data or full table definition.
Partition dump parent vs child - Dump correct partition relation name. Fix: \d+ parent to list child tables.
RLS blocks restore role - Partial data in dump. Fix: Backup role with BYPASSRLS or superuser for regulated dumps.

Alternatives

Alternative	Use When	Don't Use When
PITR to new cluster + copy table	No object dump exists	Fast table-only fix needed
`CREATE TABLE AS` snapshot	Same-DB quick copy	Off-site disaster recovery
Logical replication	Continuous copy to warehouse	One-off mistake recovery

FAQs

Object dump from standby?

Yes. pg_dump -h standby reduces primary load; snapshot is as of standby replay position.

Can I restore only indexes?

Use pg_restore --section=post-data with -t filter after data loaded.

Multiple tables with FK order?

pg_restore handles dependency order in custom format; use single dump file for related tables.

pgvector / PostGIS tables?

Include extension objects in dump; ensure target has pgvector 0.8+ and PostGIS 3.5+ installed before restore.

How long retain object dumps?

Align with legal retention; tier-0 tables often 30-90 days minimum beyond PITR window.

pg_dump & pg_restore - format details
Backup Basics - strategy overview
PITR Basics - when object dump is too old
Rollback Strategy - migration safety

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Object-Level Backup

Buscar en la documentación

Object-Level Backup