1. pg_dump -d my_database > my_backup_script.sql 2. rake db:create OR psql: CREATE DATABASE my_database_name 3. psql my_database_name < my_backup_script.sql
Running into issues, or want to understand in more detail? Let’s dig in some more.
Step 1: Backup Your Data
Open up your favorite terminal. Make sure your database server is running (in Docker, in Homebrew, etc.). We’ll be using the pg_dump command. Back up your database’s schema and its data to a file:
pg_dump -h localhost -d my_database > my_backup.sql
You may not need to explicitly specify the host when it’s localhost; I do this out of habit. Notice that the backup file is nothing scary or proprietary. It’s just a giant SQL script. If you only want to backup your data:
pg_dump -h localhost -d --data-only my_datbase > my_backup.sql
Step 2: Create Your New Database
You need an empty database to restore to. In other words, the restore will fill in the schema and populate the data, but it does not create the initial, empty database (unless you use pg_dumpall).
If you have a task runner (e.g., Rake for Rails and
rake db:create) that can create an empty database for you, I recommend using that. Otherwise, you can use your favorite database editor (
psql, PopSQL, etc.), and manually create the database. For example,
psql -h localhost and
CREATE DATABASE my_database_name.
Step 3: Restore Your Schema and Data
Let the backup script do the heavy-lifting. Do not run migrations through your ORM. You can run migrations afterwards just fine (as the backup will restore your ORM history tables automatically).
psql -h localhost my_database < my_backup.sql
One thought on “Backup and Restoring Databases in Postgres”