missing wal file entries in xlog.db

[Holger Amann]

Hi there,

I wrote a script, which checks all wal files on disk to have and entry in xlog.db and vice versa. Now I discovered that there are 3 wal files on disk which aren't referenced in xlog.db.


$ barman -v
1.2.0
$ find . -name "0000000100000B710000007D"
./wals/0000000100000B71/0000000100000B710000007D
$ find . -name "0000000100000B710000007E"
./wals/0000000100000B71/0000000100000B710000007E
$ find . -name "0000000100000B710000007F"
./wals/0000000100000B71/0000000100000B710000007F

$ barman list-backup sauspiel
sauspiel 20130909T080005 - Mon Sep 9 18:44:53 2013 - Size: 231.0 GiB - WAL Size: 47.0 GiB
sauspiel 20130819T080002 - Mon Aug 19 18:24:27 2013 - Size: 224.0 GiB - WAL Size: 195.0 GiB

$ barman list-files --target wal sauspiel 20130909T080005 | grep 0000000100000B710000007D
$ barman list-files --target wal sauspiel 20130819T080002 | grep 0000000100000B710000007D

According to 'barman show-backup' they should belong to 20130819T080002

$ barman show-backup sauspiel 20130819T080002
Backup 20130819T080002:
Server Name : sauspiel
Status : DONE
PostgreSQL Version: 90204
PGDATA directory : /var/lib/postgresql/9.2/main

Base backup information:
Disk usage : 229.0 GiB
Timeline : 1
Begin WAL : 0000000100000AD100000069
End WAL : 0000000100000AD500000047
WAL number : 987
Begin time : 2013-08-19 08:00:02.198189
End time : 2013-08-19 18:24:27.687345
Begin Offset : 32
End Offset : 11262824
Begin XLOG : AD1/69000020
End XLOG : AD5/47ABDB68

WAL information:
No of files : 40853
Disk usage : 195.0 GiB
Last available : 0000000100000B750000007F

Catalog information:
Retention Policy: not enforced
Previous Backup : - (this is the oldest base backup)
Next Backup : 20130909T080005

$ barman show-backup sauspiel 20130909T080005
Backup 20130909T080005:
Server Name : sauspiel
Status : DONE
PostgreSQL Version: 90204
PGDATA directory : /var/lib/postgresql/9.2/main

Base backup information:
Disk usage : 236.0 GiB
Timeline : 1
Begin WAL : 0000000100000B7100000082
End WAL : 0000000100000B750000007F
WAL number : 1018
Begin time : 2013-09-09 08:00:05.295499
End time : 2013-09-09 18:44:53.073439
Begin Offset : 32
End Offset : 15115784
Begin XLOG : B71/82000020
End XLOG : B75/7FE6A608

WAL information:
No of files : 9968
Disk usage : 48.0 GiB
Last available : 0000000100000B9C00000096

Catalog information:
Retention Policy: not enforced
Previous Backup : 20130819T080002
Previous Backup : 20130819T080002
Next Backup : - (this is the latest base backup)

How could that happen, and, what's more important me, how can I correct it? Just by adding the according lines to xlog.db again?

Thanks in advance
Holger

[Marco Nenciarini]

Hi Holger,

Il 14/09/13 13:31, Holger Amann ha scritto:

I think you have been hit by the bug described at
https://sourceforge.net/p/pgbarman/tickets/27/ . In releases before
1.2.3 there was a problem in our locking mechanism that can, under some
circumstances, lead to a corruption of xlog.db

I've been able to reproduce an error matching the description using
barman version 1.2.0 and issuing a long lasting "barman delete" command
while the lock was held by a "barman cron" run.

The bug has been corrected in barman 1.2.3 release and should not happen
again.

To fix the corrupted xlog.db file, given that it is not used during
recovery, the first option is to ignore the missing three lines until
the base backup 20130819T080002 will be old enough to be deleted. After
its deletion you can safely remove the remaining three WAL files.

Alternately you can temporarily disable the "sauspiel" server and edit
the xlog.db file. Only remember that editing that file is not safe if
barman has any chance to modify it. The fastest way to avoid it is
commenting out the server from the active configuration file.

Thanks for the report,
Marco

--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.ne...@2ndQuadrant.it | www.2ndQuadrant.it