-1

As both the archive_command and restore_command defined with related config options looks to be injected only with %f and %p parameter how actually to identify on top of what base backup the current segment is archived/restored on.

So that having an external storage for the backup artifacts how for the scenarios similar to

initdb switch_wal switch_wal switch_wal pg_basebackup switch_wal switch_wal pg_basebackup switch_wal switch_wal switch_wal

to be able to manage related artifacts (e.g. copy/remove/cleanup) in logically atomic way as

initdb switch_wal switch_wal switch_wal 
pg_basebackup switch_wal switch_wal 
pg_basebackup switch_wal switch_wal switch_wal

Is maybe the prefix in WAL segment names (eg 000000010000000000000005, 00000008000000000000000A) the one that could help with this

Updated

As I understand the WAL segments are kind of incremental diffs that could be applied on top of specific database state. As @Laurenz Albe mentioned in the comment it doesn’t belong to a specific base backup. Still I assume they doesn’t have any sens in terms of any other (non related base backup) either. I.e. you can't apply WAL segment to a state the database was restored from any other non related base backup. Or can you?

So my intention is to organize an external storage for the archives in logical groups chronologically organized around the base backup points. E.g.

/base-backup-point-1 backup-tar wal-segment-1 wal-segment-2 wal-segment-3 /base-backup-point-2 backup-tar wal-segment-4 wal-segment-5 /base-backup-point-3 backup-tar wal-segment-1 wal-segment-2 wal-segment-3 wal-segment-4

so that

  • Make them be able deleted all together. E.g once the base backup is deleted all its derivative WAL segments have no any sence as well
  • Taking into account the WAL segments could be rotated I want to be sure the new segments (e.g. 000000100000001 after the some cycle of backups) won’t overwrite any other old historical segments. (Or does the rotation mean just the storage space being reused not the exact segment names)
Improve this question
2
  • The question doesn't make any sense. A WAL segment doesn't belong to a certain base backup. There can be many WAL switches while a base backup is running. Moreover, your English is so bad that it is hard to understand what you are talking about (I don't blame you, but I don't understand it). Perhaps you can describe what the real problem is that you are trying to solve. Commented May 8 at 18:12
  • Hi @LaurenzAlbe. Yes I can agree It is hard for me to express my vision on the topic as it is blurred it self. I’ve updated the question trying to describe my vision. Or maybe I have some misunderstanding with the WAL segments approach though. Commented May 8 at 19:27

1 Answer 1

Reset to default
0

Every base backup will archive a *.backup file in the WAL archive that contains the earliest WAL segment that is required to recover that backup. The same information can be found in the backup.label file that is part of every base backup. So that part is simple.

However, an archived WAL segment does not belong to a single base backup. Rather, you need all WAL segments after the initial one.

If you want to purge the WAL archive, you can use pg_archivecleanup. As arguments, pass the WAL archive directory and the oldest *.backup file you want to retain. The utility will delete all older files.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Ah great. Thank you very much. It clarifies a little some aspects to me. Still I guess I would have some followup question once I check my assumption.
It looks it's pretty clear now. Thank you. As I mentioned there're some derivative questions still it rather worth a standalone topic

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.