How to Backup and Restore PostgreSQL Databases with pg_dump?
The pg_dump tool functions as a command-line utility within PostgreSQL to produce stable backups for individual databases while maintaining concurrent access without interruption. The system enables multiple output formats such as plain-text SQL scripts and
custom-format archives to provide diverse restoration methods. The pg_dumpall utility is the most recommended tool for creating extensive backups that include every database alongside global objects.
This blog will guide you on how to backup and restore PostgreSQL Database with pg_dump.
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
For more updated information, please visit the below resources:
