Restoring a PostgreSQL Database from a Backup File

How to restore a PostgreSQL Backup file using the command line?

Restoring a PostgreSQL database from a backup file can be essential for database recovery or migration. PostgreSQL provides the pg_restore and psql command-line tools for restoring backups created with pg_dump or other PostgreSQL utilities.

Description

To restore a PostgreSQL backup, use either the pg_restore or psql command. The command depends on the format of the backup file. Use pg_restore for custom or tar format backups created with pg_dump -Fc or pg_dump -Ft. For plain-text backups, use psql.

Syntax:

1. Using pg_restore:

pg_restore -U username -d database_name /path/to/backup_file

• This command is used with custom or tar format backups.

2. Using psql:

psql -U username -d database_name -f /path/to/backup_file.sql

• This command is used with plain-text backups.

Example: Restoring a PostgreSQL Database from a Backup File

Example 1: Restore from a Custom Format Backup using pg_restore

Code:

# Restore a custom format backup to a PostgreSQL database
pg_restore -U postgres -d mydatabase /backups/mydatabase.backup

Explanation:

• -U postgres: Specifies the PostgreSQL user.
• -d mydatabase: Specifies the target database where the backup will be restored.
• /backups/mydatabase.backup: Path to the custom format backup file.

Example 2: Restore from a Plain-Text Backup File using psql

Code:

# Restore a plain-text SQL backup file
psql -U postgres -d mydatabase -f /backups/mydatabase.sql

Explanation:

• -U postgres: Specifies the PostgreSQL user.
• -d mydatabase: Specifies the target database.
• -f /backups/mydatabase.sql: Path to the plain-text backup file.

Example for restoring a custom format backup file using pg_restore

Code:

# Run the pg_restore command
# -U specifies the PostgreSQL user
# -d specifies the database where the backup will be restored
# Replace "/backups/mydatabase.backup" with the actual backup file path
pg_restore -U postgres -d mydatabase /backups/mydatabase.backup

# Example for restoring a plain-text SQL backup file using psql

# Run the psql command
# -U specifies the PostgreSQL user
# -d specifies the target database for restoration
# -f specifies the path to the SQL backup file
# Replace "/backups/mydatabase.sql" with the actual path to your SQL file
psql -U postgres -d mydatabase -f /backups/mydatabase.sql

Explanation:

• Using pg_restore: This command is best for restoring backups in custom or tar formats. It recreates the database objects and data as specified in the backup.
• Using psql: This command is used with plain-text backups and reads SQL commands from the file, executing them to recreate the database contents.

Note: Ensure the database exists before restoring. To create a new database, use createdb mydatabase or add the -C option with pg_restore to automatically create it.

All PostgreSQL Questions, Answers, and Code Snippets Collection.