Обсуждение: 9.1.2: Preventing connections / syncing a database

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

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

On 01/01/2012 11:51 AM, Jay Levitt wrote:
>
> 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
More along the lines of what Greg has said.  Not clear to me why an
individual dev box needs to be that current data-wise.  Of course
stopping and starting your app should be easy, especially for the
developers so maybe that's a better place to start.  Then dev can do it
when and how often suits dev best (even cronning shutdown app; reload
db; to happen 3am Sundays)

Rob Sargentg wrote:
 > Not clear to me why an
 > individual dev box needs to be that current data-wise.

It's significantly easier to debug a production problem when you can
duplicate the problem on your local machine. "Hey, when I go to JennyC's
activity page and scroll down three times, I see out-of-order postings."
Our site is a dynamic Facebook-style feed, and you're usually seeing only
the newest content, so it's a truism that any noticeable production bug will
be with some user who registered yesterday and posted something this morning.

Likewise, new code has a much better chance of working on production if it's
been tested against production data. I know big shops use fancy technology
like "test plans" and "QA people". But. (There's actually a good argument
that we don't WANT that.)

Keeping the dev boxes up to date, daily, is the poor man's regression test.

> Of course stopping
> and starting your app should be easy, especially for the developers so maybe
> that's a better place to start. Then dev can do it when and how often suits
> dev best (even cronning shutdown app; reload db; to happen 3am Sundays)

Ah ha! Clearly you don't develop on a laptop ☺  cron jobs are no longer
useful for things like that, because it's in your backpack and asleep at
3am. Yeah, it would run when you wake up, but what's more annoying than
opening your laptop and having it freeze while it swaps everything back in,
finds your network, runs your cron jobs, rearranges your windows, etc?

And yes, shutting down the app isn't as hard as I claim - it's two or three
commands - but developers are lazy and avoid friction.  If you have to stop
the app for five minutes to update, you'll "do it later". It's like waiting
for a compile; it interrupts your rhythm.  As Rails developers, we're
spoiled; there's no compile, there's no local deploy. You change a line of
source code, you switch windows, your editor automatically saves when it
loses focus, you refresh the browser, Rails automatically reloads the
changed code, you see the change. (There are three different browser
extensions that will automatically refresh your browser, too, in case that
was too hard.)

TL;DR: Reduce friction -> more frequent database updates -> fewer bugs.

> On 01/01/2012 11:51 AM, Jay Levitt wrote:
>>
>> 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
> More along the lines of what Greg has said. Not clear to me why an
> individual dev box needs to be that current data-wise. Of course stopping
> and starting your app should be easy, especially for the developers so maybe
> that's a better place to start. Then dev can do it when and how often suits
> dev best (even cronning shutdown app; reload db; to happen 3am Sundays)
>

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

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


Jay Levitt spoke:

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

Yes, but if that's truly the process, you might as well save some steps
and just drop the existing one and do a single rename:

select pg_terminate_backend(procpid) from pg_stat_activity where \
  datname='rails_dev';
drop database rails_dev;
alter database rails_dev_new rename to rails_dev;
\q

a developer will find they have something on there they need about
two minutes after you drop it. :) Space permitting, of course.

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

Yes - the canonical way is to get the schema in sync first, then let Bucardo
handle the data.

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

Yes, that's exactly what I mean. We have clients using that with great success.
Simply make a snapshot of the production database volumes, mount it on the
dev box, and go.

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

Well, you can use Postgres' PITR (point in time recovery) aka warm standby
to create standby slaves identical to the master, and then at some point
in time flip them to become live, independent databases that can be modified.
The downside is that you then have to create a new base backup, which means
rsyncing the entire data directory to a new slave/standby box. However, this
might be worth it as you can frontload the time spent doing so - once it is
rsynced and the standby is up and running (and receiving data from the master),
swtiching it from standby to nomal mode (and thus creating a perfect clone of
production at that moment) is pretty much instant.

So the process would be:
* Turn on archive_command on prod, point it to the dev box
* Create a base backup, ship the data dir to the dev box, start up the db
* In the AM, tell the dev box to go into recovery mode. Tell the
prod box to stop trying to ship logs to it
* Rearrange some dirs on the dev box, and start over again by making
a new base backup, rsyncing data dir, etc.
* In the AM, stop the old dev database. Bring the new one up (recover it)

You could even make less frequent base backups if you keep enough logs around to
play forward through more than a days worth of logs.

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

Well, if they are needed on prod, you probably want them on dev :)

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

iEYEAREDAAYFAk8CDIIACgkQvJuQZxSWSsj1cQCfdJtmW/fmgPDRYk2esngyng7a
WZMAnjafyd+EDFGVzPA/dPUUqhks9Qkb
=HJak
-----END PGP SIGNATURE-----