You made it! You’ve launched your e-commerce platform and now you have millions of users accessing it every day. Your site is flooded with users’ information, orders, items, promotions.
That data is tremendously valuable. Lose it and your business is in serious trouble. You have to backup your database.
A Sample Application
We will be using the storedom Rails application for these exercises. Start by cloning this repository:
Once you cloned the repo, make sure that you install all the gems.
Creating Some Data
We are going to create the dabase, run the migrations and seed it with fake information. We have prepared a seed file to do that for you. To setup the database, just run
Once it finishes, you’ll have 50 users, 100 orders and 500 items in your database.
If you want to see the data, just start your rails server and visit
PostgreSQL gives you a handy utility to do that called
pg_dump. This utility makes consistent backups even if the database is being used while the backup is being run.
pg_dump does not block other users accessing the database (readers or writers).
Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database at the time it was saved.
Script files can be used to reconstruct the database even on other machines and other architectures, and in some cases, even on other SQL databases.
Dumping the Data
pg_dump to get all your database data into a file.
-Fconfigures the file format of the dump file. It can be a tar (t), plain (p) or custom (c).
-Uconfigures the username. In this case, our database doesn’t have any.
-hspecifies the host name of the machine on which the server is running. When not specified, it connects to localhost.
-pspecifies the TCP port or local socket file extension on which the server is listening for connections. If not specified, it will use the default, which for most purposes will work.
After the options, we need to specify which database we want to back up. In this case, it is
> means pipe the output into the specified file. If you don’t do that it will output to
STDOUT, which usually isn’t what you want.
After you run the command, you will be able to see a
development_backup.tar file in your directory. This file contains your database data.
Dropping the database
Now, we are going to drop your existing database to check that we actually restored the data from our backup.
Restart the server, and visit
localhost:3000. You should see an error message that says
FATAL: database "db/development" does not exist.
Your data is gone.
Restoring the Database
Fortunately, we have your information backed-up. Let’s restore the database by using
-CCreate the database before restoring into it. If –clean is also specified, drops and recreate the target database before connecting to it.
-d <dbname>Connect to database
dbnameand restore directly into the database.
Now, start your rails server. Visit
localhost:3000… and your information is back.
In the Wild
Manipulating real data is a very delicate affair. Before attempting any of these steps, you should backup your information. Also, be sure that your database backups are stored in a secured place. Otherwise, your users’ information might be exposed.
Heroku Postgres Backups
Let’s say that you have an application running on Heroku. You can create a backup by running
PG Backups. First, let’s install the addon first.
From your application folder, run the following command:
Once that you have the addon, create a copy of your database information by running the following command:
PG Backups uses pg_dump to create its backup files, making it trivial to export to other PostgreSQL installations.
Now that you have created the backup, let’s get your backup locally via
If you run
ls on your terminal, you’ll see a
latest.dump file listed. That is the local copy of your database.
Restoring your Database on Heroku
To restore your database, you will need to upload your dump file to a remote host such as Amazon Web Services. You will need to access that link when running
In this example,
HEROKU_POSTGRESQL_IVORY_URL is the database name given to us by Heroku. Make sure that you change that name to your existing database.
Now, if you visit your app, you’ll see all the data restored.