1

I'm implementing incremental backups using filesystem-level snapshots. pg_start_backup() and pg_stop_backup() can be used to this end. Via these pair of functions or via the backup_label file, I can identify the "START WAL LOCATION" and "STOP WAL LOCATION" and associated files. The problem is, when these files are not the same and not strictly consecutive (ie, there are intervening files), how do I deterministically determine them? While it might be the case that the files are monotonically increasing, how can I be sure that is the case, and how can I be sure I've located all the intervening files? Can I be guaranteed the files will be written or changed in chronological order? Or in lexicographical order? (From what I've seen, neither is the case.)

After 3x reading the documentation, I can't get any clarity or resolution on this: it tells us that the backup process must include these files, but offers no indication on how to identify them! If I've overlooked a page in the documentation, I'd appreciate being it pointed out.

BTW: Please don't tell me to use pg_basebackup unless you want a kick in the face.

BTW2: I'm trying to make a complete backup set without doing a full backup. Barman stores the archive logs somewhere else, which is what I want to avoid. Even if I implemented barman (which I don't want to), I'd still need to know which files I need for the backup during its operation.

Improve this question
6
  • I assume you had some nasty experience with pg_basebackup ;)
    – ypercubeᵀᴹ
    Commented Nov 10, 2015 at 12:00
  • Imagine you have a 2TB database and you want to take incrementals -- or even periodic backups. Now imagine you have 5 such databases.
    – Otheus
    Commented Nov 10, 2015 at 12:10
  • Have you considered BARMAN?
    – Marco
    Commented Nov 10, 2015 at 12:24
  • @Otheus I've imagined it - oh, we have them around ;) AFAIK the WAL files are named gaplessly, at least in the same timeline.
    – András Váczi
    Commented Nov 10, 2015 at 12:29
  • gapless helps, but how cna i generate the names in between?
    – Otheus
    Commented Nov 10, 2015 at 12:40

2 Answers 2

Reset to default
2

The file names are 24 hex digits long, broken into 3 fields of 8 digits each. The first is the timeline, which you probably don't need to worry about here. The other two are called the "segment" and the "file". They are really a single counter, the "file" rolls over by incrementing the "segment" by one and going back to zero.

Oddly, only the last two digits of the "file" chunk are ever non-zero. (This is apparently so that the offset into a logical 'segment' fits in a 32 bit uint, which was important at some point in the distant past)

In 9.1 and 9.2, the "file" skips FF. So from FE, it rolls back over to 00 while incrementing the segment number. In 9.3, it switches so that FF is used. So the sequence for your version would go:

0000000100000000000000FD
0000000100000000000000FE
000000010000000100000000
000000010000000100000001
...
00000001000000BE000000F9
00000001000000BE000000FA
00000001000000BE000000FB
00000001000000BE000000FC
00000001000000BE000000FD
00000001000000BE000000FE
00000001000000BF00000000
00000001000000BF00000001
00000001000000BF00000002
00000001000000BF00000003
00000001000000BF00000004

Getting the files from pg_xlog is tricky, because those files get recycled (i.e. renamed to have the name of some future file-name, then overwritten once it gets around to needing that file). This means that the timestamps on the files are all screwy. A recycled log file will have the name of some future file, but the contents and timestamp of some past file.

If a file was recycled before you had a chance to copy it, then your backup has failed and you need to try again with a higher wal_keep_segments.

And of course if you copy the "current" file before it was finalized, it will be missing the changes that occurred after the point you copied it.

It is much easier to get them from the wal archive, because that doesn't have the recycling problem and because they don't show up at all until they are finalized.

Improve this answer
7
  • "It is much easier to get them from the wal archive, because that doesn't have the recycling problem and because they don't show up at all until they are finalized." At least in 9.1.8 there seems to be a race-condition where the xlog is archived but before or during, the backup_label is removed, so the archive command no longer knows it needs to handle this file specially. There's another problem here: according to the docs, if the archive log file exists, the archive_command should not overwrite but return an error. So if filenames are recycled... what now?
    – Otheus
    Commented Nov 12, 2015 at 18:23
  • If archive_command does not overwrite and instead returns an error, then recycling will not happen. It only recycles files when it knows it is done with them, and in archiving mode it is not done with them until they are successfully archived (unfortunately, it doesn't know when you are done with them). If you don't fix the problem, then pg_xlog will grow until you run out of disk space and then your database will come to a halt.
    – jjanes
    Commented Nov 12, 2015 at 20:11
  • Shortly after you call pg_stop_backup(), there will appear in your WAL archive a file named like 00000001000000C4000000C9.00000028.backup. This is a permanent copy of the same data that was in the transient backup_label file.
    – jjanes
    Commented Nov 12, 2015 at 20:23
  • ... Which might be useful if it were named from the LAST wal, or the label, but it's named after the first one, whose name is no longer (necessarily) known to the stateless archive command because backup_label has been removed. So, back to the original question. What's a reliable way of determining which WALs need to be archived?
    – Otheus
    Commented Nov 13, 2015 at 10:30
  • What does the archive command have to do with it?
    – jjanes
    Commented Nov 13, 2015 at 16:40
0

My current best answer is to cheat: use the archive_command script ; when it notices there is a backup_label, either make an additional copy to the backup-directory, or record the name for the backup script to perform. According to the docs, this will work because pg_stop_backup function will not return until the last WAL has finished archiving via that command. What I don't like about this answer is that it "ties" the WAL-archive process to the backup process.

UPDATE: Apparently, postgresql removes the backup_label before sending the last WAL to the archive_command. sigh So the solution here requires a contorted effort and synchronization between the archive-command and backup process. blech

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.