I'm using PostgreSQL WAL archiving and pg_start_backup as outlined in the Continuous Archiving section of the Postgres manual to backup a large database. Twice a month we do a full backup and every other night we just copy the WALs over to our backup space so if there's ever an issue we just do a restore of the last full backup + the WALs that have been created since then.
I've noticed that the WALs are sequential except where pg_start_backup is called (or technically it may be when calling pg_stop_backup I'm not sure which) at which point it skips a few. Is this normal behaviour? I know when calling pg_stop_backup moves to the next WAL segment but I'd expect that to be the next number incrementally or am I missing something?
For example on the 14th of March we had the following WALs after a 1st of March full backup:
000000010000008500000090
000000010000008500000091
000000010000008500000092
000000010000008500000093
After the backup it then went to:
000000010000008500000096
000000010000008500000096.00000020.backup
After this normal WAL creation resumed:
000000010000008500000097
000000010000008500000098
and so on
So should there be a 94 or 95 in this case?
I'm in the process of doing a test restore but as that involves copying 60GB (compressed) of data between two servers in different continents, I'd prefer to know sooner rather than later if I'm going to have an issue!
Best Answer
At one point in the distant past, a comment in the docs was changed from saying "sequential" to saying "ever-increasing". Source
This wording is still in the 9.1 docs for WAL internals. So I'd say that you shouldn't expect a gapless sequence.