Our development workstations maintain a local copy of the production
database (which has been small enough that this is workable right now, and
preferable to having a test database with generated fake data). We've been
doing this by rsync'ing a compressed pgdump from the production server,
dropping and recreating the database, and doing a pg_restore. This is
finally causing enough pain that it's worth revisiting:
1. You can't drop a database with open connections; as our app gets more
complex, it gets more painful to bring all of it down and back up again,
since it's so darned smart about reconnecting automatically, and we use
secondary components like Sphinx and queue-runners which also open the
database but which are launched under separate cover.
2. pg_restore on a 400MB database is starting to take a few minutes.
My intent was to only worry about #1 for now. With flat files, you usually
solve this by renaming the file away and then creating a new one. But
Postgres is smarter than me, and you can't rename a database with open
connections either. Phooey.
So I tried this:
alter database rails_dev connection limit 0;
select pg_terminate_backend(procpid) from pg_stat_activity where
datname='rails_dev';
But the app was still able to reconnect. Hmm. Then I tried this:
revoke connect on database rails_dev from public;
select pg_terminate_backend(procpid) from pg_stat_activity where
datname='rails_dev';
Still, the app can reconnect. (So can psql.)
So...
1. How can I prevent (or redirect, or timeout, or anything) new connections?
I think superuser roles might be exempt from connection limits and
privileges. I could repeatedly terminate backends until I'm able to rename
the database, but... ick.
2. What's a better way to slave to a master database without being a
read-only slave? In other words, we want to use the production database as
a starting point each morning, but we'll be creating test rows and tables
that we wouldn't want to propagate to production. Can I do this while the
database is live through some existing replication tool? The production
database is on Ubuntu but the workstations are Macs, FWIW.
Jay