References: <53A47312.9040600@gmail.com>
Message-ID: <1403287756.14509.YahooMailNeo@web122305.mail.ne1.yahoo.com>
Date: Fri, 20 Jun 2014 11:09:16 -0700
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Cannot rebuild a standby server
To: John Scalia <jayknowsunix@gmail.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <53A47312.9040600@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

John Scalia <jayknowsunix@gmail.com> wrote:

> In the true definition of insanity, I've tried to rebuild a standby
> streaming replication server using the following steps several times:
>
> 1) ensure the postgresql data directory, /var/lib/pgsql/9.3/data, is empt=
y.
> 2) run: pg_basebackup -h <primary server> -D /var/lib/pgsql/9.3/data
> 3) manually copy the WAL's from the primary server's pg_xlog directory
> to the directory specified in the standby's recovery.conf restore_command.

Step 3 is enough to cause database corruption on the replica.

> 4) rm any artifacts from the standby's new data directory like the
> backup_label file.

So is that.

> 5) copy the saved recovery.conf into the standby's data directory and che=
ck
> it is accurate.
> 6) Start the database using "service postgresql-9.3 start"
>
> Every time, however, the following appears in the pg_log/postgresql-Fri.l=
og:
> <timestamp> LOG: entering standby mode
> <timestamp> LOG: restored log file "00000003.history"
> <timestamp> LOG: invalid secondary checkpoint record
> <timestamp> PANIC: could not locate a valid checkpoint record

Yep, that's about the best result you can expect with the above
procedure; it is also occasionally possible to get it to start, but
if it did there would almost certainly be data loss or corruption.

> All this was originally caused by testing the failover mechanism in pgpoo=
l. That
> didn't succeed and I'm trying to get the servers back to their original
> states. I've done this kind
> of thing before, but don't know what's wrong with this effort. What have
> I missed?

You should enable WAL archiving and the restore_command in
recovery.conf should copy WAL files from the archive.=A0 The pg_xlog
directory should be empty when starting recovery unless the primary
is stopped and you only copy pg_xlog files from the stopped server
into the pg_xlog directory of the recovery cluster.=A0 Don't delete
the backup_label file, because it has the information recovery
needs about the point from which it should start WAL replay --
without it, it will have to guess, and is very likely to get that
wrong.

The documentation is your friend.=A0 It gives pretty specific
instructions for what to do.

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


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin