Re: 9.1.2: Preventing connections / syncing a database - Mailing list pgsql-general

Greg Sabino Mullane wrote:
> update pg_database set datallowconn = false where datname = 'foobar';

That's perfect - thanks.  Now I can (I think) do this:

pg_restore -d rails_dev_new
[wait]
psql template1
   update pg_database set datallowconn = false where datname = 'rails_dev';
   select pg_terminate_backend(procpid) from pg_stat_activity where \
     datname='rails_dev';
   begin;
   alter database rails_dev rename to rails_dev_old;
   alter database rails_dev_new rename to rails_dev;
   commit;
   drop database rails_dev_old;
   \q

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

Bucardo looks great for replication, but it mentions that it won't do DDL. I
think that means if someone added a new column to production yesterday, but
I haven't run that migration yet locally, Bucardo will choke when it tries
to sync.. ya? (Though the easy workaround is run the darn migration first.)

By "snapshots", do you mean filesystem-level snapshots like XFS or LVM? OS X
has no support for either, sadly.

If you mean Postgres snapshots (and can I mention that I don't yet
understand where MVCC snapshots meet WAL/xlog, but let's say snapshots are a
thing), I see in the Slony docs that:

 > WAL-based replication duplicates absolutely everything, and nothing extra
 > that changes data can run on a WAL-based replica.

That sounds like I couldn't use production log-shipping to sync test
databases.  Unless that doc is not *quite* true, and I could somehow:

- Sync from production
- Take a snapshot (whatever that means, exactly)
- Do whatever with the test database
- Tomorrow, just before I sync, roll back to that snapshot
- Repeat

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

Yeah, it's working for now, but we're at that hockey-stick point on the
curve where what works now will be too slow three months from now, so I want
to start thinking ahead.

Those are good ideas; I bet the pg_restore can be much faster with giant
checkpoints, lots of memory, etc.  I also see Bucardo's split_pg_dump, which
would probably help - no point creating indexes on-the-fly.

Jay