How to restore a PostgreSQL backup

There are different types of database aside from the commonly used MySQL database. You might work on a project that uses another type of database like MongoDB or PostgreSQL

In this article, we will tackle how to import or restore a PostgreSQL backup using two different ways.

Table of Contents

Prerequisites

• PostgreSQL installed
• PostgreSQL user credentials
• A PostgreSQL backup file; if you don't already have one, SimpleBackups provides an automated PostgreSQL backups
• An existing PostgreSQL database

Understanding Backup Types:

PostgreSQL backups can be generated in different formats using pg_dump. Understanding these formats is crucial for choosing the correct restoration method:

1. SQL script file: Plain text file containing SQL commands.
2. TAR file, Directory, or Custom format: These formats require a different approach for restoration.

How to restore a PostgreSQL backup

There are two ways to restore a PostgreSQL database:

1. psql - for restoring from a plain SQL script file that is created using pg_dump
2. pg_restore for restoring from a .tar file, directory, or custom format created using pg_dump

Restore a database with psql

1. Create a new database where you will restore your backup, or use an existing database.

2. Run the following command in your terminal:

   psql -U db_user db_name < dump_name.sql

   where db_user is the database user, db_name is the database name, and dump_name.sql is the name of your backup file.

Restore a database with pg_restore

If you choose custom, directory, or archive format when creating a backup file, then you will need to use pg_restore in order to restore your database.

To restore your backup, run the following command in your terminal:

pg_restore -d db_name /path/to/your/file/dump_name.tar -c -U db_user

where db_user is the database user, db_name is the database name, and /path/to/your/file/dump_name.tar is the full path of your backup file.

Using pg_restore provides you various options, for example:

• -c to drop database objects before recreating them,
• -C to create a database before restoring into it,
• -e exit if an error has encountered,
• -F format to specify the format of the archive.

Use pg_restore --help if you want to get the full list of available options.

Common Issues and Solutions in PostgreSQL Backup Restoration

In the process of restoring PostgreSQL backups, users might encounter several issues. Below are some common problems, their suggested solutions, and example error messages:

Issue: Permission Denied Error

• Error: psql: FATAL: permission denied for database "db_name"
• Description: This error occurs when the user does not have the necessary permissions to access the database or the backup file.
• Solution: Ensure the user specified in the psql or pg_restore command has appropriate permissions. For file access issues, verify the file permissions and adjust them as needed using chmod.

Issue: Database Does Not Exist

• Error: psql: error: could not connect to server: FATAL: database "db_name" does not exist
• Description: An error indicating that the specified database does not exist on your PostgreSQL server.
• Solution: Before restoring, create the database using CREATE DATABASE [db_name]; or use the -C flag with pg_restore to create the database automatically.

Issue: Corrupt Backup File

• Error: pg_restore: [archiver] could not read from input file: end of file
• Description: Restoration fails due to corruption in the backup file.
• Solution: Verify the integrity of your backup file. If possible, generate a new backup and attempt restoration again. Regularly testing backups is crucial to ensure their reliability.

Issue: Version Mismatch

• Error: pg_restore: [archiver] unsupported version (1.13) in file header
• Description: Occurs when there's a mismatch between the PostgreSQL version used for backup and restoration.
• Solution: Ideally, use the same PostgreSQL version for both backup and restoration. If that's not possible, consider upgrading the database or using tools designed to handle version discrepancies.

Issue: Insufficient Disk Space

• Error: pg_restore: [tar archiver] could not write to output file: No space left on device
• Description: Restoration fails because there is not enough disk space on the server.
• Solution: Free up disk space or add more storage to your server before attempting to restore the backup again.

Issue: Connection Timeouts

• Error: psql: error: could not connect to server: Connection timed out
• Description: The restoration process is interrupted due to connection timeouts.
• Solution: Check the network stability and server load. Adjust the timeout settings if necessary and ensure a stable network connection during the restoration process.

Issue: Encoding Mismatches

• Error: pg_restore: [archiver] input file appears to be a text format dump. Please use psql.
• Description: Errors related to encoding mismatches during restoration.
• Solution: Ensure that the database encoding matches the encoding used in the backup. You can specify the encoding during database creation or adjust the client encoding settings.

By addressing these common issues, users can more effectively manage the challenges associated with restoring PostgreSQL backups. It's always recommended to have a thorough understanding of the backup and restoration processes and to regularly test backups for integrity and reliability.