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

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.
--
Michael