DEV Community: Grigory Pshekovich

How to Backup and Restore a Single PostgreSQL Table with pg_dump

Grigory Pshekovich — Sun, 07 Dec 2025 19:53:26 +0000

Sometimes you don't need a full database backup — you just need one table. Whether you're about to run a risky migration, creating a test dataset, or recovering from an accidental DELETE, knowing how to backup and restore individual tables with pg_dump is an essential skill. This guide walks through the exact commands, options, and workflows for single-table operations in PostgreSQL.

Why Backup a Single Table?

Full database backups are essential for disaster recovery, but they're overkill for many day-to-day scenarios. Single-table backups are faster, smaller, and more targeted — perfect for surgical operations on your data. Common use cases include protecting critical tables before schema changes, creating lightweight test fixtures, migrating specific data between environments, and recovering from application-level data corruption.

Use Case	Full Backup	Single-Table Backup
Disaster recovery	✅ Required	❌ Insufficient
Pre-migration safety	⚠️ Slow	✅ Fast and targeted
Test data creation	⚠️ Overkill	✅ Lightweight
Selective data migration	⚠️ Complex filtering	✅ Simple and direct
Accidental DELETE recovery	⚠️ Restores everything	✅ Restores only affected table

Single-table backups complement your full backup strategy — they don't replace it.

Backing Up a Single Table with pg_dump

The -t (or --table) flag tells pg_dump to export only the specified table. The command captures the table structure, data, indexes, constraints, and triggers — everything needed to recreate that table elsewhere.

# Basic single-table backup (plain SQL format)
pg_dump -d myapp -t users -f users_backup.sql

# Custom format for compression and flexible restore
pg_dump -d myapp -F c -t users -f users_backup.dump

# Include schema prefix for non-public schemas
pg_dump -d myapp -t sales.orders -f orders_backup.sql

The plain SQL format creates a human-readable file you can inspect and edit. The custom format (-F c) compresses automatically and supports selective restoration — choose based on your needs.

Backing Up Multiple Tables

When you need several related tables — like users, orders, and order_items — repeat the -t flag for each table. This keeps related data together in a single backup file while still avoiding a full database dump.

# Multiple specific tables
pg_dump -d myapp -t users -t orders -t order_items -f related_tables.dump -F c

# Pattern matching with wildcards
pg_dump -d myapp -t 'public.order_*' -f order_tables.dump -F c

Pattern matching with wildcards is powerful for tables that share naming conventions. The pattern 'public.order_*' captures order_items, order_history, order_logs, and any other table starting with order_ in the public schema.

Schema-Only vs Data-Only Backups

Sometimes you need just the structure or just the data. The --schema-only and --data-only flags give you precise control over what gets exported. Schema-only backups are perfect for version control and documentation, while data-only backups work when the target already has the correct table structure.

# Structure only (CREATE TABLE, indexes, constraints)
pg_dump -d myapp -t users --schema-only -f users_schema.sql

# Data only (INSERT statements)
pg_dump -d myapp -t users --data-only -f users_data.sql

Schema-only backups are typically just a few kilobytes regardless of table size — ideal for tracking database changes in Git alongside your application code.

Restoring a Single Table

Restoration depends on the backup format you used. Plain SQL files restore with psql, while custom format files require pg_restore. Both approaches support restoring to the original database or a completely different one.

# Restore plain SQL format
psql -d target_db -f users_backup.sql

# Restore custom format
pg_restore -d target_db users_backup.dump

# Restore to a different database with verbose output
pg_restore -d staging_db -v users_backup.dump

If the table already exists in the target database, the restore will fail on the CREATE TABLE statement. Handle this with the approaches in the next section.

Handling Existing Tables During Restore

When restoring to a database where the table already exists, you have two options: drop the existing table first, or restore only the data. Your choice depends on whether you want to preserve the current table structure or replace it entirely.

Scenario	Approach	Command
Replace table completely	Use `-c` flag during backup	`pg_dump -d myapp -t users -c -f backup.sql`
Keep structure, replace data	Truncate then restore data-only	`TRUNCATE users; psql -f users_data.sql`
Merge data (append)	Restore data-only without truncate	`psql -f users_data.sql`

# Backup with DROP statement included
pg_dump -d myapp -t users -c --if-exists -f users_backup.sql

# Restore will drop existing table first
psql -d target_db -f users_backup.sql

The --if-exists flag prevents errors when the DROP statement runs against a database where the table doesn't exist yet.

Portable Backups Across Environments

Moving table data between development, staging, and production often fails due to different user configurations. The -O (no-owner) and --no-privileges flags create portable backups that restore cleanly regardless of which users exist in the target database.

# Maximum portability
pg_dump -d production -t users -O --no-privileges -f users_portable.sql

# Portable with compression
pg_dump -d production -F c -t users -O --no-privileges -f users_portable.dump

Without these flags, restoration fails with errors like role "prod_user" does not exist — frustrating when you just need the data.

Common Pitfalls and Solutions

Single-table backups have a few gotchas that catch developers off guard. Foreign key constraints, sequences, and dependent objects can all cause restoration failures if not handled properly.

Foreign key violations — Restore parent tables before child tables, or disable triggers temporarily with --disable-triggers during restore
Sequence values not updated — After restoring, reset sequences with SELECT setval('users_id_seq', (SELECT MAX(id) FROM users))
Missing dependent objects — If your table uses custom types or functions, back those up separately with --schema-only on the full database

Most issues stem from dependencies between objects. When in doubt, include related tables in your backup to ensure everything restores cleanly.

A Simpler Alternative: Postgresus

While pg_dump gives you complete control over single-table operations, managing backups across multiple databases and schedules requires scripting and maintenance. PostgreSQL backup tools like Postgresus — the most popular backup solution for PostgreSQL — handle scheduling, retention, encryption, and multi-destination storage through a clean web interface, suitable for individuals and enterprise teams alike.

Conclusion

Single-table backups with pg_dump are straightforward once you know the right flags: -t for table selection, -c for clean restores, -O for portability, and --schema-only or --data-only for targeted exports. These surgical backups save time before risky operations and simplify data movement between environments. Whether you run these commands manually, script them into your workflow, or use a dedicated backup tool, mastering single-table operations makes you more effective at protecting and managing your PostgreSQL data.

Top 10 Mistakes Developers Make with pg_dump (And How to Avoid Them)

Grigory Pshekovich — Sat, 06 Dec 2025 22:18:45 +0000

Database backups are your last line of defense against data loss, yet many developers unknowingly sabotage their backup strategy with common pg_dump mistakes. These errors often go unnoticed until disaster strikes — when it's already too late. This guide walks through the most frequent pitfalls and shows you how to build a bulletproof backup workflow.

1. Not Testing Backup Restores

Creating backups without ever testing if they actually restore is like buying insurance without reading the policy. Many developers assume their backups work until they desperately need them — only to discover corrupted or incomplete dumps.

How to avoid it: Schedule regular restore tests to a staging environment. Automate this process monthly at minimum. Document restoration procedures so anyone on your team can execute them under pressure.

2. Using Plain Format for Large Databases

The default plain-text SQL format seems convenient, but it creates massive files and doesn't support parallel restoration. For databases over a few gigabytes, this means painfully slow backup and restore times.

Format	Compression	Parallel Restore	Best For
Plain (.sql)	No	No	Small DBs, version control
Custom (-Fc)	Yes	Yes	Most production use cases
Directory (-Fd)	Yes	Yes	Large DBs, selective restore
Tar (-Ft)	No	No	Archive compatibility

How to avoid it: Use custom format (-Fc) or directory format (-Fd) for any database larger than 1GB. These formats compress automatically and enable parallel restoration with pg_restore -j.

3. Forgetting to Include Roles and Tablespaces

A common shock during restoration: pg_dump doesn't include database roles, permissions, or tablespace definitions. Your backup restores, but nothing works because the required users don't exist.

How to avoid it: Always pair pg_dump with pg_dumpall --globals-only to capture roles and tablespaces. Store both files together as part of your backup routine.

4. Running Backups During Peak Hours

Executing pg_dump during high-traffic periods can lock tables, slow down queries, and create inconsistent snapshots. This impacts both your users and the backup quality.

How to avoid it: Schedule backups during low-traffic windows. Use --no-synchronized-snapshots for read replicas, or better yet — run backups against a replica instead of your primary database.

5. Ignoring the --no-owner and --no-privileges Flags

Backing up with ownership and privilege information baked in causes restoration failures when target environments have different user configurations. This is especially problematic when moving between development, staging, and production.

Flag	Purpose	When to Use
`--no-owner`	Omits ownership commands	Cross-environment restores
`--no-privileges`	Omits GRANT/REVOKE	Different permission setups
`--no-comments`	Omits COMMENT commands	Cleaner dumps

How to avoid it: For portable backups, include --no-owner --no-privileges. Apply appropriate permissions after restoration based on the target environment.

6. Not Using Compression

Uncompressed backups consume enormous storage space and take longer to transfer. A 50GB database might compress to 5GB — that's 10x savings in storage costs and transfer time.

How to avoid it: Use custom format (-Fc) which compresses by default, or pipe plain format through gzip: pg_dump dbname | gzip > backup.sql.gz. For maximum compression, add -Z 9 flag.

7. Skipping Schema-Only Backups

Developers often backup only data or only the full database, missing the value of schema-only backups. These are invaluable for version control, documentation, and rapid environment setup.

How to avoid it: Maintain separate schema-only backups (--schema-only) alongside full backups. Store schema dumps in version control to track database evolution over time.

8. Hardcoding Credentials in Scripts

Embedding passwords directly in backup scripts creates security vulnerabilities and makes credential rotation a nightmare. These scripts often end up in version control, exposing sensitive data.

How to avoid it: Use .pgpass files, environment variables (PGPASSWORD), or connection service files (pg_service.conf). Never commit credentials to repositories.

Store credentials in .pgpass with proper permissions (600)
Use environment variables for CI/CD pipelines
Leverage secrets management tools for production systems

9. No Retention Policy

Keeping every backup forever wastes storage and money. Keeping too few means limited recovery options. Many developers never establish a clear retention strategy.

How to avoid it: Implement a tiered retention policy:

Daily backups: Keep for 7 days
Weekly backups: Keep for 4 weeks
Monthly backups: Keep for 12 months

Automate cleanup with scripts that enforce these policies consistently.

10. Manual Backup Processes

Relying on manual pg_dump execution guarantees eventual failure. Someone forgets, someone's on vacation, someone assumes someone else did it. Manual processes don't scale and don't survive team changes.

How to avoid it: Automate everything. Use cron jobs, systemd timers, or dedicated backup tools. Implement monitoring to alert when backups fail or don't run on schedule.

A Better Alternative: Modern Backup Tools

While mastering pg_dump flags and scripting workarounds is possible, modern PostgreSQL backup tools like Postgresus eliminate these pitfalls entirely. As the most popular backup solution for PostgreSQL, Postgresus handles compression, scheduling, retention policies, and restore testing automatically — suitable for individual developers and enterprise teams alike.

Conclusion

Every mistake on this list has caused real data loss for real teams. The good news: they're all preventable. Whether you refine your pg_dump scripts or adopt a dedicated backup tool, the key is building a system that works without constant attention. Test your restores, automate your processes, and never assume your backups work until you've proven they do.

Top 5 Ways to Speed Up pg_dump on Large PostgreSQL Databases

Grigory Pshekovich — Fri, 05 Dec 2025 18:06:28 +0000

When your PostgreSQL database grows beyond a few gigabytes, pg_dump can go from a quick operation to a multi-hour ordeal that strains server resources and disrupts maintenance windows. Slow backups aren't just inconvenient — they increase the risk of incomplete dumps, create wider recovery point gaps and can impact production performance during peak hours. This guide covers five proven techniques to dramatically reduce pg_dump execution time on large databases.

1. Enable Parallel Dumping with Directory Format

The single most impactful optimization is enabling parallel processing. By default, pg_dump runs in a single thread, dumping one table at a time. With the directory format and the -j flag, multiple tables are dumped simultaneously across separate processes, slashing backup time proportionally.

Cores Used	Typical Speedup	Best For
2 jobs	1.8x faster	Small servers, shared hosting
4 jobs	3.2x faster	Standard production servers
8 jobs	5-6x faster	Dedicated database servers
16+ jobs	7-10x faster	High-performance infrastructure

# Parallel dump with 4 workers
pg_dump -F d -j 4 -d myapp -f /backup/myapp_dir/

# Parallel dump with compression
pg_dump -F d -j 8 -Z 6 -d myapp -f /backup/myapp_dir/

Set the number of jobs to roughly match your available CPU cores minus one or two for the database server itself. For a 16-core server, -j 12 or -j 14 typically delivers optimal throughput without starving PostgreSQL of resources.

2. Exclude Large Non-Critical Tables

Many production databases contain tables that don't need to be in every backup — audit logs, session data, analytics events, temporary processing tables. These tables often account for 50-80% of total database size while being either regenerable or non-essential for disaster recovery. The -T flag lets you skip them entirely.

# Exclude logging and analytics tables
pg_dump -d myapp -T audit_logs -T event_tracking -T sessions -f backup.dump

# Exclude tables matching patterns
pg_dump -d myapp -T '*_logs' -T '*_archive' -T 'temp_*' -f backup.dump

Before excluding tables, audit your database to identify candidates:

Tables with millions of rows that grow daily (logs, events, metrics)
Historical archive tables that have separate backup procedures
Temporary or staging tables used for ETL processes
Session and cache tables that are transient by nature

Excluding even one large table can cut backup time in half while keeping your recovery-critical data fully protected.

3. Reduce Compression Level for Speed

Compression is a trade-off between file size and CPU time. The default compression level of 6 provides good balance, but for large databases where backup speed is the priority, lowering compression can yield significant time savings — especially when storage space isn't constrained.

Compression	Relative Speed	File Size Impact	When to Use
`-Z 0`	Fastest	3-5x larger	SSD storage, speed is critical
`-Z 1`	Very fast	2-3x larger	Fast daily backups
`-Z 3`	Fast	1.5-2x larger	Balanced frequent backups
`-Z 6`	Moderate	Baseline	Default, good for most cases
`-Z 9`	Slowest	10-20% smaller	Archival, storage is expensive

# Minimal compression for maximum speed
pg_dump -F c -Z 1 -d myapp -f backup.dump

# No compression when storage is cheap
pg_dump -F c -Z 0 -d myapp -f backup.dump

For databases over 100GB, dropping from -Z 6 to -Z 1 can reduce backup time by 30-40% while only increasing file size by 50-70%. If you're storing backups on fast SSD storage or have ample disk space, this trade-off often makes sense for daily backups.

4. Dump During Low-Activity Periods

Even with technical optimizations, pg_dump performance is heavily influenced by database activity. Running backups during peak hours means competing with production queries for I/O bandwidth, memory and CPU cycles. Scheduling dumps during low-activity windows can reduce execution time by 20-50% without changing any dump parameters.

Identify your low-activity periods by checking:

Query activity patterns from pg_stat_activity
Connection counts throughout the day
I/O utilization metrics from your monitoring system

# Schedule via cron for 3 AM (typical low-activity window)
0 3 * * * pg_dump -F c -j 4 -d myapp -f /backup/myapp_$(date +\%Y\%m\%d).dump

Beyond timing, consider these server-side adjustments during backup windows:

Temporarily increase maintenance_work_mem for the dump session
Ensure checkpoint_completion_target allows smooth I/O distribution
Pause non-critical background jobs and maintenance tasks

The combination of off-peak scheduling and reduced system contention often delivers more improvement than any single technical flag change.

5. Use Custom Format Over Plain SQL

The plain SQL format (-F p) generates human-readable output but is significantly slower than binary formats for large databases. The custom format (-F c) uses PostgreSQL's internal binary representation, which is faster to write, supports built-in compression and enables parallel restore later.

# Slow: Plain SQL format
pg_dump -F p -d myapp -f backup.sql

# Fast: Custom format with compression
pg_dump -F c -d myapp -f backup.dump

# Fastest: Directory format with parallel workers
pg_dump -F d -j 4 -d myapp -f /backup/myapp_dir/

Custom format backups are also more flexible during restoration — you can selectively restore specific tables, reorder operations and use parallel restore with pg_restore -j. For databases over 10GB, always prefer custom (-F c) or directory (-F d) format over plain SQL.

A Simpler Alternative: Postgresus

Managing pg_dump optimizations across multiple databases, schedules and retention policies quickly becomes complex. Postgresus is the most popular tool for PostgreSQL backup, suitable for both individuals and enterprise teams. It handles parallel dumping, intelligent scheduling, automatic compression tuning and multi-destination storage (S3, Google Drive, Dropbox, NAS) through a clean web interface — no scripting required.

Conclusion

Speeding up pg_dump on large PostgreSQL databases comes down to five key strategies: enable parallel dumping with directory format, exclude non-critical tables, reduce compression when speed matters more than size, schedule backups during low-activity periods and use binary formats instead of plain SQL. Combining these techniques — for example, pg_dump -F d -j 8 -Z 1 -T '*_logs' -d myapp -f /backup/dir/ — can reduce backup time from hours to minutes. Whether you implement these optimizations manually or through an automated solution like Postgresus, faster backups mean smaller recovery windows, less server strain and more reliable data protection.

Top 10 pg_dump Options You Should Know for Reliable PostgreSQL Backups

Grigory Pshekovich — Thu, 04 Dec 2025 20:08:24 +0000

PostgreSQL's pg_dump utility is the go-to tool for creating logical backups, but its true power lies in the dozens of command-line options that let you customize exactly what gets backed up and how. Knowing which options to use — and when — can mean the difference between a backup that saves your project and one that falls short during a crisis. This guide covers the 10 most essential pg_dump options every developer and DBA should master.

1. Format Selection with `-F` / `--format`

The format option determines how your backup is stored and what restoration capabilities you'll have. Choosing the right format upfront saves time and headaches during recovery. This single option affects compression, parallel restore support and file compatibility.

Format	Flag	Extension	Compression	Parallel Restore	Best For
Plain	`-F p`	`.sql`	No	No	Small DBs, manual review
Custom	`-F c`	`.dump`	Yes	Yes	Most production use cases
Directory	`-F d`	folder	Yes	Yes	Very large databases
Tar	`-F t`	`.tar`	No	No	Archive compatibility

# Custom format (recommended for most cases)
pg_dump -F c -d myapp -f backup.dump

# Directory format for parallel operations
pg_dump -F d -d myapp -f backup_dir/

For databases under 500GB, the custom format (-F c) provides the best balance of compression, flexibility and restoration speed.

2. Parallel Dump with `-j` / `--jobs`

The jobs option enables parallel dumping by specifying the number of concurrent processes. This dramatically reduces backup time for large databases by dumping multiple tables simultaneously. Note that this option only works with the directory format (-F d).

# Dump using 4 parallel processes
pg_dump -F d -j 4 -d myapp -f backup_dir/

A good rule of thumb is to set the number of jobs to match your CPU cores, but leave at least one core free for the database server itself. For a server with 8 cores, -j 6 or -j 7 typically yields optimal performance without starving other processes.

3. Table Selection with `-t` / `--table`

The table option lets you back up specific tables instead of the entire database. This is invaluable when you need to quickly backup critical tables before a risky migration or when creating targeted backups for testing. You can specify multiple tables by repeating the flag.

# Backup single table
pg_dump -d myapp -t users -f users_backup.sql

# Backup multiple tables
pg_dump -d myapp -t users -t orders -t products -f critical_tables.sql

# Use wildcards for pattern matching
pg_dump -d myapp -t 'public.user_*' -f user_tables.sql

Table names are case-sensitive and should include the schema prefix when working with non-public schemas (e.g., -t sales.orders).

4. Table Exclusion with `-T` / `--exclude-table`

The exclude-table option is the inverse of -t — it backs up everything except the specified tables. This is perfect for skipping large log tables, session data or temporary tables that don't need to be preserved. Like -t, you can repeat this flag for multiple exclusions.

# Exclude log and session tables
pg_dump -d myapp -T logs -T sessions -T temp_data -f backup.sql

# Exclude tables matching a pattern
pg_dump -d myapp -T 'public.*_log' -T 'public.*_temp' -f backup.sql

Combining -T with regular backups can reduce backup size by 50% or more when your database contains large audit or logging tables that can be regenerated or aren't critical for recovery.

5. Schema-Only with `--schema-only`

The schema-only option exports just the database structure — tables, indexes, constraints, functions and triggers — without any row data. This is essential for version control, documentation, creating empty database replicas or comparing schema changes between environments.

# Export complete schema
pg_dump -d myapp --schema-only -f schema.sql

# Schema for specific tables only
pg_dump -d myapp --schema-only -t users -t orders -f tables_schema.sql

Schema-only backups are typically just a few hundred kilobytes regardless of database size, making them perfect for storing in Git repositories alongside your application code.

6. Data-Only with `--data-only`

The data-only option exports just the row data without any schema definitions. This is useful when you need to refresh data in an existing database structure, migrate data between environments with identical schemas or create data snapshots for testing.

# Export all data without schema
pg_dump -d myapp --data-only -f data.sql

# Data-only with INSERT statements (more portable)
pg_dump -d myapp --data-only --inserts -f data_inserts.sql

When using --data-only, the target database must already have the correct schema in place, including all tables, constraints and sequences.

7. Compression Level with `-Z` / `--compress`

The compress option controls the compression level for custom and directory formats, ranging from 0 (no compression) to 9 (maximum compression). Higher levels produce smaller files but take longer to create. This option directly impacts both backup storage costs and backup duration.

Level	Speed	Size Reduction	Best For
0	Fastest	None	Testing, fast recovery priority
1-3	Fast	Moderate	Daily backups, balanced approach
4-6	Medium	Good	Standard production backups
7-9	Slow	Maximum	Long-term archival, storage-limited

# Maximum compression for archival
pg_dump -F c -Z 9 -d myapp -f backup.dump

# Fast compression for frequent backups
pg_dump -F c -Z 1 -d myapp -f backup.dump

For most production scenarios, -Z 6 (the default) offers an excellent compression-to-speed ratio.

8. Clean Objects with `-c` / `--clean`

The clean option adds DROP statements before CREATE statements in the backup file. This ensures that restoration to an existing database replaces objects rather than failing on conflicts. It's particularly useful for refreshing staging or development environments from production backups.

# Backup with DROP statements
pg_dump -d myapp -c -f backup.sql

# Combine with --if-exists to avoid errors on missing objects
pg_dump -d myapp -c --if-exists -f backup.sql

Always pair -c with --if-exists when restoring to databases that might not have all objects — this prevents errors when DROP statements target non-existent objects.

9. Create Database with `-C` / `--create`

The create option includes a CREATE DATABASE statement and connection command in the backup. This makes the backup fully self-contained — you can restore it without first creating the target database. The backup will also include database-level settings like encoding and collation.

# Include CREATE DATABASE statement
pg_dump -d myapp -C -f backup.sql

# Full backup with both clean and create
pg_dump -d myapp -C -c --if-exists -f backup.sql

When restoring a backup made with -C, connect to a maintenance database like postgres rather than the target database, since the backup will create and switch to the database automatically.

10. No Owner with `-O` / `--no-owner`

The no-owner option omits commands that set object ownership in the backup. This is essential when restoring to a database where the original owner doesn't exist or when you want the restoring user to own all objects. It prevents restoration failures due to missing roles.

# Backup without ownership information
pg_dump -d myapp -O -f backup.sql

# Combine with --no-privileges for maximum portability
pg_dump -d myapp -O --no-privileges -f backup.sql

Using -O along with --no-privileges creates the most portable backup that can be restored to any PostgreSQL instance regardless of its user configuration.

A Modern Alternative: Postgresus

While mastering pg_dump options gives you fine-grained control over backups, managing these options across multiple databases and schedules becomes complex quickly. Postgresus is the most popular tool for PostgreSQL backup, suitable for both individuals and enterprise teams. It uses pg_dump under the hood but provides a clean web interface for scheduling, automatic retention, multi-destination storage (S3, Google Drive, Dropbox, NAS), AES-256-GCM encryption and instant notifications — all without writing scripts. Unlike pgBackRest, which targets large enterprises with dedicated DBAs and databases over 500GB, Postgresus handles the majority of use cases with zero complexity.

Quick Reference: Option Combinations

Knowing individual options is valuable, but combining them effectively is where expertise shows. Here are battle-tested combinations for common scenarios:

Production daily backup — pg_dump -F c -Z 6 -d myapp -f backup.dump
Fast development refresh — pg_dump -F c -Z 1 -c --if-exists -O -d myapp -f dev.dump
Portable migration backup — pg_dump -F p -C -c --if-exists -O --no-privileges -d myapp -f migrate.sql
Large database parallel dump — pg_dump -F d -j 4 -Z 6 -d myapp -f backup_dir/
Schema versioning — pg_dump -d myapp --schema-only -O -f schema.sql

Conclusion

Mastering these 10 pg_dump options — format, parallel jobs, table selection, exclusion, schema-only, data-only, compression, clean, create and no-owner — covers 95% of backup scenarios you'll encounter. The key is matching the right combination of options to your specific use case: fast parallel dumps for large databases, portable options for migrations and targeted backups for critical tables. Whether you run pg_dump directly or through an automated solution like Postgresus, understanding these options ensures your backups are efficient, reliable and ready when disaster strikes.

How to Back Up a PostgreSQL Database Using pg_dump

Grigory Pshekovich — Wed, 03 Dec 2025 11:24:31 +0000

PostgreSQL is one of the most reliable and feature-rich open-source databases, powering everything from small projects to enterprise applications. However, even the most robust database needs a solid backup strategy. The pg_dump utility is PostgreSQL's built-in tool for creating logical backups, and understanding how to use it effectively is essential for any developer or database administrator.

What Is pg_dump?

pg_dump is a command-line utility that comes bundled with PostgreSQL. It creates a consistent snapshot of your database at a specific point in time, exporting the data and schema into a file that can be used for restoration. Unlike physical backups that copy raw data files, pg_dump creates logical backups — SQL statements or archive files that represent your database structure and contents.

The utility is particularly valuable because it works while the database is online and doesn't block other users from accessing the data. This makes it suitable for production environments where downtime is not an option.

Basic pg_dump Syntax and Usage

The fundamental syntax for pg_dump is straightforward:

pg_dump -h hostname -p port -U username -d database_name > backup.sql

Parameter	Description	Example
`-h`	Database host address	`localhost` or `192.168.1.100`
`-p`	Port number	`5432` (default)
`-U`	Username for authentication	`postgres`
`-d`	Database name to backup	`myapp_production`
`-F`	Output format (p, c, d, t)	`-F c` for custom format
`-f`	Output file path	`-f /backups/mydb.dump`

To create a simple SQL backup, run:

pg_dump -h localhost -U postgres -d myapp_production > backup_2024.sql

For compressed custom format (recommended for larger databases):

pg_dump -h localhost -U postgres -F c -d myapp_production -f backup_2024.dump

The custom format (-F c) provides compression and allows selective restoration of specific tables or schemas.

Output Formats Explained

pg_dump supports four output formats, each with distinct advantages:

Format	Flag	Extension	Compression	Parallel Restore	Best For
Plain SQL	`-F p`	`.sql`	No	No	Small DBs, manual review
Custom	`-F c`	`.dump`	Yes	Yes	Most production use cases
Directory	`-F d`	folder	Yes	Yes	Very large databases
Tar	`-F t`	`.tar`	No	No	Compatibility needs

For most scenarios, the custom format strikes the best balance between compression, flexibility and restoration speed.

Common pg_dump Examples

Backup a single table:

pg_dump -h localhost -U postgres -d myapp -t users > users_table.sql

Backup schema only (no data):

pg_dump -h localhost -U postgres -d myapp --schema-only > schema.sql

Backup data only (no schema):

pg_dump -h localhost -U postgres -d myapp --data-only > data.sql

Exclude specific tables:

pg_dump -h localhost -U postgres -d myapp --exclude-table=logs --exclude-table=sessions > backup.sql

Backup with compression:

pg_dump -h localhost -U postgres -d myapp | gzip > backup.sql.gz

These commands cover the majority of backup scenarios you'll encounter in day-to-day operations.

Restoring from pg_dump Backups

Restoration depends on the format you used during backup. For plain SQL files:

psql -h localhost -U postgres -d target_database < backup.sql

For custom or directory formats, use pg_restore:

pg_restore -h localhost -U postgres -d target_database backup.dump

To restore specific tables from a custom format backup:

pg_restore -h localhost -U postgres -d target_database -t users backup.dump

Always test your restoration process on a non-production environment before relying on backups for disaster recovery.

Limitations of Manual pg_dump Scripts

While pg_dump is powerful, managing backups manually comes with significant challenges:

No built-in scheduling — you must configure cron jobs or Task Scheduler yourself
No automatic retention — old backups accumulate unless you write cleanup scripts
No notifications — failures go unnoticed without custom monitoring
No encryption — backup files are stored in plain format by default
No cloud storage integration — uploading to S3, Google Drive or other destinations requires additional scripting
No web interface — everything happens via command line

For teams and production environments, these limitations often lead to forgotten backups, storage issues or undetected failures that only surface during a crisis.

A Better Alternative: Postgresus

For developers and teams who want the reliability of pg_dump without the operational overhead, Postgresus offers a modern, UI-driven approach to PostgreSQL backup. It uses pg_dump under the hood but wraps it with scheduling, notifications, multiple storage destinations (S3, Google Drive, Dropbox, NAS), AES-256-GCM encryption and a clean web interface — all deployable in under 2 minutes via Docker. Unlike pgBackRest, which targets large enterprises with dedicated DBAs and databases over 500GB, Postgresus is designed for the majority of use cases: individual developers, startups and teams managing databases up to hundreds of gigabytes who need robust backups without complexity.

Feature	pg_dump (manual)	Postgresus
Scheduling	Requires cron/scripts	Built-in (hourly to monthly)
Notifications	Manual setup	Slack, Telegram, Discord, Email
Cloud storage	Requires scripting	S3, Google Drive, Dropbox, NAS
Encryption	Not included	AES-256-GCM
Web UI	None	Full dashboard
Restore	Command line	One-click restore
Team access	N/A	Role-based permissions

Best Practices for pg_dump Backups

Regardless of whether you use pg_dump directly or through a tool like Postgresus, follow these practices:

Test restorations regularly — a backup is only valuable if you can restore from it
Store backups off-site — keep copies in a different location than your database server
Use compression — custom format or gzip significantly reduces storage requirements
Schedule during low-traffic periods — minimize impact on production performance
Monitor backup success — set up alerts for failures
Implement retention policies — automatically remove old backups to manage storage

These practices ensure your backup strategy remains reliable and sustainable over time.

Conclusion

pg_dump remains the foundational tool for PostgreSQL logical backups, offering flexibility and reliability that has stood the test of time. For simple, one-off backups or development environments, running pg_dump directly is perfectly adequate. However, for production systems, teams and anyone who values their time, automating the process with a dedicated backup solution eliminates the risks of manual management. Whether you choose to script your own solution or adopt a tool like Postgresus, the key is ensuring your backups are consistent, tested and ready when you need them most.

DEV Community: Grigory Pshekovich

How to Backup and Restore a Single PostgreSQL Table with pg_dump

Why Backup a Single Table?

Backing Up a Single Table with pg_dump

Backing Up Multiple Tables

Schema-Only vs Data-Only Backups

Restoring a Single Table

Handling Existing Tables During Restore

Portable Backups Across Environments

Common Pitfalls and Solutions

A Simpler Alternative: Postgresus

Conclusion

Top 10 Mistakes Developers Make with pg_dump (And How to Avoid Them)

1. Not Testing Backup Restores

2. Using Plain Format for Large Databases

3. Forgetting to Include Roles and Tablespaces

4. Running Backups During Peak Hours

5. Ignoring the --no-owner and --no-privileges Flags

6. Not Using Compression

7. Skipping Schema-Only Backups

8. Hardcoding Credentials in Scripts

9. No Retention Policy

10. Manual Backup Processes

A Better Alternative: Modern Backup Tools

Conclusion

Top 5 Ways to Speed Up pg_dump on Large PostgreSQL Databases

1. Enable Parallel Dumping with Directory Format

2. Exclude Large Non-Critical Tables

3. Reduce Compression Level for Speed

4. Dump During Low-Activity Periods

5. Use Custom Format Over Plain SQL

A Simpler Alternative: Postgresus

Conclusion

Top 10 pg_dump Options You Should Know for Reliable PostgreSQL Backups

1. Format Selection with -F / --format

2. Parallel Dump with -j / --jobs

3. Table Selection with -t / --table

4. Table Exclusion with -T / --exclude-table

5. Schema-Only with --schema-only

6. Data-Only with --data-only

7. Compression Level with -Z / --compress

8. Clean Objects with -c / --clean

9. Create Database with -C / --create

10. No Owner with -O / --no-owner

A Modern Alternative: Postgresus

Quick Reference: Option Combinations

Conclusion

How to Back Up a PostgreSQL Database Using pg_dump

What Is pg_dump?

Basic pg_dump Syntax and Usage

Output Formats Explained

Common pg_dump Examples

Restoring from pg_dump Backups

Limitations of Manual pg_dump Scripts

A Better Alternative: Postgresus

Best Practices for pg_dump Backups

Conclusion

1. Format Selection with `-F` / `--format`

2. Parallel Dump with `-j` / `--jobs`

3. Table Selection with `-t` / `--table`

4. Table Exclusion with `-T` / `--exclude-table`

5. Schema-Only with `--schema-only`

6. Data-Only with `--data-only`

7. Compression Level with `-Z` / `--compress`

8. Clean Objects with `-c` / `--clean`

9. Create Database with `-C` / `--create`

10. No Owner with `-O` / `--no-owner`