1

I want to update my local posgres database nightly from my Heroku Postgres database.

I may have apps that are connected to the local database at the time. I don't want to have to remember to close and restart them. I don't want to keep changing my app's database name.

How can I restore the database with minimal intervention and downtime?

Heather Piwowar
  • 341
  • 2
  • 6

1 Answers1

1

Put this in a bash file and call it from a cron job that'll run when you don't mind having the database change out from under you:

# copy local db from server into a file
curl -o fresh.db `heroku pgbackups:url --app myapp`

# make a new local database with a temporary name
psql -c 'CREATE DATABASE freshdb;'

# restore the fresh database from the file
pg_restore --verbose --clean --no-acl --no-owner -h localhost -d freshdb fresh.db

# kill all connections to the local postgres server so we can rename the database
# from http://stackoverflow.com/a/13023189/596939
cat <<-EOF | psql  
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
where pg_stat_activity.datname = 'workingdb'
EOF

# make the fresh database the active one, drop the old one
psql -c 'ALTER DATABASE workingdb RENAME to olddb;'
psql -c 'ALTER DATABASE freshdb RENAME to workingdb;'
psql -c 'DROP DATABASE olddb;'

# clean up temporary files
rm fresh.db

Your app should be set up to automatically reconnect to the database when it notices it has lost a connection; you're all set! Next time you use your app locally it'll have an updated copy of the db.

Heather Piwowar
  • 341
  • 2
  • 6