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.
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
2. Using psql:
psql -U username -d database_name -f /path/to/backup_file.sql
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:
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:
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:
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.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4