In this article, you will learn how to practically perform backups and restore databases in PostgreSQL using the pg_dump and pg_restore tools.
In PostgreSQL, to extract a database or a table into a file, we can generate backups using the pg_dump utility. pg_dump is an external tool used to export data and/or schema into a format that can be restored later.
It is possible to generate a backup either in SQL text format or in a binary format.

CREATING A BACKUP

SQL BACKUP

We can use pg_dump to save the entire database:

pg_dump -U postgres_username -d db_name > backup.sql

Or we can use pg_dump to save only a specific table:

pg_dump -U postgres_username -d db_name -t table_name > backup.sql
  • The pg_dump command also accepts common options that psql supports. So, in addition to specifying -U (user) and -d (database), you can explicitly indicate the server's host and port if needed. -h to specify the host (the PostgreSQL server address) -p to specify the port (the port where the server is listening).

The > symbol redirects the output of the command to a file, saving the result into a newly created .sql file.
This file will contain the necessary SQL commands to recreate the database or table in the same state and data as it was at the time of the backup.
The generated file can be edited and opened in any text editor. It can also be versioned using tools like Git to track changes over time, or directly applied to another PostgreSQL server to recreate the database elsewhere.

BINARY BACKUP(-Fc FORMAT)

pg_dump -U postgres_username -d db_name -Fc -f /path/backup.dump
  • The -Fc option instructs pg_dump to save the backup in a custom binary format.
  • The -f option specifies the output file.

RESTORING THE BACKUP

Important: To restore a backup, the target database must already exist.

RESTORING FROM AN SQL BACKUP
For a SQL restore use psql:

psql -U postgres -d newdb_name < backup.sql

RESTORING FROM A BINARY BACKUP
For large databases, it is recommended to use pg_restore

Binary backup custom:

pg_restore -U postgres -d newdb_name backup.dump
  • The pg_restore command also accepts common options that psql supports. Therefore, you can also specify the server's host and port if needed.

With pg_dump and pg_restore, PostgreSQL provides flexible options for both simple exports and optimized backups for handling large volumes of data.