Restore a Postgres Database from a Dump File

A PostgreSQL database dump is a backup of a PostgreSQL database. It is a file that contains data that can be used to recreate the database including its tables, indices, data, user roles, and other database objects.

We can create the database dump files using the pg_dump utility in PostgreSQL which exports a database to a script or other archive files. The output file, often in “.sql” or “.dump” format, contains SQL commands to reproduce the original database object definitions and table data.

This tutorial explores the steps to restore a PostgreSQL database from a dump file.

Requirements:

Installed PostgreSQL on your machine.
Access to a PostgreSQL dump file, usually a “.sql” or “.dump” file.

Export a PostgreSQL Database to Dump

To export a PostgreSQL database to a dump file, we can use the pg_dump utility. For example, we can run the following command:

pg_dump -U username -d databasename -f dumpfile.sql

The command parameters are explained as follows:

-U username – It specifies the username to connect to the PostgreSQL server.

-d databasename – It defines the name of the database to dump.

-f dumpfile.sql – It specifies the file name to which to output the dump.

The previous command creates a plain SQL file. However, if you want a custom-format archive file (.dump), you can use the -F option:

pg_dump -U username -d databasename -F c -f dumpfile.dump

For example, to export a database called USDA as a postgres user, we can run the following command:

pg_dump -U postgres -d usda -f usda.dump

Restore a PostgreSQL Database from a Dump

The following steps describe hwo to restore a PostgreSQL database from a dump.

Check the Server Status

Before proceeding, ensure that the PostgreSQL service is running. You can run the systemctl command to check the server status.

sudo service postgresql status

Output:

13/main (port 5432): online

You can use Homebrew to check the server status if you are on macOS. Use the service manager to start or check the server status on Windows.

Create a New Database

Before restoring a database from the dump, you must ensure that the target database exists on the server. If not, you can create it using the following SQL command:

CREATE DATABASE <database_name>;

Replace the database_name with your desired database name.

Restore the Database from a Dump File

Once you create the database on the server, you can restore the data using the pg_restore utility.

If you are using a custom “.dump” file, use the following command:

pg_restore -U postgres -d <database_name> /path/to/dump/file.dump

However, if you are using a plain SQL file, you can run the following command:

psql -U postgres -d <database_name> -f /path/to/dump/file.sql

The provided commands should read the commands that are provided in the dump files and restore the data into the database.

Verify the Restoration

Once the restore process is complete, you can verify that the data is restored by logging into the server using the PSQL utility:

psql -U postgres

Connect to the restored database:

\c <database_name>

Once connected, list the tables to confirm the restoration:

\dt

You should see the list of tables in your restored database.

Conclusion

This tutorial explored the steps to export a PostgreSQL database to a dump file. We also covered how to restore a database from a dump file using the pg_restore and psql utilities.