Import production Postgresql database into the development environment

Suppose you’ve configured both the development and production environments for your Rails application that uses Postgresql.

The production environment is hosted on a server whose IP is accessible from your development environment.

For example, let’s assume you’ve used the awesome Digital Ocean (yep, these are referral links <3)

And that you’ve configured your production environment to perform periodic backups, for example by calling the following script via cron:

#!/bin/bash

RAILS_APP_NAME="XXX"
DEPLOYER_USERNAME="YYY"
DATABASE_NAME="$RAILS_APP_NAME_production"

filename="/home/$DEPLOYER_USERNAME/backups/db/$(date --utc +"%Y%m%d%H%M%S")[$DATABASE_NAME].sql"
pg_dump $DATABASE_NAME > $filename
bzip2 $filename

Obviously, change XXX and YYY according to your setup.

Also, be sure to add your local ssh pub key to the production ~/.ssh/authorized_keys so that you won’t get asked to insert the ssh password every time.

Now, within the bin/ directory, contained in your Rails application structure, you can create the following script:

#/bin/bash

DEPLOYER_USERNAME="YYY"
PRODUCTION_PG_ROLE=$DEPLOYER_USERNAME
DEVELOPMENT_PG_ROLE="ZZZ"
RAILS_APP_NAME="XXX"
PRODUCTION_HOST="KKK"

echo "Make sure you're not running the server or the console (Spring should be stopped, too)."

date
echo "Fetching the freshest backup from the server."
filename=$(ssh $DEPLOYER_USERNAME@$PRODUCTION_HOST "ls ~/backups/db" | sort | tail -n 1)
scp "$DEPLOYER_USERNAME@$PRODUCTION_HOST:~/backups/db/$filename" tmp/
bzip2 -d -f "tmp/$filename"

echo "Recreating local database."
bundle exec rake db:drop
bundle exec rake db:create

extracted_dump_filename=$(ls "tmp/*.sql" | sort | tail -n 1)

echo "Setting local owner."
sed -i "s/OWNER TO $PRODUCTION_PG_ROLE/OWNER TO $DEVELOPMENT_PG_ROLE/g" $extracted_dump_filename

echo "Importing data into the local database."
psql --quiet --username="$DEVELOPMENT_PG_ROLE" --set ON_ERROR_STOP=on "$RAILS_APP_NAME"_development < $extracted_dump_filename > tmp/db_import.log

echo "Done."
date

The only trickier part is the string replacement:

sed -i "s/OWNER TO $PRODUCTION_PG_ROLE/OWNER TO $DEVELOPMENT_PG_ROLE/g" $extracted_dump_filename

and this is needed because when exporting the database with pg_dump you obtain a dump file containing the following commands:

ALTER TABLE public.JUSTATABLENAME OWNER TO PRODUCTION_DEPLOYER_ROLE;

And since PRODUCTION_DEPLOYER_ROLE can differ from production to development, you need to change every occurrence of this kind so that when we use psql to import the dump, it correctly sets the local role as the owner of the tables.

This is just a hack and I’d be more than happy to use a more clean approach ;-)

Finally do a chmod u+x bin/my_import_script and perform a ./bin/my_import_script every time you want to import the production database into your development environment.