How to back up and restore a PostgreSQL database using pg_dump and pg_restore

Backing up and restoring a database is an essential task for any database administrator. It ensures that the data is protected and can be recovered in case of data loss, corruption, or system failure. PostgreSQL provides two useful utilities, pg_dump and pg_restore, to make this task easy and reliable. In this step-by-step guide, we’ll show you how to use these utilities to back up and restore a PostgreSQL database.

Prerequisites

Before we get started, make sure you have the following:

• PostgreSQL installed on your system.
• A database to back up and restore.

Understanding pg_dump and pg_restore

pg_dump is a PostgreSQL utility that creates a backup of a database by generating a text file that contains SQL statements to recreate the database’s schema and data. It can also be used to back up specific tables, schema, or even individual records. pg_dump creates a portable file format that can be used to transfer data between different PostgreSQL installations or even between different database management systems.

pg_restore is a PostgreSQL utility that restores a backup file created by pg_dump or a similar tool. It reads the SQL statements from the backup file and applies them to a new or existing database, creating a copy of the original database. pg_restore can be used to restore the entire database, specific tables or schemas, or even individual records.

Step 1 – Creating a Backup

The first step is to create a backup of your database using pg_dump. This utility creates a text file that contains SQL statements to recreate the database’s schema and data.

Syntax

The syntax for using pg_dump is as follows:

$ pg_dump [options] [dbname] > [backup_file]

• options: additional options to customize the backup process, such as the format, compression, encoding, or exclude objects.
• dbname: the name of the database to back up.
• backup_file: the name of the file to write the backup to.

Example

To create a backup, open a terminal or command prompt and run the following command:

$ pg_dump dbname > backup.sql

Replace dbname with the name of the database you want to back up, and backup.sql with the name you want to give to the backup file.

The pg_dump utility will ask you for the database’s password. Enter the password and press Enter.

The backup process may take a while, depending on the size of your database.

Once the backup is complete, you will have a file named backup.sql in your current directory.

Customizing the Backup

pg_dump provides many options to customize the backup process. Some of the most useful options are:

• -F: specifies the backup format. The default format is plain text (-Fp), but you can also use binary (-Fc) or directory (-Fd) formats.
• -j: specifies the number of parallel jobs to use. This option can speed up the backup process on multi-core systems.
• -T: excludes tables from the backup. You can use wildcards or regular expressions to match the table names.
• -n: excludes schemas from the backup. You can use wildcards or regular expressions to match the schema names.
• -a: backs up only the data, not the schema. This option can be useful when you want to transfer the data between two databases with different schema structures.

Step 2 – Restoring a Backup

The next step is to restore the backup using pg_restore. This utility reads the SQL statements from the backup file and applies them to a new database.

Syntax

The syntax for using pg_restore is as follows:

$ pg_restore [options] [backup_file]

• options: additional options to customize the restore process, such as the target database, the format, the encoding, or the schema mapping.
• backup_file: the name of the file to restore from.

Example

To restore a backup, open a terminal or command prompt and run the following command:

$ pg_restore backup.sql

Replace backup.sql with the name of the backup file you want to restore from.

The pg_restore utility will ask you for the database’s password. Enter the password and press Enter.

The restore process may take a while, depending on the size of your database.

Once the restore is complete, you will have a new database with the same schema and data as the original database.

Customizing the Restore

pg_restore also provides many options to customize the restore process. Some of the most useful options are:

• -d: specifies the target database to restore to. By default, pg_restore creates a new database with the same name as the original database.
• -F: specifies the backup format. The default format is plain text (-Fp), but you can also use binary (-Fc) or directory (-Fd) formats.
• -j: specifies the number of parallel jobs to use. This option can speed up the restore process on multi-core systems.
• -n: specifies a schema mapping from the backup file to the target database. This option can be useful when you want to restore the backup to a different schema than the original database.
• -t: restores only the specified tables. You can use wildcards or regular expressions to match the table names.

Conclusion

Backing up and restoring a database is a critical task for any database administrator. With pg_dump and pg_restore, it’s easy to create a backup and restore it to a new or existing database. By following the steps outlined in this guide, you can ensure that your data is protected and can be recovered in case of data loss or system failure.