w3resource

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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/PostgreSQL/snippets/restore-postgresql-backup-command-line.php