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
