Re: 9.1.2: Preventing connections / syncing a database

From: Jay Levitt <jay(dot)levitt(at)gmail(dot)com>
To: Rob Sargentg <robjsargent(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: 9.1.2: Preventing connections / syncing a database
Date: 2012-01-02 15:32:18
Message-ID: 4F01CE02.6070401@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Jay Levitt 2012-01-02 16:06:21 Re: 9.1.2: Preventing connections / syncing a database
Previous Message Nulik Nol 2012-01-02 15:06:00 asynchronous api questions