Re: Broken primary key after backup restore.

Hi,

Just want to let everybody knows

So, I found out this morning what went wrong.

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.

Yes, we were looking at Barman before. But at that time Barman didn't
support our version of PostgreSQL. I think that I will pursue this.

Thanks again.

On Fri, Sep 18, 2015 at 2:40 PM, David Steele <david(at)pgmasters(dot)net> wrote:

> On 9/18/15 3:44 PM, Michael Chau wrote:
>
>> Hi Jeff,
>>
>> Only if you are very lucky. If your tar command tars up the pg_xlog
>>> directory as the last thing it does, then you are probably going to be OK.
>>> Otherwise, it is a crap shoot.
>>>
>>
>> May be that's it. I have another similar set up, but the pg_xlog is a
>> soft link to another directory, and I use 'tar -chvzf'. It tar up the
>> pg_xlog at the very last. And the restore is fine.
>>
>
> This is still not always safe. It depends on your wal_keep_segments
> settings and some luck. WAL segments can be recycled during the backup.
>
> For this one, DB1 and DB2, the pg_xlog is the directory itself, and I
>> use 'tar -cvzf'. And it tar up pg_xlog at the beginning. I always have
>> doubt about it. But I though pg_stop_backup() and pg_start_backup() like
>> freezing would prevent the inconsistency.
>>
>
> This is definitely not a good idea.
>
> Indeed, I will look inot pgbasebackup.
>>
>
> pg_basebackup is good for creating replicas but for real backup you might
> want to consider purpose-built backup software like pgBackRest or barman.
>
> --
> -David
> david(at)pgmasters(dot)net
>

--
*Michael Chau*
*Database Administrator*
*GAME GOLF*
77 Geary St, 5th floor
San Francisco, CA 94108
c) *510-366-3800*
e) *michael(dot)chau(at)gameyourgame(dot)com <http://gameyourgame.com>*
f) www.facebook.com/gamegolf <http://www.facebook.com/gamegolf.gyg>
t) @GAMEGOLF
w) www.gamegolf.c <http://www.gameyourgame.com/>*om*