Subject: Re: Recovery from WAL archives not advancing timeline?
To: Don Seiler <don@seiler.us>
Cc: Ian Lawrence Barwick <barwick@gmail.com>,
 Pgsql-admin <pgsql-admin@lists.postgresql.org>
References: 
 <CAHJZqBBk1cgbvXevUuAGTsbMTYx6_BDwwUqV_4ne_QUG=AuMmw@mail.gmail.com>
 <CAB8KJ=iQWTop4Ekb1XaKCBm4RU0Jpqte6vUKH3E0BGZvS5Ukfg@mail.gmail.com>
 <CAHJZqBDVajF1zuVbomNL=Jp2QkoFN4uNxnsMZdvw8b-v7D1DBQ@mail.gmail.com>
 <CAB8KJ=jsEKz6GY0d+bi6Q2_U5_GDSJkaOO8Mh2V+rQ-JHM6_vg@mail.gmail.com>
 <CAHJZqBB-TXuPS4R2M9N0mHSQUc8OjNwCkttE4q21-Cskhe+kbg@mail.gmail.com>
From: Ian Barwick <ian.barwick@2ndquadrant.com>
Message-ID: <e920ad47-81a4-cffb-75b9-93a5fa47e9de@2ndquadrant.com>
Date: Tue, 11 Aug 2020 10:08:23 +0900
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: 
 <CAHJZqBB-TXuPS4R2M9N0mHSQUc8OjNwCkttE4q21-Cskhe+kbg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 2020/08/09 0:59, Don Seiler wrote:
> On Sat, Aug 8, 2020 at 10:34 AM Ian Lawrence Barwick <barwick@gmail.com <mailto:barwick@gmail.com>> wrote:
> 
>     2020年8月9日(日) 0:12 Don Seiler <don@seiler.us <mailto:don@seiler.us>>:
>      >
>      > There's no attempt to look for 00000002.history that I would normally expect when a replica doing WAL restore/recovery runs of out of files to restore.
> 
>     In that case are you
>     a) absolutely sure recovery.conf contains "recovery_target_timeline = 'latest'"?
>     b) the standby was restarted *after* the change was made?
> 
>     I ask because not attempting to fetch a history file would be a sign
>     that "recovery_target_timeline"
>     is not set (or set but not to "latest").
> 
> 
> I'm 100% confident that it was set, as I did it via script that wrote the recovery.conf to all while the DBs were still shut down. The file still has:
> 
> recovery_target_timeline = 'latest'

Unfortunately it's not practical to verify what setting the server has in Pg11
and earlier. Is restarting the server an option? I'm sure I'm not the only person who's
been in a situation where I'm sure I've done something and it turns out I hadn't.

The code path there should definitely attempt to fetch a history file if
"recovery_target_timeline = 'latest'", so it would be useful rule out the
possibility that the setting simply hasn't been applied before looking into
other potential causes.

> Can I set that parameter to a specific timeline (eg '2')? Or does it just take 'latest'?

Yes, you can explicitly specify the timeline, though as changing it would require a
server restart, I suggest restarting the server as-is to double-check whether
the "recovery_target_timeline = 'latest'" is being applied, and only if not
try setting it to "recovery_target_timeline = 2".

>      From the timestamps, 000000010000142E0000005C and later are files
>     which were "recycled"
>     for future use and as such are perfectly normal.

> Do you think it would be worth trying to delete (move/rename) the 000000010000142E0000005B file and see if it changes things?

It probably wouldn't do any harm in this case, but in general nothing good
ever came of speculatively moving/deleting files in the WAL directory.


Regards

Ian Barwick

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