How to Restore a Postgresql Backup File Using the Command Line


By squashlabs, Last Updated: October 30, 2023

How to Restore a Postgresql Backup File Using the Command Line

Restoring a Postgres backup file using the command line is a straightforward process that allows you to recover your database from a previous state. In this guide, we will walk through the step-by-step instructions to restore a Postgres backup file using the pg_restore command-line tool.

Step 1: Locate the Backup File

Before you proceed with the restoration process, ensure that you have a backup file available. This backup file could be in one of several formats, such as a plain text SQL file or a custom-format archive file. Make sure you know the exact location of the backup file on your system.

Related Article: How to Check and Change Postgresql's Default Port

Step 2: Identify the Target Database

Next, identify the database where you want to restore the backup. If the target database already exists, the restoration process will overwrite its contents. If the target database does not exist, pg_restore will create it for you during the restoration process.

Step 3: Execute the pg_restore Command

Once you have located the backup file and identified the target database, you can execute the pg_restore command to restore the backup. The basic syntax of the command is as follows:

pg_restore -d  

Replace with the name of your target database and with the path to your backup file.

Step 4: Additional Options

The pg_restore command provides various options that allow you to customize the restoration process. Here are some commonly used options:

-c or --clean: Drops the existing database objects before restoring the backup.
-j or --jobs=: Specifies the number of parallel jobs to use for the restoration process. This can speed up the restoration process on multi-core systems.
-F or --format=: Specifies the format of the backup file. Some common formats include p for plain text SQL files, c for custom-format archive files, and d for directory-format backups.
-O or --no-owner: Restores the database objects without altering their ownership.
-t <table> or --table=<table>: Restores only the specified table from the backup file.

Refer to the pg_restore documentation for a complete list of available options and their descriptions.

Related Article: How to Create a Database from the Command Line Using Psql

Step 5: Examples

Let’s look at a few examples to illustrate the usage of the pg_restore command.

Example 1: Restoring a plain text SQL backup file to a target database named “mydatabase”:

pg_restore -d mydatabase backup.sql

Example 2: Restoring a custom-format archive backup file to a target database named “mydatabase” with parallel jobs set to 4:

pg_restore -d mydatabase -j 4 backup.dump

Example 3: Restoring a specific table named “mytable” from a directory-format backup to a target database named “mydatabase”:

pg_restore -d mydatabase -t mytable /path/to/backup/

Step 6: Best Practices

To ensure a successful restoration process, consider the following best practices:

1. Regularly schedule backups: Create a backup strategy that includes regular backups of your database. This will allow you to restore your database to a known state in case of any data loss or corruption.

2. Test backups regularly: Periodically test your backup files by restoring them to a test environment. This will help you verify the integrity of the backups and ensure that they can be successfully restored when needed.

3. Use version control for SQL files: If you are using plain text SQL backup files, consider storing them in a version control system like Git. This will allow you to track changes to the backup files over time and easily revert to previous versions if necessary.

4. Keep backup files secure: Ensure that your backup files are stored in a secure location, away from unauthorized access. Consider encrypting the backup files to protect sensitive data.

5. Document the restoration process: Document the steps involved in the restoration process, including the location of backup files and the commands used for restoration. This documentation will be helpful in case someone else needs to restore the database or if you need to restore it in the future.

Tutorial: Managing PostgreSQL Databases with Vacuumdb

Managing PostgreSQL databases efficiently is crucial for optimal database management. This in-depth guide will help you understand and utilize the power of vacuumdb in... read more

How to Create a PostgreSQL Read Only User

Creating a read-only user in PostgreSQL database is an important step in securing your data. This article provides a guide on how to achieve this, covering topics such... read more

How to Check & Change the DB Directory in PostgreSQL

A detailed look at the functionality and application of postgresql-check-db-dir in PostgreSQL databases. This article explores the common queries used in PostgreSQL, how... read more

How to Disable IPv6 in PostgreSQL Databases

Disabling IPv6 in your PostgreSQL database setup is an important step to ensure optimal performance and security. This article provides a step-by-step guide on how to... read more

Step-by-Step Process to Uninstall PostgreSQL on Ubuntu

Uninstalling PostgreSQL from your Ubuntu system can be a process if you follow the step-by-step instructions provided in this article. From preparing for the... read more

Tutorial: Using Navicat for PostgreSQL Database Management

This article provides a detailed guide on using Navicat for PostgreSQL database management. Learn about data modeling, SQL queries, data migration, database... read more