Lesson Tuesday

Databases can quickly grow in complexity, and it is useful for us to have a way to save our database schema for future reference and use. That way, we won't have to manually construct all the tables and columns each time we want to clone and launch our application.

In this lesson, we'll walk through the steps to back up the schema from a postgres database in order to push it to GitHub and save for later. Then, we'll learn how to restore a database using one of these backup file.

You can use these instructions to help backup a database from your classroom mac or home machine.

Backing up Databases

We can backup a database schema in a few steps:

1. (Optional) Clear Tables

First, we need to decide whether we'd like to keep the data within our database, or if we only want to keep the schema itself. If you would like to retain the contents of your database in your backup, you may skip this step.

If you would like to backup only the schema of your database without its current contents, you'll need to clear all data out of all tables. We can do this by running the following psql command:

# DELETE FROM name_of_your_table;

If your database includes more than one table, you will need to clear each one with this same command.

2. Dropping Test Databases

Next, we'll drop any test databases we no longer need. If we need them again after restoring our backup, we'll simply create them using our main database as a template. We can drop a database like this:

DROP DATABASE name_of_database;

3. Extract the Postgres Database

Now that we've removed unnecessary content, we can actually extract the tables themselves. To do this, we'll run the following command. This command needs to be executed in bash. That is, your "normal" terminal window, not psql:

$ pg_dump name_of_database > outfile

This will copy all tables in the database with the name you specified to a media.sql file. This file must have a .sql file extension to work correctly.

4. Add and Commit the Database Backup to Git/GitHub

We can now $ git add and $ git commit in order to include the media.sql file containing our extracted database in our Git and GitHub repos.

Note: If you experience any issues adding your database backup to Git, make sure you are not ignoring .sql files in your local or global .gitignore.

Restoring Databases

Once we've included our backup file in a repository, we can later clone it down and use it to restore our previous tables.

1. Create a Database

First, we must manually create our database. The backup file contains only the tables within our database.

Connect to psql and run:

# CREATE DATABASE your_database_name;

2. Restore the Tables

Now we can restore the contents of our media.sql into the database we've just created.

To do this, run the following command in the terminal (not psql):

$ psql your_database_name < media.sql

After running this command, you should see something like this:

COPY 0
 setval
--------
     17
(1 row)

COPY 3
 setval
--------
     38
(1 row)

ALTER TABLE
ALTER TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT

3. Confirm Success

Next, we just need to double check that everything was restored correctly.

If we switch to psql and run:

# \c your_database_name

To connect to the database, then run:

\dt

All tables from your backup file should be restored.

Important Note

If you change your schema (adding tables, columns or altering column types) you must run the pg_dump command (step 3 in the "Backing Up Databases" section above) on your database again and overwrite your previous .sql file. When you update the architecture of your database, the changes will not be saved to your backup file automatically. You must manually create a new backup file.

You also want to make sure that you are not discluding .sql files from being tracked by git - they are often listed in standard .gitignore files.