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@gmail.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.