How to Backup and Restore a Database in PostgreSQL

PostgreSQL is free, open-source, and one of the most popular relational database management systems, powering millions of applications. Suppose you are a database administrator using PostgreSQL in a production environment. In that case, it is essential to back up your database so you can quickly restore your system if your database is lost.

PostgreSQL has built-in utilities to create or restore the database backup by default. There are several ways to backup and restore your PostgreSQL databases. With PostgreSQL, you can easily take a full, incremental, or continuous backup. You can use the pg_dump utility to back up the PostgreSQL database. This function allows you to back up a local database and restore it on a remote database simultaneously.

In this tutorial, we will show you how to back up and restore the PostgreSQL database in Linux.

Understand the Basic Syntax of pg_dump

PostgreSQL comes with built-in utilities called pg_dump for quickly creating and restoring backups.

The basic syntax of the pg_dump command is shown below:

pg_dump [OPTION]... [DBNAME]

A brief explanation of each option is shown below:

-d, –dbname=DATABASENAME : Used to specify the database that you want to back up.
-h, –host=HOSTNAME :  Used to specify the hostname of your database server.
-U, –username=USERNAME : Used to specify the PostgreSQL username.
-w, –no-password : Used to ignore the password prompt.
-p, –port=PORT : Used to specify the PostgreSQL server port number.
-W, –password : Used to force password prompt.
–role=ROLENAME : SET ROLE before the dump.

Backup and Restore a Single Database

You can back up and restore a single database using the pg_dump utility. The basic syntax to back up a single database is shown below:

pg_dump -d [source-database] -f [database_backup.sql]

For example, to back up a single database named testdb and generate a backup file named testdb_backup.sql, run the following command:

su - postgres
pg_dump -d testdb -f testdb_backup.sql

You can also restore a single database using psql command. The basic syntax to restore a single database is shown below:

psql -d [destination_database] -f [database_backup.sql]

For example, to restore a single database named testdb from a backup file named testdb_backup.sql, run the following command:

su - postgres
psql -d testdb -f testdb_backup.sql

You should get the following output:

SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET

If you want to stop the database restore process in case an error occurs, run the following command:

psql -d testdb --set ON_ERROR_STOP=on -f testdb_backup.sql

Note : Before restoring any database, it is recommended to terminate all connections to that database and prepare the backup file.

Backup and Restore All Databases

You can back up all databases in PostgreSQL using pg_dumpall utility. The basic syntax to backup all databases as shown below:

pg_dumpall -f [alldatabase_backup.sql]

For example, to back up all databases in PostgreSQL and generate a backup file named alldb_backup.sql, run the following command:

pg_dumpall -f alldb_backup.sql

To restore all databases from a backup file named alldb_backup.sql, run the following command:

psql -f alldb_backup.sql

Backup and Restore Single Table

PostgreSQL also allows you to back up a single table from the specific database. You can achieve this using the following syntax:

pg_dump -d [source-database] -t [table_name]-f [dbtable_backup.sql]

For example, to back up a table named mytab from the database named testdb and generate a backup file named testdb_mytab.sql, run the following command:

pg_dump -d testdb -t mytab  -f testdb_mytab_backup.sql

If you want to restore this table from the backup file, run the following command:

psql -d testdb -f testdb_mytab_backup.sql

Backup and Restore Compressed Database

You can also back up the PostgreSQL database and compress it in .gz format to reduce the backup size.

To take a back up of database named testdb and generate a compressed backup file named testdb_compressed.sql.gz, run the following command:

pg_dump -d testdb | gzip > testdb_compressed.sql.gz

You can also restore the backup from the compressed file using the following command:

gunzip -c testdb_compressed.sql.gz | psql  -d testdb

Conclusion

In the above guide, you learned several ways to backup and restore a PostgreSQL database to help you perform day-to-day database backup operations. Get started today with a PostgreSQL database on VPS hosting from Atlantic.Net!