Backup and Restore PostgreSQL Databases

Learn how to safely backup and restore your PostgreSQL databases using standard command-line tools.

1. Simple Database Backup (`pg_dump`)

The pg_dump utility extracts a PostgreSQL database into a script file or other archive file.


                # Backup a single database to a plain-text SQL file
pg_dump -U username -h localhost -d my_database > backup.sql
 
# Backup the database into a compressed custom format (Recommended)
pg_dump -U username -h localhost -F c -d my_database -f backup.dump

Note:

Using the custom format (-F c) is highly recommended because it is compressed by default and allows you to use pg_restore perfectly.

2. Backing up ALL Databases (`pg_dumpall`)

If you want to backup everything (including users, roles, and table spaces), use pg_dumpall.


                pg_dumpall -U username > all_databases_backup.sql

3. Restoring a Database

If you have a customized or compressed dump created by pg_dump -F c, use pg_restore.


                # Create the target database first (if it doesn't exist)
createdb -U username my_database
 
# Restore the compressed dump file
pg_restore -U username -h localhost -d my_database -1 backup.dump

Tip:

The -1 flag executes the restore as a single transaction. If anything fails, the entire restore is rolled back safely!

4. Restoring from Plain-Text SQL

If your backup is a .sql file, you can restore it directly using psql.


                psql -U username -h localhost -d my_database < backup.sql

On This Page