PostgreSQL pg_dump: Backup and Restore Database Easily

PostgreSQL: Using pg_dump for Backup and Restore

pg_dump is a PostgreSQL utility used to back up a database into a script or an archive file. It is widely used for creating consistent backups, which can be restored later to the same or another database.

1. Basic Usage

To back up a database:

pg_dump -U <username> -d <database_name> -f <output_file>.sql  

Here:

• <username>: PostgreSQL username.
• <database_name>: Name of the database to back up.
• <output_file>.sql: File where the backup will be stored.

Example:

Code:

pg_dump -U postgres -d mydb -f backup.sql  

2. Backup Formats

pg_dump supports different output formats:

Plain SQL (default):

pg_dump -U <username> -d <database_name> > backup.sql  

Custom Format:

pg_dump -U <username> -F c -d <database_name> -f backup.dump   

Directory Format:

pg_dump -U <username> -F d -d <database_name> -f /path/to/backup_dir/  

Tar Format:

pg_dump -U <username> -F t -d <database_name> -f backup.tar  

3. Backup Specific Tables

To back up specific tables, specify them using the -t flag:

pg_dump -U <username> -d <database_name> -t <table_name> -f table_backup.sql  

Example:

Code:

pg_dump -U postgres -d mydb -t employees -f employees_backup.sql  

4. Restoring a Backup

Use psql to restore a plain SQL backup:

psql -U <username> -d <database_name> -f <backup_file>.sql  

For custom format or directory backups, use pg_restore:

pg_restore -U <username> -d <database_name> <backup_file>  

Example:

Code:

pg_restore -U postgres -d restored_db backup.dump   

5. Key Options for pg_dump

Example Use Case

Full Database Backup

Code:

pg_dump -U admin -d production_db -F c -f production_backup.dump  

Restoration of Custom Backup

Code:

pg_restore -U admin -d production_restored production_backup.dump  

All PostgreSQL Questions, Answers, and Code Snippets Collection.