Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1druvg-0006e4-Pi for pgsql-hackers@arkaria.postgresql.org; Tue, 12 Sep 2017 23:49:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1druvg-0002C3-DK for pgsql-hackers@arkaria.postgresql.org; Tue, 12 Sep 2017 23:49:20 +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 1druve-0002AZ-TV for pgsql-hackers@postgresql.org; Tue, 12 Sep 2017 23:49:18 +0000 Received: from post.visena.com ([46.226.10.50]) by magus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1druvW-0004fz-OP for pgsql-hackers@postgresql.org; Tue, 12 Sep 2017 23:49:18 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=visena.com; s=20141101.wh; h=Content-Type:MIME-Version:Subject:In-Reply-To:Message-ID:Cc:To:From:Date; bh=3WYppvSyetQrA4/6+N0PzD9ZxPu/6B/6I5T/kSjzvFw=; b=nLO8pgHgB1+DCYBIO6UsS5BBnu3Qce04mC+WLLvIyBDpkEu1wXzMRJ9v9OfBDKdEMVTDEonc1ycByGKhwbKA9jEr4uovvL1QqJ87DV1EGf6LG5/7dXcThT05axgz8+MQql7/EtnApkp4KDDFFJu6gi1j+xKcDcXnA9zvs5DzwT4=; Received: from [10.0.1.10] (helo=tc7-visena.wh.internal.visena.com) by post.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1druvQ-0001Ty-Rk; Wed, 13 Sep 2017 01:49:06 +0200 Received: from localhost ([127.0.0.1] helo=tc7-visena.wh.internal.visena.com) by tc7-visena.wh.internal.visena.com with esmtp (Exim 4.86_2) (envelope-from ) id 1druvO-0002hp-Hf; Wed, 13 Sep 2017 01:49:02 +0200 Date: Wed, 13 Sep 2017 01:49:02 +0200 (CEST) From: Andreas Joseph Krogh To: Stephen Frost Cc: Bruce Momjian , Michael Paquier , Robert Haas , "pgsql-hackers@postgresql.org" Message-ID: In-Reply-To: <20170912233840.GS4628@tamriel.snowman.net> Subject: Re: Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers) MIME-Version: 1.0 X-Mailer: Visena Mail 2.1.0-SNAPSHOT X-Spam-Score: -1.0 X-Spam-Report: SpamAssasin (score=-1.0, required 5.0 ALL_TRUSTED=-1,HTML_MESSAGE=0.001) Content-Type: multipart/related; boundary="----=_Part_476_1662012946.1505260142392" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org ------=_Part_475_2015109968.1505260142392 Content-Type: multipart/related; boundary="----=_Part_476_1662012946.1505260142392" ------=_Part_476_1662012946.1505260142392 Content-Type: multipart/alternative; boundary="----=_Part_477_1777993396.1505260142405" ------=_Part_477_1777993396.1505260142405 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 onsdag 13. september 2017 kl. 01:38:40, skrev Stephen Frost < sfrost@snowman.net >: Bruce, all, [snip] Further, really, I think we should provide a utility to do all of the above instead of using rsync- and that utility should do some additional things, such as: - Check that the control file on the primary and replica show that they =C2=A0 reached the same point prior to the pg_upgrade.=C2=A0 If they didn'= t, then =C2=A0 things could go badly as there's unplayed WAL that the primary got =C2=A0 through and the replica didn't. - Not copy over unlogged data, or any other information that shouldn't =C2=A0 be copied across. - Allow the directory structures to be more different between the =C2=A0 primary and the replica than rsync allows (wouldn't have to have a =C2=A0 common subdirectory on the replica). - Perhaps other validation checks or similar. Unfortunately, this is a bit annoying as it necessairly involves running things on both the primary and the replica from the same tool, without access to PG, meaning we'd have to work through something else (such as SSH, like rsync does, but then what would we do for Windows...?). > > 3. What if the directory-layout isn't the same on primary and standby,= ie. > > tablespaces are located differently? > > The way we reconfigured the location of tablespaces in PG 9.0 is that > each major version of Postgres places its tablespace in a subdirectory > of the tablespace directory, so there is tbldir/9.5 and tbldir/9.6.=C2= =A0 If > your tbldir is different on the primary and standby, rsync will=C2=A0 st= ill > work.=C2=A0 Everything _under_ the standby dir must be laid out the same= , but > the directories above it can be different. That's correct, the directory to use for the tablespace actually *is* the tablespace directory (unlike the base directories, it doesn't need to be a directory above the tablespace directory, the documentation could probably be clearer on this point). As for all of the people raising concerns about if this process is correct or valid- I contend that the method used above, if done properly, isn't materially different from what pg_upgrade itself does. If we can't consider this safe then I'm not sure how we consider pg_upgrade safe.=C2=A0 (yes, I know there are some who don't, and that's a fair position to take also, but I consider the process above, when implemented correctly, is essentially the same). All that said, I honestly hadn't expected this method to end up in the documentation.=C2=A0 Not because I don't trust it or because I wanted to hoard the process, but because it takes a great deal of care and there's really additional validation that should be done (as discussed above) and those are things that I feel reasonable confident I'd remember to do when using such a procedure but which I wouldn't expect someone new to PG to realize they should do. Thanks! Stephen =C2=A0 =C2=A0 Thanks for th explaination. =C2=A0 I have to ask; Why not run pg_upgrade on standby, after verifying that it's= in=20 sync with primary and promoting it to primary if necessary and then making = it=20 standby again after pg_upgrade is finished? =C2=A0 -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com www.visena.com =C2=A0 ------=_Part_477_1777993396.1505260142405 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 onsdag 13. september 2017 kl. 01:38:40, skrev Stephen Frost &l= t;sfrost@snowman.net>:
Bru= ce, all,
[snip]

Further, really, I think we should provide a utility to do all of the
above instead of using rsync- and that utility should do some additional things, such as:

- Check that the control file on the primary and replica show that they
=C2=A0 reached the same point prior to the pg_upgrade.=C2=A0 If they didn't= , then
=C2=A0 things could go badly as there's unplayed WAL that the primary got =C2=A0 through and the replica didn't.

- Not copy over unlogged data, or any other information that shouldn't
=C2=A0 be copied across.

- Allow the directory structures to be more different between the
=C2=A0 primary and the replica than rsync allows (wouldn't have to have a =C2=A0 common subdirectory on the replica).

- Perhaps other validation checks or similar.

Unfortunately, this is a bit annoying as it necessairly involves running things on both the primary and the replica from the same tool, without
access to PG, meaning we'd have to work through something else (such as
SSH, like rsync does, but then what would we do for Windows...?).

> > 3. What if the directory-layout isn't the same on primary and sta= ndby, ie.
> > tablespaces are located differently?
>
> The way we reconfigured the location of tablespaces in PG 9.0 is that<= br> > each major version of Postgres places its tablespace in a subdirectory=
> of the tablespace directory, so there is tbldir/9.5 and tbldir/9.6.=C2= =A0 If
> your tbldir is different on the primary and standby, rsync will=C2=A0 = still
> work.=C2=A0 Everything _under_ the standby dir must be laid out the sa= me, but
> the directories above it can be different.

That's correct, the directory to use for the tablespace actually *is*
the tablespace directory (unlike the base directories, it doesn't need
to be a directory above the tablespace directory, the documentation
could probably be clearer on this point).

As for all of the people raising concerns about if this process is
correct or valid- I contend that the method used above, if done
properly, isn't materially different from what pg_upgrade itself does.
If we can't consider this safe then I'm not sure how we consider
pg_upgrade safe.=C2=A0 (yes, I know there are some who don't, and that's a<= br> fair position to take also, but I consider the process above, when
implemented correctly, is essentially the same).

All that said, I honestly hadn't expected this method to end up in the
documentation.=C2=A0 Not because I don't trust it or because I wanted to hoard the process, but because it takes a great deal of care and there's really additional validation that should be done (as discussed above)
and those are things that I feel reasonable confident I'd remember to do when using such a procedure but which I wouldn't expect someone new to
PG to realize they should do.

Thanks!

Stephen
=C2=A0
=C2=A0
Thanks for th explaination.
=C2=A0
I have to ask; Why not run pg_upgrade on standby, after verifying that= it's in sync with primary and promoting it to primary if necessary and the= n making it standby again after pg_upgrade is finished?
=C2=A0
--
Andrea= s Joseph Krogh
CTO / Partner<= /span> - Visena AS
Mobile: +47 90= 9 56 963
=3D""
=C2=A0
------=_Part_477_1777993396.1505260142405-- ------=_Part_476_1662012946.1505260142392 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_476_1662012946.1505260142392-- ------=_Part_475_2015109968.1505260142392--