Re: Broken primary key after backup restore.

Michael Chau <michael(dot)chau(at)gameyourgame(dot)com> wrote:

> For some reason, there were some bad wal log files in pg_xlog. I
> believe that they got generated during the backup last Monday,
> but I don't know why. I speculate that may be the
> wal_keep_segments was not set high enough as I have changed it
> recently.
>
> Luckily, I have archived the wal log files. And by comparing
> between the two directories, I did see those bad wal log files in
> pg_xlog directory only.

The instructions for making a backup explicitly say to exclude or
delete the files in the pg_xlog directory and use those from the
archive. That's because tar (or whatever you are using to copy the
files) may copy a WAL file before some change made during the
backup is written to it. What you were doing is not supported and
likely to appear to work sometimes (possibly even without hidden
corruption), and fail to create a backup that will even start on
other attempts.

You might find this blog post helpful:

http://tbeitr.blogspot.com/2015/07/deleting-backuplabel-on-restore-will.html

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company