Re: can we avoid pg_basebackup on planned switches?

On Aug 7, 2012, at 9:32 AM, Fujii Masao wrote:

> On Mon, Aug 6, 2012 at 3:29 AM, Ben Chobot <bench(at)silentmedia(dot)com> wrote:
>>
>> Oh, I would have though that doing a clean shutdown of the old master (step 1) would have made sure that all the unstreamed wal records would be flushed to any connected slaves as part of the master shutting down. In retrospect, I don't remember reading that anywhere, so I must have made that up because I wanted it to be that way. Is it wishful thinking?
>
> When clean shutdown is requested, the master sends all WAL records to
> the standby,
> but it doesn't wait for the standby to receive them. So there is no
> guarantee that all WAL
> records have been flushed to the standby. Walreceiver process in the
> standby might
> detect the termination of replication connection and exit before
> receiving all WAL records.
> Unfortunately I've encountered that case some times.

Oh, I see. Well, that's unfortunate. Thanks for the help though! It shouldn't be too hard to script up what you suggest.
>From pgsql-general-owner(at)postgresql(dot)org Tue Aug 7 16:15:26 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
by mail.postgresql.org (Postfix) with ESMTP id A39587895AC
for <pgsql-general(at)postgresql(dot)org>; Tue, 7 Aug 2012 16:15:24 -0300 (ADT)
Received: from mail-gh0-f174.google.com ([209.85.160.174])
by makus.postgresql.org with esmtp (Exim 4.72)
(envelope-from <gray(dot)ru(at)gmail(dot)com>)
id 1SypFT-00041J-Hy
for pgsql-general(at)postgresql(dot)org; Tue, 07 Aug 2012 19:15:24 +0000
Received: by ghrr11 with SMTP id r11so4076090ghr.19
for <pgsql-general(at)postgresql(dot)org>; Tue, 07 Aug 2012 12:15:10 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=gmail.com; s 120113;
h=mime-version:sender:in-reply-to:references:from:date
:x-google-sender-auth:message-id:subject:to:cc:content-type;
bh=/1ox/TFW0HjUMpwLsYcPsyWW3ubvo3uaIg76COeRSl4=;
b=iMfHgFWC6LC97Qw1XpXW7dpea4iU1EKfNDlVKf5eWKQ52S0e/RJNjOR52csCHvciA3
ETXWVQ9IuQPN8AUGj4WgdEQlJ6jIf3Swmhxf18f+mshrEimVQHCxpQRptxbG1rQ2btyJ
wtxy8+Xns+0cR7T9yo0kya3JDXol+MVvnLRFlsD57Eer7OfKfvgwBFN5KFVfOFmmbQPG
j0l7mKeQ2QvqkXbt1T07gkoqiEr9Fg/0DjfCyoeMYuSnm2AF7glpvEXKIuv0x0uuGB4f
WUNh0DUsFfNYyMGo1T+VX4w82fXNDaVqfChjQ4d3ZoYAG7MVv28UELONTd8w4sEwmFuc
5lQw=
Received: by 10.60.22.165 with SMTP id e5mr25611858oef.60.1344366910727; Tue,
07 Aug 2012 12:15:10 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.182.62.196 with HTTP; Tue, 7 Aug 2012 12:14:49 -0700 (PDT)
In-Reply-To: <CAHGQGwEjM34L8=wq1JipCP5CRyV5SUqx8jkrCxnDG0B5qOtEqg(at)mail(dot)gmail(dot)com>
References: <08F5D866-B066-410C-86B0-C78A835C3A50(at)silentmedia(dot)com> <CAHGQGwEjM34L8=wq1JipCP5CRyV5SUqx8jkrCxnDG0B5qOtEqg(at)mail(dot)gmail(dot)com>
From: Sergey Konoplev <sergey(dot)konoplev(at)postgresql-consulting(dot)com>
Date: Tue, 7 Aug 2012 23:14:49 +0400
X-Google-Sender-Auth: AE-5ItDaPo_TqAvgqKxeODiZ6EE
Message-ID: <CAL_0b1viiz-du9yn2Ck=m68U9Cscm=Ch6XX=5jH6T3SwuLN_Dw(at)mail(dot)gmail(dot)com>
Subject: Re: can we avoid pg_basebackup on planned switches?
To: Fujii Masao <masao(dot)fujii(at)gmail(dot)com>
Cc: Ben Chobot <bench(at)silentmedia(dot)com>,
Postgresql General <pgsql-general(at)postgresql(dot)org>
Content-Type: text/plain; charset=ISO-8859-1
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201208/144
X-Sequence-Number: 189321

On Sun, Aug 5, 2012 at 10:12 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> Have we just avoided running pg_basebackup, or have we just given ourselves
>> data corruption?
>
> If you change your operations in the above-mentioned way, I think you can
> avoid pg_basebackup on the planned switch. I've not tested your operations.
> So please test them carefully before applying them to your system.

It is really hopeful.

So are there any thoughts of how to make sure that after performing
the changed process there are no data corruption on the new replica
(ex-master)?

ps. BTW do not we need to CHECKPOINT the old replica after copying all
the WAL files from the stopped master and before promoting it (the old
replica) to a new master?

>
>> Because we're using wal archiving, can we simplify and
>> leave out step 3?
>
> Yes.
>
> Regards,
>
> --
> Fujii Masao
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204