Home > mailing lists

Re: Broken primary key after backup restore. - Mailing list pgsql-general

From	Michael Chau
Subject	Re: Broken primary key after backup restore.
Date	September 18, 2015 23:44:13
Msg-id	CALE++3SWw+3V6qy1KC5NA-2w=JKFt9tYBqTb+Y_Fnf3jgSqoUA@mail.gmail.com Whole thread Raw
In response to	Re: Broken primary key after backup restore. (Jeff Janes <jeff.janes@gmail.com>)
Responses	Re: Broken primary key after backup restore.
List	pgsql-general

Tree view

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.janes@gmail.com> wrote:

On Fri, Sep 18, 2015 at 6:16 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 09/17/2015 11:15 PM, Guillaume Lelarge wrote:
Le 18 sept. 2015 5:23 AM, "Adrian Klaver" <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.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