Syncing PostgreSQL database from Ubuntu Server to your local Rails environment

I was working on Rails project that aims to greatly improve the productivity lawyers in handling their email. In the alpha phase of the project, it had a bug that I could not replicate locally.

My only option was to copy data from the testing server. The project was using PostgreSQL and deployed on an Ubuntu server. What I needed to was create a backup of the testing server data and copy it to my local development environment.

Creating a PosgreSQL backup

The first thing I had to do was create a backup for the existing test server:

  1. SSH into your test server: ssh {username}@{server_ip}
  2. Use pg_dump: pg_dump {source_db} -f {dumpfilename.sql}

If you encounter an error regarding user permissions just like me, all you need to do is specify the database user: pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

Try doing an ls to check if .sql exists.

The next thing you need to do is copy the dumpfile into your local environment. In your local terminal type: scp ~/location/to/download [username]@[server_ip]:/location/of/dumpfilename.sql

If you are working on application that even at alpha stage involves Gigabytes of data, try using compression: scp -C ~/location/to/download [username]@[server_ip]:/location/of/dumpfilename.sql

There are other faster alternatives to SCP, but so SCP fits the bill for me at this stage of the application.

Now that you have a copy of the dumpfile locally, what you need to do is restore the dumpfile to your local environment.

To avoid any conflicts, I usually drop the existing postgresql in my local environment.

  1. In terminal, type: rake db:drop
  2. Next, do: psql -U postgres
  3. Then, do: dropdb {name of database}
  4. Remember to exit psql, do: \q
  5. Now to create the app database again, do: rake db:create
  6. Finally to restore the dumpfile, do: psql -U {user-name} -d {desintation_db} -f {dumpfilename.sql}