On 09/17/2015 11:15 PM, Guillaume Lelarge wrote:
Le 18 sept. 2015 5:23 AM, "Adrian Klaver" <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
