Subject: Re: Re: Continue work on changes to recovery.conf API
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Stephen Frost <sfrost@snowman.net>, Andres Freund <andres@anarazel.de>,
 Michael Paquier <michael@paquier.xyz>, Sergei Kornilov <sk@zsrv.org>,
 PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
References: <51a6446c-c2b6-b653-c9f5-da38e23ddd9d@2ndquadrant.com>
 <22367601542121030@iva5-d3020dc3459d.qloud-c.yandex.net>
 <2d1877b6-e8cf-a27c-4608-c40758dd0f6c@2ndquadrant.com>
 <20181121060012.GE1951@paquier.xyz>
 <d7f0860c-65d6-5346-c672-3b1116b20ac4@2ndquadrant.com>
 <20181125182415.GP3415@tamriel.snowman.net>
 <20181125203927.f66lvh55ef5aw65g@alap3.anarazel.de>
 <ec74a8ae-8d25-6d60-1db4-907c7999acbb@2ndquadrant.com>
 <0ea1b300-38b5-bd96-1fa0-784729160efa@pgmasters.net>
 <26178006-74c0-4599-0125-6bf9eb489e96@2ndquadrant.com>
 <20181127153405.GX3415@tamriel.snowman.net>
From: David Steele <david@pgmasters.net>
Message-ID: <c8738383-57a7-d891-1eec-e5dcaa3b2b11@pgmasters.net>
Date: Thu, 28 Feb 2019 17:41:19 +0200
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:60.0)
 Gecko/20100101 Thunderbird/60.5.0
MIME-Version: 1.0
In-Reply-To: <20181127153405.GX3415@tamriel.snowman.net>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hi Peter,

On 11/27/18 5:34 PM, Stephen Frost wrote:
> Greetings,
> 
> * Peter Eisentraut (peter.eisentraut@2ndquadrant.com) wrote:
>> On 27/11/2018 13:21, David Steele wrote:
>>> I would prefer a specific file that will be auto-included into
>>> postgresql.conf when present but be ignored when not present.  Some
>>> settings are generally ephemeral (recovery_target_time) and it would be
>>> nice for them to go away.  When recovery is complete the file would be
>>> renamed to .done just as recovery.conf is now.
>>
>> That might be a useful facility, but it wouldn't really address the
>> pg_basebackup -R issue, because that creates settings that you don't
>> want going away in this manner.  You'd then need two separate such
>> files, one for targeted recovery that goes away when recovery ends, and
>> one that is automatically included that pg_basebackup can overwrite at will.
> 
> I've been thinking about this also and I agree that there's some
> challenges when it comes to having another file- what happens if someone
> does an ALTER SYSTEM on primary_conninfo while it's in the
> 'recovery.auto.conf' (or whatever) file?  Does that go into
> postgresql.auto.conf, or somewhere else?  What if they do a RESET?
> 
> Then there's the other side of things- do we really want things like
> recovery_target_time being kept around in postgresql.auto.conf after a
> promotoion?  Do we want to keep appending primary_conninfo into
> postgresql.auto.conf?  I haven't looked but I'm also concerned that
> something like ALTER SYSTEM RESET isn't really prepared to find
> duplicates in postgresql.auto.conf...
> 
> Maybe thinking through what we want to have happen in each scenario
> would be good.  If you perform a pg_basebackup -R and there's already
> something set in postgresql.auto.conf for primary conninfo- what should
> happen?  If we reach the end of recovery and promote while
> postgresql.auto.conf has recovery_target_time set, what should happen?
> If third-party tools want to do what pg_basebackup -R does and modify
> things in postgresql.auto.conf, how should they do that?
> 
> Here's my thoughts on answers to the above:
> 
> - pg_basebackup -R should INSERT .. ON CONFLICT UPDATE the settings that
>    it wants to set in postgresql.auto.conf
> 
> - When we reach the end of recovery and promote, we should DELETE from
>    the postgresql.auto.conf the recovery target settings.
> 
> - External tools should either be told that they can modify
>    postgresql.auto.conf and given guideance on how to do so, or we should
>    provide a tool which allows them to do so (or maybe both).
> 
> As we already have a section for recovery target settings that clearly
> has them as independent, hopefully this will make sense to users.  Open
> to other ideas too, of course, but I don't think we can continue to just
> append things to the end of postgresql.auto.conf when pg_basebackup is
> run with -R.

I'd be interested to get your take to these questions.

Just about every third-party backup and HA tool out there writes 
recovery.conf files and automates restores.  This is a huge change.

I personally would prefer to have something like 
postgresql.recovery.conf file that is automatically included if it is 
present.  This simplifies the issue of how to maintain recovery settings 
in postgresql.auto.conf.  The file could be renamed to 
postgresql.recovery.conf.done similar to how recovery.conf is now.

Of course, some settings like primary_conninfo could just stay in 
postgresql.conf or postgresql.auto.conf since they are far less subject 
to change.  Or they could be in postgresql.recovery.conf for HA 
environments.

I don't see why this won't work with pg_basebackup -R -- it just may be 
the case that some settings get overridden.  In HA scenarios it's hard 
to see how pg_basebackup would have the correct info for something like 
primary_conninfo anyway.

I like the flexibility of recovery options being available as GUCs but 
I'm not sure the ramifications of these changes have been completely 
thought through.

Regards,
-- 
-David
david@pgmasters.net