Mirror an PG database to another (e.g. production to staging).

pgmirror.rb

#!/usr/bin/env ruby
#
# This script is used to mirror an PG database to another (e.g. production to
# staging). It takes to arguments - the first is the database URL of the
# source, and the second is the the database URL of the target. The target DB
# (second argument) will be wiped out and replaced with the data of the source.
#
# Tested & works. But be cartful with the arguments!

require 'uri'

source = URI.parse(ARGV[0])
target = URI.parse(ARGV[1])

source_pg_env = "PGHOST=#{source.host} PGPORT=#{source.port} PGUSER=#{source.user} PGPASSWORD=#{source.password} PGDATABASE=#{source.path.gsub('/', '')}"
target_pg_env = "PGHOST=#{target.host} PGPORT=#{target.port} PGUSER=#{target.user} PGPASSWORD=#{target.password} PGDATABASE=#{target.path.gsub('/', '')}"

system "#{source_pg_env} pg_dump #{source.path.gsub('/', '')} -Fc > pgmirror_source"
system "#{target_pg_env} psql -c 'drop schema public cascade; create schema public;'"
system "#{target_pg_env} pg_restore pgmirror_source -n public -d #{target.path.gsub('/', '')}"