Re: Broken primary key after backup restore.

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.

Anything in the Postgres logs when you started up the test server based
off the DB2 file system backup?

Anything in the original DB2 logs around the time you where taking the
backup?

>
> Thanks
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com