Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com Mar 26 '13 at 0:32

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks. I've just done a restore and all worked as expected so I believe this to be the correct answer –  Georgio_1999 Mar 26 '13 at 17:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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