Re: Broken primary key after backup restore.

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