Re: pg_basebackup problem... - Mailing list pgsql-admin
| From | John Scalia |
|---|---|
| Subject | Re: pg_basebackup problem... |
| Date | |
| Msg-id | CABzCKRDPXGaafHPcaaF1S=KWV64icy_v84mXabiF8ZfLTOF91w@mail.gmail.com Whole thread Raw |
| In response to | Re: pg_basebackup problem... (Kevin Grittner <kgrittn@ymail.com>) |
| Responses |
Re: pg_basebackup problem...
|
| List | pgsql-admin |
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@ymail.com> wrote:
John Scalia <jayknowsunix@gmail.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
pgsql-admin by date: