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.
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics