Subject: Re: [PATCH] Stop ALTER SYSTEM from making bad assumptions
To: Alvaro Herrera <alvherre@2ndquadrant.com>,
 Robert Haas <robertmhaas@gmail.com>, Stephen Frost <sfrost@snowman.net>
Cc: Amit Kapila <amit.kapila16@gmail.com>,
 "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
References: <20190624190625.GA10524@alvherre.pgsql>
From: Ian Barwick <ian.barwick@2ndquadrant.com>
Message-ID: <2be33909-bb7f-7ff9-d6e7-2484fd484df6@2ndquadrant.com>
Date: Tue, 25 Jun 2019 10:57:58 +0900
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.7.0
MIME-Version: 1.0
In-Reply-To: <20190624190625.GA10524@alvherre.pgsql>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

On 6/25/19 4:06 AM, Alvaro Herrera wrote:
 > On 2019-Jun-24, Robert Haas wrote:
 >
 >> On Sat, Jun 22, 2019 at 5:13 PM Stephen Frost <sfrost@snowman.net> wrote:
 >>> All that said, whatever code it is that we write for pg_basebackup to
 >>> do this properly should go into our client side library, so other tools
 >>> can leverage that and avoid having to write it themselves.
 >>
 >> That is probably only going to help people who are writing in C (or
 >> maybe some close family member) and a lot of tools for managing
 >> PostgreSQL will be written in scripting languages.
 >
 > But we already have ALTER SYSTEM, so why do we need to write it again?
 > You just need to check whether the system is running: if it is, connect
 > and do "ALTER SYSTEM".  If it isn't, do `echo ALTER SYSTEM | postgres
 > --single`.  Maybe we can embed smarts to do that in, say, pg_ctl; then
 > everybody has access to it.

Unfortunately, to quote the emitted log message, "standby mode is not
supported by single-user servers", which as-is renders this approach useless for
setting up replication configuration on a standby server (unless I'm missing
something).

I've looked in to what might be involved into creating a client-side function
for modifying .auto.conf while the system is not running, and basically
it seems to involve maintaining a stripped down version of ParseConfigFp()
which doesn't recurse (because we don't allow "include" directives in
.auto.conf, right? Right? I'll send in a different patch for that later...)
and somehow exposing write_auto_conf_file().

And for all those scripts which can't call the putative frontend C function,
we could provide a utility called "pg_alter_system" or similar which accepts
a name and a value and (provided the system is not running) "safely"
writes it to .auto.conf (though of course it won't be able to validate the
provided parameter(s)).

Alternatively (waves hand vaguely in air) there might be some way of
creating a single user startup mode for the express purpose of leveraging
the backend code to modify .auto.conf.

Bur that seems like a lot of effort and complexity to replace what, in Pg11
and earlier, was just a case of writing to recovery.conf.

Which brings me to another thought which AFAIK hasn't been discussed -
what use-cases are there for modifying .auto.conf when the system isn't
running?

The only one I can think of is the case at hand, i.e. configuring replication
after cloning a standby in a manner which *guarantees* that the
replication configuration will be read at startup, which was the case
with recovery.conf in Pg11 and earlier.

For anything else, it seems reasonable to me to expect any customised
settings to be written (e.g. by a provisioning system) to the normal
configuration file(s).

Having pg_basebackup write the replication configuration to a normal file
is icky because there's no guarantee the configuration will be written
last, or even included at all, which is a regression against earlier
versions as there you could clone a standby and (assuming there are no
issues with any cloned configuration files) have the standby start up
reliably.


Regards

Ian Barwick

--
  Ian Barwick                   https://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services