Re: Broken primary key after backup restore.

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.

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.

Indeed, I will look inot pgbasebackup.

Thanks,

On Fri, Sep 18, 2015 at 11:20 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Fri, Sep 18, 2015 at 6:16 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>
>> On 09/17/2015 11:15 PM, Guillaume Lelarge wrote:
>>
>>> Le 18 sept. 2015 5:23 AM, "Adrian Klaver" <adrian(dot)klaver(at)aklaver(dot)com
>>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> a écrit :
>>> >
>>> > On 09/17/2015 05:37 PM, Michael Chau wrote:
>>> >>
>>> >> To restore on test server:
>>> >>
>>> >> 1) Just untar the tar ball, then start up Postgres. Of course the
>>> data
>>> >> directory is empty beforehand.
>>> >>
>>> >> This has been working for almost 2 years without any problem until
>>> last
>>> >> Monday. I remember that I just ran vacuum analyze that table on both
>>> DB1
>>> >> and DB2 that morning. But, I don't think that it harms anything.
>>> >
>>> >
>>> > Well it looks fairly straight forward, to me at least.
>>> >
>>>
>>> Do I miss something obvious? Because this is to me the wrong way to do
>>> the restore. You need to apply WAL files archived between
>>> pg_start_backup and pg_stop_backup to get consistent data files.
>>>
>>
>> Would that not be taken care of by the tar data directory/ untar data
>> directory?
>>
>
> 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.
>
>
>
>> I would think if it was a WAL issue the OP could never get the server to
>> start and get to the point the query failed on a single table and column.
>
>
> With pg_basebackup, that is probably the case, as it either doesn't copy
> xlog at all, or if it does it makes sure it is complete. But with tar, you
> have no such protection.
>
>
>
>> All that being said, I think the OP would be better served by
>> pg_basebackup:
>>
>> http://www.postgresql.org/docs/9.4/static/app-pgbasebackup.html
>
>
>
> Yes, indeed.
>
> Cheers,
>
> Jeff
>
>

--
*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*