Backup Remote DB & Restore It To Your Dev Env

Over the past month or so I have deployed my FlexBlogr Ruby on Rails based blogging application to a Virtual Private Server (VPS) hosted by Digital Ocean.  I've migrated all of my previously created markdown files from my Octopress based site to this one here at ognt.io.  Along the way I discoverd some issues and bugs that were not caught nor thought of when I developed FlexBlogr.  

In deveolopment, although I had created some sample post records, I didn't realize some things I missed or didn't think about until I had actual data.  As I worked on issues and feature enhancements on my laptop, I thought "...you know, this would be better if I had some real data to work with."  And that is why I decided to figure out how to get my data off of my production server and onto my development laptop.

In summary, I am going to review how I did the following

1.  Backup database for OGNT.io (flex-production) on Digital Ocean VPS
2.  Copy the backup file from the Digital Ocean VPS to my laptop (SFTP)
3.  Drop my development database on the laptop (kills database, tables and data)
4.  Create an empty database
5.  Restore the database, including tables, data, etc

Note:  For the database steps below, we are using commands related to Postgres.  If you are using a different database platform, the database related commands will most likely not work for you.

The first thing I did was to login via ssh to my Digital Ocean VPS.  And although the first step below is not required, I feel it helps me to get things into context:

cd /var/www/ognt.io
pg_dump flex-production > ognt_backup_12062015

What did we just do?  We changed to the top level directory to where my OGNT.io site lives on the server, and I used the pg_dump command to make a backup copy of the flex-production database and named it ognt_backup_12062015.

Next we will transfer that backup file from the remote server to our local development machine.  I used the Transmit application from Panic.  Transmist supports remote file transfer of files using FTP/SFTP/AmazonS3/WebDAV.  If you do not have the Transmit application and want a free solution, you could try the following:

#ALTERNATIVE:
➜  ~  cd ~/Documents
➜  Documents  sftp -P [your_port_num (default is 22)] [your_domain_or_ip_address_here]
Connected to [your_domain_or_ip_address_here]
sftp> cd /var/www/ognt.io
sftp> ls
html  ognt_backup_12062015  oldguynewtrick
sftp> get ognt_backup_12062015
Fetching /var/www/ognt.io/ognt_backup_12062015 to ognt_backup_12062015
/var/www/ognt.io/ognt_backup_12062015                                                 100%  222KB 221.7KB/s   00:00
sftp> quit
➜  Documents
➜  Documents  ls -la ognt_backup_12062015
-rw-r--r--  1 jfhogarty  staff  226969 Dec  7 16:16 ognt_backup_12062015
➜  Documents

Note:  If you have trouble using SFTP, make sure that your host machine has it enabled/running and that you are using the correct port number.

Ok, now that we have a copy of the backup file on our development machine, we need to prepare our local copy of Posgres for the restoral from the production backup file.  We need to navigate to where the development copy of our application is located and drop the current development database:

cd /Users/jfhogarty/Document/RoR/ognt_fbrr
dropdb flex-dev

Above we first changed to the appropriate directory.  Then we used the dropdb command, with the target database name - flex-dev.  The dropdb command is a tool that comes with Postgres.

Now that we have removed the previous development database, flex-dev, we next need to create a blank database for our restoration to populate.  We will use rails to help us with this task:

rake db:create

Ok, we are almost done.  The last thing that we need to do is to restore the production data from our backup file to our target development database:

psql flex-dev < /Users/jfhogarty/Documents/RoR/ognt_backup_12062015

Fire up your rails application (rails server / thin start / etc) and you should now see that all of your data from your production server is now available to your development version.

One thing that occurred to me during this process is that I probably should put together some type of backup plan for my production database.  Keep an eye out here, as I will write about how I solved that task in a future blog posting.

Learn Something New Everyday

Last Edited by: John on December 13, 2015