How to Backup and Restore a PostgreSQL Database Using pg_dump and pg_restore

When working with PostgreSQL databases, it's essential to have a reliable backup strategy to protect your data. The two most commonly used tools for backing up and restoring PostgreSQL databases are pg_dump and pg_restore. In this blog post, I'll walk you through the process of creating backups and restoring them using these tools, along with some useful tips and examples.
1. Creating a Backup with pg_dump
To back up a PostgreSQL database, we use the pg_dump command. This tool allows us to create a backup in various formats, including plain text, custom format, and others. For this example, we will use the custom format (-Fc), which is ideal for restoring the database later.
Command Example:pg_dump --no-owner --no-privileges --no-publications --no-subscriptions --no-tablespaces -Fc -v -d "postgresql://username:password@hostname:port/dbname?sslmode=require" -f "C:\path\to\your\backup.bak"

Explanation of the Options:--no-owner: Excludes ownership information.
--no-privileges: Excludes privileges and grants (useful if you're not concerned with replicating them).
--no-publications: Excludes publication-related data.
--no-subscriptions: Excludes subscription-related data.
--no-tablespaces: Excludes tablespace data.
-Fc: Creates the backup in the custom format.
-v: Enables verbose mode for detailed output.
-d: Specifies the connection string to the PostgreSQL database.
-f: Specifies the path where the backup file will be saved.
Sample Output:pg_dump: last built-in OID is 16383 pg_dump: reading extensions pg_dump: reading schemas pg_dump: reading user-defined tables ... pg_dump: dumping contents of table "public.example_table_1"

This output shows the process of dumping the database, reading schema information, tables, functions, and so on. After this completes, you'll have a .bak file that you can use for restoring your database.
2. Restoring a Backup with pg_restore
Once you have a backup file, you can use the pg_restore tool to restore the data into a new or existing PostgreSQL database. The pg_restore command works with backups created in the custom format, and it provides various options for how to restore the data.
Command Example:pg_restore -v -d "postgresql://username:password@hostname:port/dbname?sslmode=require" "C:\path\to\your\backup.bak"

Explanation of the Options:-v: Enables verbose mode, so you can see the details of the restoration process.
-d: Specifies the connection string to the database where you want to restore the backup.
Sample Output:pg_restore: creating TYPE "public.example_type" pg_restore: creating TABLE "public.example_table_1" pg_restore: creating SEQUENCE "public.example_table_1_id_seq" pg_restore: processing data for table "public.example_table_1" pg_restore: processing data for table "public.example_table_2" pg_restore: processing data for table "public.example_table_3" ... pg_restore: creating CONSTRAINT "public.example_table_3 example_table_3_pkey"

The restoration process will first recreate the database objects such as tables, sequences, and constraints. Then, it will insert the data into the respective tables.
3. Common Error and Fix:
During the restoration process, you might encounter an error like this:pg_restore: error: options -d/--dbname and -f/--file cannot be used together

This error typically occurs when both the -d (database) and -f (file) options are used incorrectly together. Ensure you’re only using -d to specify the database and not -f for the file location during restore.
4. Full Example:
Here’s an actual example from the command line output showing the process of both backup and restore:
Backup Command:C:\Program Files\PostgreSQL\16\bin>pg_dump --no-owner --no-privileges --no-publications --no-subscriptions --no-tablespaces -Fc -v -d "postgresql://<username>:<password>@<hostname>:<port>/<dbname>?sslmode=require" -f "C:\path\to\your\backup.bak"

Backup Output:pg_dump: last built-in OID is 16383 pg_dump: reading extensions pg_dump: identifying extension members pg_dump: reading schemas pg_dump: reading user-defined tables ... pg_dump: dumping contents of table "public.example_table_1"

This shows the successful execution of pg_dump, where various database objects like tables, user-defined types, functions, and sequences are backed up.
Restore Command:C:\Program Files\PostgreSQL\16\bin>pg_restore -v -d "postgresql://<username>:<password>@<hostname>:<port>/<dbname>?sslmode=require" "C:\path\to\your\backup.bak"

Restore Output:pg_restore: creating TYPE "public.example_type" pg_restore: creating TABLE "public.example_table_1" pg_restore: creating SEQUENCE "public.example_table_1_id_seq" pg_restore: creating SEQUENCE OWNED BY "public.example_table_1_id_seq" pg_restore: processing data for table "public.example_table_1" pg_restore: processing data for table "public.example_table_2" pg_restore: processing data for table "public.example_table_3" pg_restore: creating CONSTRAINT "public.example_table_3 example_table_3_pkey"

The output confirms the restoration of various tables, sequences, and constraints to the target PostgreSQL database. The data is also processed and inserted into the corresponding tables.
5. Conclusion
Using pg_dump and pg_restore provides a simple and efficient way to back up and restore PostgreSQL databases. Whether you are moving a database to a new environment, creating a backup for disaster recovery, or migrating data, these tools are essential for any PostgreSQL administrator.
Make sure to test your backups regularly to ensure they can be restored successfully. Always use secure and encrypted connections, especially when working with cloud-hosted databases.
Final Note:
The commands above are intended to demonstrate the basic process. Make sure to replace any placeholder values such as <username>, <password>, <hostname>, <port>, and <dbname> with your actual database credentials. Additionally, ensure you have appropriate permissions on the target database when restoring data.