Re: 9.1.2: Preventing connections / syncing a database

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

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

Superusers have a pool of reserved connections outside of the normal
connection limits for a database. What I usually do is this:

update pg_database set datallowconn = false where datname = 'foobar';

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

How incremental does it need to be? You could use Bucardo to create
slaves that can still be written to. Then in the morning you would simply
kick off a sync to bring the slave up to date with the master (and optionally
remove any test rows, etc.) Many caveats there, of course - it depends on
your exact needs. If you have the right hardware/software, using snapshots
or clones is an excellent way to make dev databases as well.

If the pg_dump / restore is working for you, I'd keep that as long as you
can. Try fiddling with some of the compressions, etc. to maximize speed.
Quick ideas: try nc or tweak rsync, and on the slave: turn fsync off,
boost maintenance_work_mem and checkpoint_segments, look into parallel
restore.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201201011447
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk8AuRwACgkQvJuQZxSWSsgoqwCgvPuaFA30Kugof3Xpqs60PVQY
IbEAoLMMtBL97pzXeKRSthWUFN4Rr3Yh
=1HiF
-----END PGP SIGNATURE-----