Re: pg_basebackup problem...

Thanks, Kevin,

I'm still considering a better way to sanely initialize a DB, but I
acknowledge that a WAL replay is one way to do this. The problem I've been
trying to solve is this: The old procedure took the tarball pg_basebackup
generated, expanded it, then rsync'd it into the data directory after they
removed everything in that directory including recursively removing the
contents of pg_xlog. Hence, without including the -X f flag, there wouldn't
be any WAL files available for initializing the DB. They old operation
folks caused this problem, and now they want me to fix it, ie., get a
database started from one of these backups where no WAL segments are
present. I keep telling them this is impossible, but they're arguing it's
been done here before and worked. I've been pointing out the impossibility
and stating that there had to be some WAL files remaining in the pg_xlog
directory for the restore to succeed.

On Thu, Aug 6, 2015 at 11:49 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> John Scalia <jayknowsunix(at)gmail(dot)com> wrote:
>
> > I've been examining the source code to pg_basebackup written by
> > Magnus Hagander. It definitely shows that if you do not issue a
> > "-X f" or "-X s" then the pg_xlogs will NOT be included in the
> > backup. This leads me to a couple more questions:
> >
> > 1) Does anyone know why anything in a WAL segment is required for
> > a database to start?
>
> Yes. This is covered in the docs, but in summary: the files could
> be modified during the copy, and without WAL replay from before the
> copy started the database would not necessarily be consistent.
>
> > I ask this as the backup_label generated by pg_basebackup
> > indicates a segment where the latest checkpoint is contained.
>
> It indicates the location of the last checkpoint before the start
> of the backup -- the latest point from which you cab start WAL
> replay to achieve a consistent database.
>
> > Surely, there has to be some better way to permit the DB to
> > sanely initialize.
>
> A write-ahead log is a well-known and widely used technique for
> this. What would you suggest instead?
>
> > 2) If a WAL segment is in fact required for the backed up DB to
> > start, why would pg_basebackup not include those by default? To
> > not do so, doesn't create a backup file, just in this case, a
> > tarball that's worthless.
>
> ... unless you are archiving the WAL to somewhere that it will be
> kept long enough to be usable for such purposes. If you are (and I
> highly recommend that you do so), including WAL in the base backup
> is a waste of both bandwidth and storage space.
>
> Besides reviewing the documentation's discussions of WAL and
> backups, you might want to read this:
>
>
> http://tbeitr.blogspot.com/2015/07/deleting-backuplabel-on-restore-will.html
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>