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
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;
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.