Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42558=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1Wy43n-000893-4Z
 for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jun 2014 19:01:15 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42558=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1Wy43l-0003s6-Pn
 for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jun 2014 19:01:13 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <jayknowsunix@gmail.com>)
 id 1Wy43k-0003rz-OV
 for pgsql-admin@postgresql.org; Fri, 20 Jun 2014 19:01:12 +0000
Received: from mail-yh0-x22d.google.com ([2607:f8b0:4002:c01::22d])
 by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <jayknowsunix@gmail.com>)
 id 1Wy43c-0006L9-IZ
 for pgsql-admin@postgresql.org; Fri, 20 Jun 2014 19:01:11 +0000
Received: by mail-yh0-f45.google.com with SMTP id t59so3178006yho.18
 for <pgsql-admin@postgresql.org>; Fri, 20 Jun 2014 12:01:01 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=message-id:date:from:user-agent:mime-version:to:subject:references
 :in-reply-to:content-type:content-transfer-encoding;
 bh=Yy8O9N691ECap9yo0qj+w22MylpuwC7NFSYICqmJKyw=;
 b=s/6hBs5j7hrrHf0jXn+E8M8bfSwWNz6vDuFqnJA/ZsHaxA+njpBgQLA01BUUszD67w
 PPdGgRwZN4eQpUcAfR0DEkmtdDQTN7HrfZ4TFKTYtio+fGJCMI+etjnbMsOArBFiJI4i
 2DaQAGXx1ax2VvhJdo00nyGzRuwmO3FFrA/Ba+soyIlCdVXHuP/vzNS0I+bECY+LCh8x
 kiqwHknocHr1LFjgLAKVT2dmWtQ9oyAQV668ri+KtdDVAYdZha9kSG1VYtUQrDLggXXY
 WR0s7KYiBO7HLDGV363CmObyG/Ff4CimwXzyY2bdQP9C6Y37m5nX9oio+qUroG2X3OKx
 8LXA==
X-Received: by 10.236.4.34 with SMTP id 22mr7641703yhi.41.1403290861230;
 Fri, 20 Jun 2014 12:01:01 -0700 (PDT)
Received: from [192.168.254.2] (h77.89.102.166.static.ip.windstream.net.
 [166.102.89.77])
 by mx.google.com with ESMTPSA id l66sm15732963yhc.54.2014.06.20.12.01.00
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Fri, 20 Jun 2014 12:01:00 -0700 (PDT)
Message-ID: <53A484E8.2060200@gmail.com>
Date: Fri, 20 Jun 2014 15:00:56 -0400
From: John Scalia <jayknowsunix@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: Kevin Grittner <kgrittn@ymail.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Cannot rebuild a standby server
References: <53A47312.9040600@gmail.com>
 <1403287756.14509.YahooMailNeo@web122305.mail.ne1.yahoo.com>
In-Reply-To: <1403287756.14509.YahooMailNeo@web122305.mail.ne1.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Well, I did finally get it working by adding -X s -c fast to the pg_basebackup command.

Kevin, if I didn't copy WALs over, the database still refused to start as it claimed it was looking for a one of the first specific files. Also, I've not seen any references to 
removing certain files like a backup_label file in the standby's data directory causing problems. The other files I removed were the old postgresql.pid file from the primary and a 
file called archiving_active, which I use for controlling whether postgresql writes WAL files or not. Seems a little funny to me that I've done this same procedure for over 4 
months with no problems, and today was the first time it bit me.

On 6/20/2014 2:09 PM, Kevin Grittner wrote:
> 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 empty.
>> 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 check
>> 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.log:
>> <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 pgpool. 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.  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.  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.  It gives pretty specific
> instructions for what to do.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>



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