Re: Associating a basebackup and it's .backup WAL file

That is really unfortunate. It seems it would be a nice feature for
pg_basebackup to simply create a .metadata file in basebackup output
directory or something along those lines.

Non tarballed/compressed basebackup is fine since I can read the label,
but most people probably want to always compress. I'll probably try to
get the WAL file by getting last modified .backup right after basebackup
invoke.

I also found another "automation" problem looking at PITR recovery
documentation, specifically:

"If you have unarchived WAL segment files that you saved in step 2, copy
them into pg_xlog/. (It is best to copy them, not move them, so you
still have the unmodified files if a problem occurs and you have to
start over.)"

Seems like a lot of manual work to me, to automate it I'd basically have
to diff both directories and then copy only the newest differences over
to the recovery. So far I was unable to find a supersecret git repo with
bash scripts accomplishing this tasks which is surprising.

On 07/13/2017 11:26 AM, Michael Paquier wrote:
> On Thu, Jul 13, 2017 at 10:30 AM, cen <imbacen(at)gmail(dot)com> wrote:
>> Given a basebackup base.tar.gz and an archive of WAL files, is there any way
>> to find out which .backup WAL file is associated with the basebackup from
>> command line?
> Not from what Postgres ships directly. Without any custom meta data
> save with each one of your backups, say something that you write after
> calling pg_basebackup, you would need to untar base.tar to look for
> the backup_label file.
>
>> My use case is for a retention policy bash script which:
>> -deletes all basebackups older than X days
>> -runs pg_archivecleanup for the oldest basebackup
>>
>> I just don't know how to find out which WAL to feed to pg_archivecleanup at
>> this point.
> Recalling something I know about, pg_rman uses its own meta data to do
> this decision making with dedicated folder names that use a structure
> and names based on timestamps, and this meta data is written and saved
> when each backup is taken. This saves future lookups at all tarballs
> when doing cleanup of past backups.
>
> I am not sure about the more popular barman and pgBackrest since I
> know them less, but I would imagine they handle retention policies
> similarly.