w3resource

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

Option Description
-h <host> Specifies the database server's host.
-p <port> Specifies the port number for the database server.
-Fc Creates a custom-format archive.
--data-only Dumps only the data, excluding schema.
--schema-only Dumps only the schema, excluding data.

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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/PostgreSQL/snippets/postgresql-pg-dump.php