Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1clfco-0006k7-JF for pgsql-admin@arkaria.postgresql.org; Wed, 08 Mar 2017 17:43:46 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1clfco-0001do-61 for pgsql-admin@arkaria.postgresql.org; Wed, 08 Mar 2017 17:43:46 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1clfcn-0001da-NO for pgsql-admin@postgresql.org; Wed, 08 Mar 2017 17:43:45 +0000 Received: from omr2.cc.ipv6.vt.edu ([2607:b400:92:8400:0:33:fb76:806e] helo=omr2.cc.vt.edu) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1clfcg-0002ll-Pg for pgsql-admin@postgresql.org; Wed, 08 Mar 2017 17:43:44 +0000 Received: from mr1.cc.vt.edu (mr1.cc.vt.edu [IPv6:2607:b400:92:8300:0:31:1732:8aa4]) by omr2.cc.vt.edu (8.14.4/8.14.4) with ESMTP id v28HhatS022268 for ; Wed, 8 Mar 2017 12:43:36 -0500 Received: from mail-qk0-f200.google.com (mail-qk0-f200.google.com [209.85.220.200]) by mr1.cc.vt.edu (8.14.7/8.14.7) with ESMTP id v28HhUtj009336 for ; Wed, 8 Mar 2017 12:43:36 -0500 Received: by mail-qk0-f200.google.com with SMTP id j127so92488284qke.2 for ; Wed, 08 Mar 2017 09:43:35 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:cc:from:message-id:date :user-agent:mime-version:in-reply-to; bh=ixEagLWNTFCBL5z7EYkBjLMgm1ZwKpiuLtDx8rJ+scE=; b=DVNzmhwjYQ+5nI7JStWpqhbrdpwruNvsbg6U8P+rme3ZQW0GzOuBj+Sq6uKliiRKiG R49HavxvwifZTJ00gxslWb/36WMkN5y/G1kuxd2LnOaTX36kXNEy4JtQHvoMj+Xfnaso 7nilMZ7UhSlCOP9UFyqUGpARpO/6b7rXiIv9DIA0spQtGGzHOogaYcSmlXhafLu4YWBj DzDW6S075hPzxP2nHH5/P0MS97LJG5qc3Zcw1PaCbmHSzjTU7BU6Du7uV53BJoY69LUW bnxvhIxgYIvHrV858ou6fGR39ejpfqWBY+k6JzQX1dP3zatD8ZeiRWUbcKvukN8/JpDb FStA== X-Gm-Message-State: AMke39mponniFmjKQxNBqcHQ4WXAq2N80ebcoVgeb9lVPO6ARxOoqrGmE83UQAgQr5mWFaO9P0SNy6cXY1tEXbmpnKT3w5NnBpct20EYwYAxCi3eyUIo0AX31m57xDu1yOAgVUdxFDD4FvofxFndcdC2KIrVfsMfmjtP0ktyRuF0N9EOQA3g122zfDejTXry56ACa8A= X-Received: by 10.237.59.213 with SMTP id s21mr8799456qte.146.1488995010266; Wed, 08 Mar 2017 09:43:30 -0800 (PST) X-Received: by 10.237.59.213 with SMTP id s21mr8799426qte.146.1488995009957; Wed, 08 Mar 2017 09:43:29 -0800 (PST) Received: from stell.cns.vt.edu ([2001:468:c80:2102:49cb:6af7:97b0:770e]) by smtp.gmail.com with ESMTPSA id w35sm2518944qtc.55.2017.03.08.09.43.29 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 08 Mar 2017 09:43:29 -0800 (PST) Subject: Re: pg_basebackup recovery failure To: Magnus Hagander References: Cc: "pgsql-admin@postgresql.org" From: Ray Stell Message-ID: Date: Wed, 8 Mar 2017 12:43:28 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10; rv:45.0) Gecko/20100101 Thunderbird/45.7.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------13B53BC2AD71CB90382F3A11" X-Spam-Status: No, score=1.0 required=5.0 tests=HTML_MESSAGE, RCVD_IN_DNSWL_NONE,RCVD_IN_MSPIKE_H3,RCVD_IN_MSPIKE_WL,SPF_SOFTFAIL autolearn=disabled version=3.4.0 X-Spam-Checker-Version: SpamAssassin 3.4.0 (2014-02-07) on mr1.cc.vt.edu X-Pg-Spam-Score: -3.7 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------13B53BC2AD71CB90382F3A11 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 3/7/17 3:36 PM, Magnus Hagander wrote: > On Tue, Mar 7, 2017 at 12:31 PM, Ray Stell > wrote: > > On this doc page are some command examples for pg_basebackup: > > https://www.postgresql.org/docs/current/static/app-pgbasebackup.html > > > such as: > > pg_basebackup -h mydbserver -D /usr/local/pgsql/data > > I've never gotten this to work. When I bring up the copy on an > intended standby system I always get the classic: > PANIC: could not locate a valid checkpoint record > > Failing that, I backed up with pg_start_backup/tar/pg_stop_backup as an alternative, but I was always curious what I was doing wrong. > The wiki page on setting up replication: > > https://wiki.postgresql.org/wiki/Streaming_Replication > > > says use the command with this options: > > pg_basebackup -h 192.168.0.10 -D /srv/pgsql/standby -P -U replication --xlog-method=stream > > and I stumbled into option: > > --checkpoint=fast > > The standby was able to recover when I used one of these, I don't remember which, maybe both. > > Should the doc be updated or am I missing something? > > > If you are using pg_basebackup without a log archive, then yes, you > need --xlog-method=stream (or fetch). The checkpoint method only > affects how quickly the checkpoint runs, it will always run. > If you do have a log archive, it should work fine without > --xlog=stream, but you need to put your restore_command into > recovery.conf manually. Too funny not to post. Years ago, maybe version 7 or 8 something, I remember needing to rm the backup_label from the restored/standby dir. I did that without thinking and it seems that was the source of the problem. I just repeated the process with a mv of the backup_label and I get a: PANIC: XX000: could not locate a valid checkpoint record I restored the backup_label file and we're happy: LOG: 00000: started streaming WAL from primary at 0/15000000 on timeline 1 Lesson: I need to get out more often, but I must say, the error msg gave me a pretty good head fake. --------------13B53BC2AD71CB90382F3A11 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 3/7/17 3:36 PM, Magnus Hagander wrote:

On Tue, Mar 7, 2017 at 12:31 PM, Ray Stell <stellr@vt.edu> wrote:

On this doc page are some command examples for pg_basebackup:

        https://www.postgresql.org/docs/current/static/app-pgbasebackup.html

such as: 

    pg_basebackup -h mydbserver -D /usr/local/pgsql/data

I've never gotten this to work.  When I bring up the copy on an intended standby system I always get the classic:

	PANIC:  could not locate a valid checkpoint record

Failing that, I backed up with pg_start_backup/tar/pg_stop_backup as an alternative, but I was always curious what I was doing wrong. 
The wiki page on setting up replication:
 
     https://wiki.postgresql.org/wiki/Streaming_Replication

says use the command with this options:
   
     pg_basebackup -h 192.168.0.10 -D /srv/pgsql/standby -P -U replication --xlog-method=stream

and I stumbled into option:

      --checkpoint=fast

The standby was able to recover when I used one of these, I don't remember which, maybe both. 

Should the doc be updated or am I missing something?
If you are using pg_basebackup without a log archive, then yes, you need --xlog-method=stream (or fetch). The checkpoint method only affects how quickly the checkpoint runs, it will always run.
If you do have a log archive, it should work fine without --xlog=stream, but you need to put your restore_command into recovery.conf manually.
Too funny not to post.   Years ago, maybe version 7 or 8 something, I remember needing to rm the backup_label from the restored/standby dir.  I did that without thinking and it seems that was the source of the problem.   I just repeated the process with a mv of the backup_label and I get a:     PANIC:  XX000: could not locate a valid checkpoint record I restored the backup_label file and we're happy:       LOG:  00000: started streaming WAL from primary at 0/15000000 on timeline 1 Lesson: I need to get out more often, but I must say, the error msg gave me a pretty good head fake. --------------13B53BC2AD71CB90382F3A11--