undeniablyrob

TL;DR

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