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.ja...@gmail.com> wrote: > On Fri, Sep 18, 2015 at 6:16 AM, Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > >> On 09/17/2015 11:15 PM, Guillaume Lelarge wrote: >> >>> Le 18 sept. 2015 5:23 AM, "Adrian Klaver" <adrian.kla...@aklaver.com >>> <mailto:adrian.kla...@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 > > -- *Michael Chau* *Database Administrator* *GAME GOLF* 77 Geary St, 5th floor San Francisco, CA 94108 c) *510-366-3800* e) *michael.c...@gameyourgame.com <http://gameyourgame.com>* f) www.facebook.com/gamegolf <http://www.facebook.com/gamegolf.gyg> t) @GAMEGOLF w) www.gamegolf.c <http://www.gameyourgame.com/>*om*
