Re: pg_basebackup problem... - Mailing list pgsql-admin

From John Scalia
Subject Re: pg_basebackup problem...
Date
Msg-id CABzCKRCg5JNOv-y+-y9_H--9OkcK6+bc9EGh2pkwLV1DZwf3DA@mail.gmail.com
Whole thread Raw
In response to Re: pg_basebackup problem...  (Kevin Grittner <kgrittn@ymail.com>)
List pgsql-admin
Oh, I had forgotten about pgresetxlog, but do recall that is a method of last resort.

On Thu, Aug 6, 2015 at 1:38 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
John Scalia <jayknowsunix@gmail.com> wrote:

> 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.

The only way to get the base backup started without the WAL records
that were generated during the backup is with pg_resetxlog, and
that provides no guarantees that there is no corruption.  See:

http://www.postgresql.org/docs/current/static/app-pgresetxlog.html

... which says, in part:

| pg_resetxlog clears the write-ahead log (WAL) and optionally
| resets some other control information stored in the pg_control
| file. This function is sometimes needed if these files have
| become corrupted. It should be used only as a last resort, when
| the server will not start due to such corruption.
|
| After running this command, it should be possible to start the
| server, but bear in mind that the database might contain
| inconsistent data due to partially-committed transactions. You
| should immediately dump your data, run initdb, and reload. After
| reload, check for inconsistencies and repair as needed.

Absence of WAL files in pg_xlog, or copies made while changes were
being logged to them (rather than taken from a stopped cluster or
through archiving, streaming replication, or pg_basebackup options)
is a form of database corruption.

There are many ways to get the WAL records; for example maybe they
previously restored before they were removed from the primary
system and they had the recovery.conf file configured to retrieve
them from there.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

pgsql-admin by date:

Previous
From: Kevin Grittner
Date:
Subject: Re: pg_basebackup problem...
Next
From: Peter Eisentraut
Date:
Subject: Re: pg_basebackup problem...