From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org>, Michael Chau <michael(dot)chau(at)gameyourgame(dot)com> |
Subject: | Re: Broken primary key after backup restore. |
Date: | 2015-09-18 13:16:03 |
Message-ID: | 55FC0E93.1060709@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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:
> >>
> >> 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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Kong Man | 2015-09-18 15:19:30 | Re: search_path not reloaded via unix socket connections |
Previous Message | Leif Jensen | 2015-09-18 13:06:14 | Re: Delete trigger |