Prev: Differential backup
Next: pgsql: MakeCheckRequiredParameterValues() depend upon correct
From: Heikki Linnakangas on 29 Apr 2010 06:46
Simon Riggs wrote:
> The whole point of
> wal_level discussion was to avoid needing multiple switches to turn
> something on.

No, the point of wal_level was to make the behavior easier to
understand, by uncoupling the level of WAL-logging from various other
switches, controling it directly and explicitly with a new GUC instead.
It added a new switch, but made the system as a whole easier to
understand and configure.

>> > I can't imagine a situation where "allow connections if the WAL allows
>> > it" would be a sensible setting. If there is one, we could have an
>> > 'auto' mode, but not as the default.
>
> I can. Simple, obvious behaviour. Turn it on in the master, works on the
> standbys.

Yes, but when would you want that?

Here's the use cases I can think of:

purpose of the standby - do you want hot standby or not?
reporting - yes
offloading queries from master - yes
warm standby for high availability - no
offloading taking filesystem-level backups from master - no
offloading pg_dump from master - yes

All of those either want hot standby, or don't. What use case is there
for "enabled, if the required information is in the WAL"? If there is
one, it sure doesn't seem like the most common one. When you just want
hot standby to be on or off, it's weird to control that from the master.
Action at a distance.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Heikki Linnakangas on 29 Apr 2010 07:55
Simon Riggs wrote:
> On Thu, 2010-04-29 at 13:46 +0300, Heikki Linnakangas wrote:
>> warm standby for high availability - no
>> offloading taking filesystem-level backups from master - no
>
> These can be explicitly turned off.
> You're presuming there is benefit in turning recovery_connections = off,
> though it is perfectly valid to do those use cases with it on. There are
> many ways to control connections, not just that switch. It will
> certainly be easier to monitor the HA system by running queries against
> it than not. Do you have any evidence there is benefit in the *typical*
> case for turning the setting off?

It depends on your exact configuration, but one typical one is that you
have a work-balancing router or pgbouncer sitting in front of the
servers, directing traffic to the server that's up and running. If the
standby starts accepting connections prematurely, the clients will be
incorrectly routed to the standby server and update operations will fail
(and SELECTs will return slightly delayed data).

>> All of those either want hot standby, or don't. What use case is there
>> for "enabled, if the required information is in the WAL"? If there is
>> one, it sure doesn't seem like the most common one.
>
> I think "I want it to just work" is fairly common.

You need quite a bit of set up anyway, flipping one more GUC hardly
makes a difference. There is less risk of oversight and accidental
misconfiguration if the admin makes a conscious decision to turn it on.

I'd like to scaremonger with the following fictional story:

An administrator sets up two PostgreSQL servers in a high availability
warm standby set up. Clients are set up to try to connect to both
servers, so that when failover happens, they will automatically
reconnect to the remaining server. wal_level is set to 'archive' in the
master, and all is well.

After running successfully for six months in production, a reporting
server is introduced to offload heavy queries from the mission-critical
OLTP server. wal_level is set to 'hot_standby' in the master to allow
read-only queries to be run against the reporting server, and the
reporting server is set up using the same WAL archive used for the warm
standby server. All seems to be running well, the admin logs in to the
application and clicks through a few screens to test it. A few hours
later a user rings and complains that he's getting a "cannot execute
INSERT in a read-only transaction" error. What happened, and why does it
work just fine when the admin tries the same?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Heikki Linnakangas on 29 Apr 2010 08:54
Simon Riggs wrote:
> recovery_connections was on by default and unanimous agreement until
> recently and I don't want to change that now, just because a change
> somewhere else appears to be forcing that but need not be so.

We never really had that discussion, until now. It has always been 'on'
by default, and that has been useful to get more testing during the
development cycle, but it's not clear that's a good default for
real-life usage.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Heikki Linnakangas on 29 Apr 2010 11:40
Simon Riggs wrote:
> On Thu, 2010-04-29 at 09:48 -0400, Tom Lane wrote:
>> Simon Riggs <simon(a)2ndQuadrant.com> writes:
>>> recovery_connections was on by default and unanimous agreement until
>>> recently and I don't want to change that now,
>> Uh, it was on by default only because a lot of us hadn't noticed that.
>> I agree with Heikki's position here: it should not be on by default.
>
> We're talking about the case where somebody has set up a standby
> database. It's not like they happen on this accidentally.
>
> * HS on by default, in the standby, via recovery_connections.
> * HS off by default, in the master, via wal_level.
>
> Overall, that *is* off by default. (Note: I said nothing about that).
>
> We don't need it off *twice*, nor do we even need two switches.

wal_level is not supposed to control if Hot Standby is on or off. It
controls what information is written to WAL. If you have
wal_level='archive' and recovery_connections='on' in the standby, that's
a configuration error, just like setting wal_level='minimal' and
archive_mode='on'. You wanted to enable Hot Standby, but the information
required isn't in the WAL.

It's error-prone to control what happens in the standby from the master.
That's the "action at a distance" effect I mentioned earlier. The master
should be configured in the master, and each standby should configured
in the standby.

The right mental model is that wal_mode controls what is written to the
WAL. In fact, I might recommend always setting it to 'hot_standby'
instead of 'archive', even if you have no standbys and you're only doing
WAL archival - it's a lot easier to do PITR with hot standby. To control
whether hot standby is enabled in the standby, use recovery_connections.
And I'd prefer it to be off by default because off is the safer option.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Heikki Linnakangas on 29 Apr 2010 12:23
Simon Riggs wrote:
> On Thu, 2010-04-29 at 18:40 +0300, Heikki Linnakangas wrote:
>
>> It's error-prone to control what happens in the standby from the master.
>> That's the "action at a distance" effect I mentioned earlier. The master
>> should be configured in the master, and each standby should configured
>> in the standby.
>
> Repeating the same thing when its been refuted doesn't help. What you
> say has not been proposed.

I was responding to your mail where you said that there is two settings
for turning hot standby off, and asking why we need two. What I'm saying
is that you shouldn't think of wal_level as a setting to turn hot
standby on or off. It would be error-prone to control that from the
master. So there is only one setting to turn hot standby on/off,
recovery_connections in the standby.

> If there is a case for HS-off-by-default, make it. If you want to change
> code, arguing directly against your own position, mentioned many times,
> we need a reason. How else can we know which argument of yours to
> believe?

Now you lost me.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Differential backup
Next: pgsql: MakeCheckRequiredParameterValues() depend upon correct