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.
We can back up a database schema in a few steps:
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 back up 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.
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;
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 filename.sql file. This file must have a
.sql file extension to work correctly. The filename is up to you, but it is good practice to give it the same name as the database.
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.
Once we've included our backup file in a repository, we can later clone it down and use it to restore our previous tables.
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;
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
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:
All tables from your backup file should be restored.
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.