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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
