Syncing PostgreSQL database from heroku

Ademar Tutor | Jan 10, 2015




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}

Remember to exit psql, do: \q

4. Now to create the app database again, do: rake db:create

5. Finally to restore the dumpfile, do:

psql -U {user-name} -d {desintation_db} -f {dumpfilename.sql}

And that's it!