How to Backup and Restore PostgreSQL Databases with pg_dump?
pg_dump -Fc -v --host=host --port=5432 --username=user --dbname=dbname -f backup.dump. To restore it, run pg_restore --host=host --port=5432 --username=user --dbname=dbname backup.dump. Use pg_dump for a single database or pg_dumpall to back up every database on the server, including roles and global objects.What is pg_dump?
pg_dump (also searched as “pgdump” or “postgres dump”) is PostgreSQL’s official command-line utility for creating a backup of a single database. It generates a logical backup – a snapshot of your database’s schema and data – as either a plain-text SQL script or a compressed custom/directory-format archive, which can later be restored using psql or pg_restore.
Unlike a physical file copy, pg_dump runs safely on a live, active database without locking tables or interrupting reads and writes, since it uses PostgreSQL’s built-in MVCC (multi-version concurrency control) to take a consistent snapshot at the moment the dump starts.
For backing up every database on a server – along with global objects like roles and tablespaces – PostgreSQL provides a separate utility, pg_dumpall, covered later in this guide.
Prerequisites
Before you start, you need to check the following:
- Ensure you have PostgreSQL client tool installed and activated on the system, and have a database.
- Check if your target PostgreSQL version is equal or higher than your source database.
Backup the PostgreSQL Database
1. Export the database. Replace backup.dump with by adding filename.
pg_dump -Fd -v --host=yourdbusername.com --port=5432--username=example-user --dbname=example_db -f backup.dump
2. To export all PostgreSQL databases on the server, execute the below command.
pg_dumpall -Fd -v --host=yourdbusername.com --port=5432 --username=example-user --dbname=example_db -f full-backup.dump
3. After completion, verify that the backup file is present in the working directory.
ls
Restore the PostgreSQL Database
1. Use psql or pg_restore to import the database and run the below syntax to restore PostgreSQL Database
pg_restore --host=[host] --port=<port> --username=[user] --dbname=[database-name] database.backup
2. Create a new target database with the same name as the original database.
CREATE DATABASE example_db;
3. Close the PostgreSQL client tool.
\q
4. Then, import the database backup file to the server
pg_restore --host=yourdbusername.com --port=5432 --username=example-user --dbname=example_db backup.dump
5. After completing, log in again into the server and switch to the database.
\c example_db
6. Verify that restoration of the database is successful.
\dt
7. Verify that table data is present.
SELECT * FROM app_users;
8. Close the PostgreSQL client.
\dt
Conclusion
pg_dump remains the most reliable way to back up and restore individual PostgreSQL databases without downtime, while pg_dumpall and pg_basebackup cover full-server and physical backup needs respectively. Following a consistent backup schedule, choosing the right output format, and regularly testing restores will ensure your PostgreSQL data stays recoverable when it matters most.
FAQs
What’s the difference between pg_dump and pg_dumpall?
pg_dump backs up one database at a time and does not include global objects like roles or tablespaces. pg_dumpall backs up every database on the server along with these global objects, making it the better choice for full-server migrations or disaster recovery, while pg_dump is faster and more practical for routine single-database backups.
How do I restore a PostgreSQL dump using psql?
If your dump was created in plain-text SQL format (-Fp), restore it with psql -U username -d dbname -f backup.sql. This works because plain SQL dumps are just a script of standard SQL commands that psql executes sequentially. For custom or directory-format dumps, use pg_restore instead, which handles compressed and parallelized archives.
How do I use pg_restore to restore a pg_dump backup?
Run pg_restore --host=host --port=5432 --username=user --dbname=dbname backup.dump. pg_restore is required (not psql) when your backup was created with -Fc (custom) or -Fd (directory) format, since these are compressed, non-plain-text archives that pg_restore can also parallelize with the -j flag for faster restores on large databases.
Can I back up or restore just a single table?
Yes. Add -t table_name to your pg_dump command to export only that table, e.g., pg_dump -t app_users -Fc -f table_backup.dump. When restoring, use the same flag with pg_restore: pg_restore -t app_users --dbname=example_db table_backup.dump. This is useful for quick backups before schema changes without dumping the entire database.
What’s an example pg_dump command for a custom-format backup?
pg_dump -Fc -v --host=localhost --port=5432 --username=example_user --dbname=example_db -f backup.dump – the -Fc flag creates a compressed custom-format archive (smaller and faster to restore selectively than plain SQL), and -v enables verbose output so you can monitor progress during the dump.
Which pg_dump output format should I choose — plain, custom, or directory?
Use plain-text (-Fp) for small databases or when you need a human-readable, editable SQL script. Use custom format (-Fc) for compression and selective restores. Use directory format (-Fd) for the largest databases, since it supports parallel dumping and restoring via multiple CPU cores, significantly speeding up both operations.
Is it safe to run pg_dump on a live production database?
Yes. pg_dump takes a consistent snapshot using PostgreSQL’s MVCC (multi-version concurrency control), so it does not lock tables or block other reads and writes during the backup. However, it can add I/O and CPU load on large databases, so scheduling dumps during low-traffic windows is still recommended.
If you’re looking for a fully managed PostgreSQL hosting environment with automated backups built in, Cantech’s Managed Database Hosting handles the backup, monitoring, and recovery process for you.
For more updated information, please visit the below resources:
