Re: Broken primary key after backup restore.

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:
>  >>
>  >> 1)
>  >>
>  >>     In Production, I have a DB2 which is replicated partially using
> Londiste
>  >>     from DB1.
>  >>
>  >>
>  >> Well I think the above needs more explanation to help understand how the
>  >> DB2 backup got into this state and possibly prevent it in the future.
>  >>
>  >> A: So, the DB1 has several schemas in the database. We use Londiste to
>  >> replicate just one of the schemas to DB2.  The table in question is in
>  >> that schema. Backup is done on both DB1 and DB2.
>  >>
>  >> 2)
>  >> I make file-system backups nightly on both DBs.
>  >>
>  >> How is that done exactly?
>  >>
>  >> A: To backup:
>  >>
>  >> 1) pg_start_backup()
>  >> 2) tar up the files under the data directory
>  >> 3) pg_stop_backup()
>  >>
>  >> 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?

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


>


--
Adrian Klaver
adrian.klaver@aklaver.com