2

I am using pgBarman for PITR recovery. Now it's a bit of a crisis, because the Barman backup and the WAL archive seems like it cannot be recovered. We're running PostgreSQL 9.3.6 and latest stable Barman.

I ran the following command:

$ barman recover --target-time "2015-09-03 10:00:00.000" ke 20150501T200001 /tmp/ke-recovery

Leaving us with /tmp/ke-recovery, a folder we can start once we change the path names in postgresql.conf.

It then starts with the recovery of the WAL files from barman_xlog/ but then fails in about half an hour due to one of the WAL files are missing.

We assume this is because of an rsync "gone bad" from the master to the Barman box, and it was not noticed. It means we have almost all WAL files, but at least one is missing.

How can we "force" PostgreSQL to continue with the recovery (even at the potential loss of some data)? Perhaps by using pg_resetxlog ?

Improve this question
3
  • Perhaps. If you do this on a cluster that is not your production, just try it.
    – dezso
    Sep 4, 2015 at 13:23
  • You are not the first one :-( Do not think that it can be done without a very good knowledge of the WAL file structure to get something out of the ones after the missing one (how many are there?). Best to re-create the database with the help of Barman on a separate server to see what you can get back from what you have.
    – Marco
    Sep 4, 2015 at 13:47
  • 3
    Please show the archive_command you were using and the script, if any, that it called. This should not happen. If copying the WAL segment fails, PostgreSQL will retry until it succeeds. The only way I've seen this happen is with buggy archive scripts, or with sysadmin action like "cleaning" up WAL archives to free space. Unfortunately, you can't really just force PostgreSQL to continue recovery after a missing WAL segment, not without major work. Consider postgresql.org/support/professional_support . (Note that I work for one of the listed companies)
    – Craig Ringer
    Sep 7, 2015 at 2:43

1 Answer 1

Reset to default
1

I've been kicking PostgreSQL backup around the last couple of weeks, because of a new job, and I find the implementation of PostgreSQL's backup quite hard to grasp.

What I have learned however, is that the a missing WAL file is the worst situation you can have. A missing WAL file will not allow you to continue your restore past the missing file, because the PostgreSQL database relies on the WAL file to be ACID.

You could try to retrieve some of the information from the non-restored WAL files, but you wouldn't have any guarantee, that the resulting database/instance is consistent.

Improve this answer
1
  • Correct - and what we've learned from this is to not trust rsync blindly. It can go wrong, as we've seen several other times. It's not barman's fault, but relying completely on rsync transferring WAL files between links that are unreliable will fail at some point, given months and hundreds of gigabytes transferred.
    – T.K.
    Mar 23, 2016 at 14:38

Your Answer

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

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