Restoring a Postgres Database from a 'pg_dump' File
April 08, 2023
I recently closed down a Heroku-hosted database, and wanted to access the data on my own computer using localhost. Here are the steps of how to do it, courtesy ChatGPT:
CPGT Instructions
To restore a PostgreSQL database from a backup file to a local PostgreSQL server on your computer, you can use the pg_restore command. Here’s an example command that you can use to restore the Heroku database backup file to a local PostgreSQL server:
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U postgres -d your_database_name /path/to/heroku_database_backup_file.dump |
This command assumes that you have already installed PostgreSQL on your local computer and created a new empty database with the name “your_database_name” to restore the data into.
Here’s what each option in the command means:
--verbose: Prints detailed output as the restore operation progresses.--clean: Drops any existing schema and data before restoring the backup file.--no-acl: Disables the restoration of access control lists (ACLs).--no-owner: Disables the restoration of ownership information.-h localhost: Specifies that the PostgreSQL server is running on the local computer.-U postgres: Specifies the username to use when connecting to the PostgreSQL server (in this case, the default username “postgres”).-d your_database_name: Specifies the name of the database to restore the backup file into./path/to/heroku_database_backup_file.dump: Specifies the path to the backup file that you downloaded from Heroku.
Note that the file extension of the backup file may be different depending on the format of the backup. The file extension “.dump” is used here assuming that it’s in the custom format generated by the pg_dump command. If the backup file has a different format, you’ll need to adjust the file extension accordingly.
Note on File Extension
The Heroku backup dump file doesn’t actually have a file extension at all; this command worked fine with the file not having a file extension.
Also, a reminder that any spaces in the backup file directory path will need to be escaped.