Backup and Restoring Databases in Postgres

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

 

 


One thought on “Backup and Restoring Databases in Postgres

Your Thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s