Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e4Ttk-0004Ai-GL for pgsql-admin@arkaria.postgresql.org; Tue, 17 Oct 2017 15:35:16 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e4Ttj-0004Mc-T4 for pgsql-admin@arkaria.postgresql.org; Tue, 17 Oct 2017 15:35:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e4Tta-00044g-JF for pgsql-admin@postgresql.org; Tue, 17 Oct 2017 15:35:06 +0000 Received: from omr2.cc.ipv6.vt.edu ([2607:b400:92:8400:0:33:fb76:806e] helo=omr2.cc.vt.edu) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e4TtV-0007mI-KB for pgsql-admin@postgresql.org; Tue, 17 Oct 2017 15:35:05 +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 v9HFYweQ004195 for ; Tue, 17 Oct 2017 11:34:58 -0400 Received: from mail-qk0-f198.google.com (mail-qk0-f198.google.com [209.85.220.198]) by mr1.cc.vt.edu (8.14.7/8.14.7) with ESMTP id v9HFYrq6023961 for ; Tue, 17 Oct 2017 11:34:58 -0400 Received: by mail-qk0-f198.google.com with SMTP id h9so2559409qke.8 for ; Tue, 17 Oct 2017 08:34:58 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:to:from:subject:message-id:date:user-agent :mime-version:content-language; bh=iLLzXamhTg7teKkhDkQDvc7/wS5gYWOPPPBBRSSv8yQ=; b=uXuzpxT2MT775GQsYTKOdV78mM4BQSO4SW4pkp0MgrUVHKiYOqtPFkE3cv0Vj+IQbc Ez1gGyf6ZVj6kxQl7k5rDoWtbK+CfnwJ6EVI4h66jyu3+m0Fv3PRtu4Q8zpJJ8KRXJF+ EtPhVMYToWGNPzqVidMfqVfwb8fQ8S1ujtqPZ3oXn9+Ir618IhpyiKoRAoxhEdTZ0Jrs vASZ3fWWmBipvJMqkmKGDvNhgzk3bzzyVL3DYfAbJNy9vWyJQFUtI9LpmgMpdNhA7Aed A1vquNx3Wn3Gj/cKOpajqmdnP6F4mJq1HYKN2CXKFIf0M39T0O2yT68f1lZTchetwwsc OInA== X-Gm-Message-State: AMCzsaVD8UzMrf5YOrMAH2H0bihWH3/cv83ZCj1M7mXbpzBKNKqw4H8A UzuTSEny2mWTk5Mz7TTm5/4sHqL3xkdaxt6T1hDodRMbLOuAIqCXb3ajk6LGH3foGYoOpKu4xu5 Az0JGdiRRtYHDb3klsGBnl2J21NXbCaVwBLPzgQroYyCDPUIJHQStHaML2P75eYX/4IBguLT5WI Bx X-Received: by 10.55.214.143 with SMTP id p15mr19260557qkl.298.1508254492818; Tue, 17 Oct 2017 08:34:52 -0700 (PDT) X-Google-Smtp-Source: AOwi7QBX/8isrRRUF2ThfzdTRHIKTv7P1Hj1o9AlnpsoLNN27FIfjPWm19n/blPFkSdg1rZSbV8iTw== X-Received: by 10.55.214.143 with SMTP id p15mr19260535qkl.298.1508254492485; Tue, 17 Oct 2017 08:34:52 -0700 (PDT) Received: from stell.cns.vt.edu ([2001:468:c80:2102:50ce:2266:6dbd:e7c5]) by smtp.gmail.com with ESMTPSA id y9sm6374526qti.76.2017.10.17.08.34.51 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 17 Oct 2017 08:34:51 -0700 (PDT) To: "pgsql-admin@postgresql.org" From: Ray Stell Subject: standby upgrade questions Message-ID: <700537e7-9039-0bbd-61fb-b28af635121e@vt.edu> Date: Tue, 17 Oct 2017 11:34:51 -0400 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:52.0) Gecko/20100101 Thunderbird/52.4.0 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------1637E46798FAEF544F58F123" Content-Language: en-US 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. --------------1637E46798FAEF544F58F123 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit From: https://www.postgresql.org/docs/9.6/static/pgupgrade.html Q1 - Is there a procedure to force this "catch up?"   I suppose preventing db activity with a pg_hba hack and running "select pg_switch_xlog()."   Maybe there's a better way? 1. Prepare for standby server upgrades If you are upgrading standby servers (as outlined in sectionstep 10 ), verify that the old standby servers are caught up by runningpg_controldataagainst the old primary and standby clusters. Verify that the"Latest checkpoint location"values match in all clusters. (There will be a mismatch if old standby servers were shut down before the old primary.) Also, if upgrading standby servers, changewal_leveltoreplicain thepostgresql.conffile on the new master cluster. Q2 - I was wondering what the end result is, in general, of this rsync of old and new? If the old db files and upgraded, new db files are meshed what do you get on the other side of the rsync?  Again, I mean in general what is the goal? f. Runrsync From a directory that is above the old and new database cluster directories, run this for each slave: rsync --archive --delete --hard-links --size-only old_pgdata new_pgdata remote_dir whereold_pgdataandnew_pgdataare relative to the current directory, andremote_dirisabovethe old and new cluster directories on the standby server. The old and new relative cluster paths must match on the master and standby server. --------------1637E46798FAEF544F58F123 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

From:   https://www.postgresql.org/docs/9.6/static/pgupgrade.html

Q1 - Is there a procedure to force this "catch up?"   I suppose preventing db activity with a pg_hba hack and running "select pg_switch_xlog()."   Maybe there's a better way?

  1. Prepare for standby server upgrades

    If you are upgrading standby servers (as outlined in section step 10), verify that the old standby servers are caught up by running pg_controldata against the old primary and standby clusters. Verify that the "Latest checkpoint location" values match in all clusters. (There will be a mismatch if old standby servers were shut down before the old primary.)

    Also, if upgrading standby servers, change wal_level to replica in the postgresql.conf file on the new master cluster.


Q2 - I was wondering what the end result is, in general, of this rsync of old and new?  If the old db files and upgraded, new db files are meshed what do you get on the other side of the rsync?  Again, I mean in general what is the goal?

f. Run rsync

From a directory that is above the old and new database cluster directories, run this for each slave:

rsync --archive --delete --hard-links --size-only old_pgdata new_pgdata remote_dir

where old_pgdata and new_pgdata are relative to the current directory, and remote_dir is above the old and new cluster directories on the standby server. The old and new relative cluster paths must match on the master and standby server.

--------------1637E46798FAEF544F58F123--