Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1druiJ-0005q3-L4 for pgsql-hackers@arkaria.postgresql.org; Tue, 12 Sep 2017 23:35:31 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1druiI-0006PO-JE for pgsql-hackers@arkaria.postgresql.org; Tue, 12 Sep 2017 23:35:30 +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 1druiG-0006OV-T3 for pgsql-hackers@postgresql.org; Tue, 12 Sep 2017 23:35:29 +0000 Received: from post.visena.com ([46.226.10.50]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1druiD-0006CF-JK for pgsql-hackers@postgresql.org; Tue, 12 Sep 2017 23:35:27 +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=VsMHgkWtGWja5OssUYUNQEVmi7blONAw593HYVsCVfA=; b=dzfni2HlOsReWIsyPHalnmwA9Z85mmxOjn+M/bZ28YjCUFzkpE8EseEQGdIEzVjxa5jTPmqtt12g8f6HRo13fFvLNxUW/97JW9NnMndnprYQC+juT2OT77fcZBiUARw7Zd0IBFOq81A6787lRvjP3aDiGZvjbCH5cPjPWPhTSfM=; 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 1drui7-0000uL-O0; Wed, 13 Sep 2017 01:35:21 +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 1drui5-0002Bc-CP; Wed, 13 Sep 2017 01:35:17 +0200 Date: Wed, 13 Sep 2017 01:35:17 +0200 (CEST) From: Andreas Joseph Krogh To: Bruce Momjian Cc: Michael Paquier , Robert Haas , "pgsql-hackers@postgresql.org" , Stephen Frost Message-ID: In-Reply-To: <20170912230020.GD6766@momjian.us> 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/alternative; boundary="----=_Part_472_1001175699.1505259317228" 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_470_1138616347.1505259317215 Content-Type: multipart/related; boundary="----=_Part_471_955525209.1505259317215" ------=_Part_471_955525209.1505259317215 Content-Type: multipart/alternative; boundary="----=_Part_472_1001175699.1505259317228" ------=_Part_472_1001175699.1505259317228 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 onsdag 13. september 2017 kl. 01:00:20, skrev Bruce Momjian >: On Wed, Sep 13, 2017 at 12:40:32AM +0200, Andreas Joseph Krogh wrote: > P=C3=A5 tirsdag 12. september 2017 kl. 23:52:02, skrev Bruce Momjian < > bruce@momjian.us>: > >=C2=A0 =C2=A0 =C2=A0On Tue, Sep 12, 2017 at 08:59:05PM +0200, Andreas Jos= eph Krogh wrote: >=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 =C2=A0Improvements? >=C2=A0 =C2=A0 =C2=A0> >=C2=A0 =C2=A0 =C2=A0> Thanks, that certainly improves things. >=C2=A0 =C2=A0 =C2=A0> But; I still find the rsync-command in f) confusing= ; >=C2=A0 =C2=A0 =C2=A0> 1. Why=C2=A0--size-only? From rsync manual: "skip f= iles that match in=20 size", >=C2=A0 =C2=A0 =C2=A0is this >=C2=A0 =C2=A0 =C2=A0> safe?? > > >=C2=A0 =C2=A0 =C2=A0> 2. Why is old_pgdata in the rsync-command, why is i= t needed to sync=20 it? > >=C2=A0 =C2=A0 =C2=A0If the file exists under the same name, it doesn't ne= ed to be checked at >=C2=A0 =C2=A0 =C2=A0all --- it is the same.=C2=A0 We don't want to check = the file modification >=C2=A0 =C2=A0 =C2=A0time because it will probably be different because of= replay delay or >=C2=A0 =C2=A0 =C2=A0clock drift.=C2=A0 We could use checksums, but there = is no need since there=20 is >=C2=A0 =C2=A0 =C2=A0no way the file contents could be different. > > =C2=A0 > =C2=A0 > So you're saying that if the file exists (has the same name) on the stan= dby=20 (in > old_pgdata), and has the same size, then you're safe that it contains th= e=20 same > data, hence --size-only? > Does this apply when not using --link mode for pg_upgrade? Well, it is really true in every case.=C2=A0 For link mode, we have to use= an rsync command that lists both the old and new clusters on the command line (since we need rsync to see those hard links to reproduce them). If we don't use --size-only, we are going to checksum check the _old_ data cluster.=C2=A0 The new cluster will be empty so we will copy all of that (= no need for a checksum there since there are no files).=C2=A0 I think you nee= d size-only even without link since that old cluster is going to be listed for rsync. Now, what you could do, if you are _not_ using link mode, is to rsync only the new cluster, but the instructions we give work the same for link and non-link mode and produce the same results in the same time even if we had a non-link-mode example, so it seems we might as well just give one set of instructions. >=C2=A0 =C2=A0 =C2=A0> There are many ways to do/configure things it seems= , resulting in many >=C2=A0 =C2=A0 =C2=A0ifs and >=C2=A0 =C2=A0 =C2=A0> buts which makes section 10 rather confusing. I rea= lly think a=20 complete >=C2=A0 =C2=A0 =C2=A0> example, with absolute paths, would be clarifying. > >=C2=A0 =C2=A0 =C2=A0You mean a full rsync command, e.g.: > >=C2=A0 =C2=A0 =C2=A0=C2=A0 rsync --archive --delete --hard-links --size-o= nly \ >=C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 =C2=A0 /opt/PostgreSQL/9.5 /opt/Postgre= SQL/9.6 standby:/opt/PostgreSQL > >=C2=A0 =C2=A0 =C2=A0Does that help? > > =C2=A0 > =C2=A0 > It seems some non-obvious assumptions (to me at least) are made here. > This example seems only valid when using=C2=A0pg_upgrade --link, correct= ? If so=20 it > would be clearer to the reader if explicitly stated. Well, as I stated above, --hard-links is only going to recreate hard links on the standby that exist on the primary, and if you didn't use pg_upgrade's --link mode, there will be none, so it is harmless if pg_upgrade --link mode was not used. > 1. Why do you have to rsync both=C2=A0/opt/PostgreSQL/9.5 AND=20 /opt/PostgreSQL/9.6, > wouldn't=C2=A0/opt/PostgreSQL/9.6 suffice? Or does this assume "pg_upgra= de=20 --link" > AND "rsync --hard-links" and therefore it somewhat needs to transfer les= s=20 data? As I stated above, rsync has to see _both_ hard links on the primary to recreate them on the standby.=C2=A0 I thought the doc patch was clear on that, but obviously not.=C2=A0 :-(=C2=A0 Suggestions?=C2=A0 (Yes, I admit = that using rsync in this way is super-crafty, and I would _love_ to take credit for the idea, but I think the award goes to Stephen Frost.) > 2. What would the rsync command look like if pg_upgrade wasn't issued wi= th > --link? It would look like: =C2=A0 rsync --archive /opt/PostgreSQL/9.6 standby:/opt/PostgreSQL/9.6 but effectively there isn't anything _in_ standby:/opt/PostgreSQL/9.6, so you are really just using rsync as cp, and frankly I have found 'cp' is faster than rsync when nothing exists on the other side so it really becomes "just copy the cluster when the server is down", but I don't think people even need instructions for that. Maybe we should recommend rsync only for pg_upgrade --link mode? > 3. What if the directory-layout isn't the same on primary and standby, i= e. > 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 stil= l work.=C2=A0 Everything _under_ the standby dir must be laid out the same, = but the directories above it can be different. =C2=A0 =C2=A0 (I know this isn't exactly -hackers food, but it seems natural to end this= =20 thread here) =C2=A0 Ok, thanks. It is clearer what happens now that you've explained that there's a clever= =20 "rsync-trick" involving=C2=A02 directories and making rsync preserving=20 hard-links=C2=A0that way on the destination-server. Maybe it's because I'm = not a=20 native English speaker but it wasn't obvious to me... =C2=A0 I have my tablespaces laid out like this: /storage/fast_ssd/9.6/tablespaces/ which you correctly say that in practice means that 9.6 files are (I see no= w=20 that I don't need the pg-version in my directory-structure): /storage/fast_ssd/9.6/tablespaces//PG_9.6_201608131 =C2=A0 I understand, I hope, that without link-mode rsyncing tablespaces would be= =20 like this: rsync --archive /path/to/tablespace_basedir standby:/path/to/tablespace_bas= edir =C2=A0 What would the equivalent be in link-mode, for transferring most efficientl= y?=20 The reason I ask is that it's not immediately obvious to me what "old_datad= ir"=20 and "new_datadir" when rsync'ing tablespaces and pg_wal dirs outside the=20 "pg-dirs". =C2=A0 Speaking of pg_wal, how should this be rsynced now that it's changed its na= me=20 (from pg_xlog), just rsync pg_xlog and rename it? =C2=A0 I know I'm being a little nitty-gritty here, but if it helps me understand = it=20 might help others. =C2=A0 Thanks. -- Andreas Joseph Krogh =C2=A0 ------=_Part_472_1001175699.1505259317228 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 onsdag 13. september 2017 kl. 01:00:20, skrev Bruce Momjian &l= t;bruce@momjian.us>:
On = Wed, Sep 13, 2017 at 12:40:32AM +0200, Andreas Joseph Krogh wrote:
> P=C3=A5 tirsdag 12. september 2017 kl. 23:52:02, skrev Bruce Momjian &= lt;
> bruce@momjian.us>:
>
>=C2=A0 =C2=A0 =C2=A0On Tue, Sep 12, 2017 at 08:59:05PM +0200, Andreas J= oseph Krogh wrote:
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 =C2=A0Improvements?
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> Thanks, that certainly improves things.
>=C2=A0 =C2=A0 =C2=A0> But; I still find the rsync-command in f) conf= using;
>=C2=A0 =C2=A0 =C2=A0> 1. Why=C2=A0--size-only? From rsync manual: &q= uot;skip files that match in size",
>=C2=A0 =C2=A0 =C2=A0is this
>=C2=A0 =C2=A0 =C2=A0> safe??
>
>
>=C2=A0 =C2=A0 =C2=A0> 2. Why is old_pgdata in the rsync-command, why= is it needed to sync it?
>
>=C2=A0 =C2=A0 =C2=A0If the file exists under the same name, it doesn't = need to be checked at
>=C2=A0 =C2=A0 =C2=A0all --- it is the same.=C2=A0 We don't want to chec= k the file modification
>=C2=A0 =C2=A0 =C2=A0time because it will probably be different because = of replay delay or
>=C2=A0 =C2=A0 =C2=A0clock drift.=C2=A0 We could use checksums, but ther= e is no need since there is
>=C2=A0 =C2=A0 =C2=A0no way the file contents could be different.
>
> =C2=A0
> =C2=A0
> So you're saying that if the file exists (has the same name) on the st= andby (in
> old_pgdata), and has the same size, then you're safe that it contains = the same
> data, hence --size-only?
> Does this apply when not using --link mode for pg_upgrade?

Well, it is really true in every case.=C2=A0 For link mode, we have to use = an
rsync command that lists both the old and new clusters on the command
line (since we need rsync to see those hard links to reproduce them). If we don't use --size-only, we are going to checksum check the _old_ data
cluster.=C2=A0 The new cluster will be empty so we will copy all of that (n= o
need for a checksum there since there are no files).=C2=A0 I think you need=
size-only even without link since that old cluster is going to be listed for rsync.

Now, what you could do, if you are _not_ using link mode, is to rsync
only the new cluster, but the instructions we give work the same for
link and non-link mode and produce the same results in the same time
even if we had a non-link-mode example, so it seems we might as well
just give one set of instructions.

>=C2=A0 =C2=A0 =C2=A0> There are many ways to do/configure things it = seems, resulting in many
>=C2=A0 =C2=A0 =C2=A0ifs and
>=C2=A0 =C2=A0 =C2=A0> buts which makes section 10 rather confusing. = I really think a complete
>=C2=A0 =C2=A0 =C2=A0> example, with absolute paths, would be clarify= ing.
>
>=C2=A0 =C2=A0 =C2=A0You mean a full rsync command, e.g.:
>
>=C2=A0 =C2=A0 =C2=A0=C2=A0 rsync --archive --delete --hard-links --size= -only \
>=C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 =C2=A0 /opt/PostgreSQL/9.5 /opt/Postg= reSQL/9.6 standby:/opt/PostgreSQL
>
>=C2=A0 =C2=A0 =C2=A0Does that help?
>
> =C2=A0
> =C2=A0
> It seems some non-obvious assumptions (to me at least) are made here.<= br> > This example seems only valid when using=C2=A0pg_upgrade --link, corre= ct? If so it
> would be clearer to the reader if explicitly stated.

Well, as I stated above, --hard-links is only going to recreate hard
links on the standby that exist on the primary, and if you didn't use
pg_upgrade's --link mode, there will be none, so it is harmless if
pg_upgrade --link mode was not used.

> 1. Why do you have to rsync both=C2=A0/opt/PostgreSQL/9.5 AND /opt/Pos= tgreSQL/9.6,
> wouldn't=C2=A0/opt/PostgreSQL/9.6 suffice? Or does this assume "p= g_upgrade --link"
> AND "rsync --hard-links" and therefore it somewhat needs to = transfer less data?

As I stated above, rsync has to see _both_ hard links on the primary to
recreate them on the standby.=C2=A0 I thought the doc patch was clear on that, but obviously not.=C2=A0 :-(=C2=A0 Suggestions?=C2=A0 (Yes, I admit t= hat using
rsync in this way is super-crafty, and I would _love_ to take credit for the idea, but I think the award goes to Stephen Frost.)

> 2. What would the rsync command look like if pg_upgrade wasn't issued = with
> --link?

It would look like:

=C2=A0 rsync --archive /opt/PostgreSQL/9.6 standby:/opt/PostgreSQL/9.6

but effectively there isn't anything _in_ standby:/opt/PostgreSQL/9.6,
so you are really just using rsync as cp, and frankly I have found 'cp'
is faster than rsync when nothing exists on the other side so it really
becomes "just copy the cluster when the server is down", but I do= n't
think people even need instructions for that.

Maybe we should recommend rsync only for pg_upgrade --link mode?

> 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 I= f
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 same, b= ut
the directories above it can be different.
=C2=A0
=C2=A0
(I know this isn't exactly -hackers food, but it seems natural to end = this thread here)
=C2=A0
Ok, thanks.
It is clearer what happens now that you've explained that there's a cl= ever "rsync-trick" involving=C2=A02 directories and making rsync = preserving hard-links=C2=A0that way on the destination-server. Maybe it's b= ecause I'm not a native English speaker but it wasn't obvious to me...
=C2=A0
I have my tablespaces laid out like this:
/storage/fast_ssd/9.6/tablespaces/<customer>
which you correctly say that in practice means that 9.6 files are (I s= ee now that I don't need the pg-version in my directory-structure):
/storage/fast_ssd/9.6/tablespaces/<customer>/PG_9.6_201608131
=C2=A0
I understand, I hope, that without link-mode rsyncing tablespaces woul= d be like this:
rsync --archive /path/to/tablespace_basedir standby:/path/to/tablespac= e_basedir
=C2=A0
What would the equivalent be in link-mode, for transferring most effic= iently? The reason I ask is that it's not immediately obvious to me what &q= uot;old_datadir" and "new_datadir" when rsync'ing tablespace= s and pg_wal dirs outside the "pg-dirs".
=C2=A0
Speaking of pg_wal, how should this be rsynced now that it's changed i= ts name (from pg_xlog), just rsync pg_xlog and rename it?
=C2=A0
I know I'm being a little nitty-gritty here, but if it helps me unders= tand it might help others.
=C2=A0
Thanks.

--
Andreas Joseph Krogh
=C2=A0
------=_Part_472_1001175699.1505259317228-- ------=_Part_471_955525209.1505259317215-- ------=_Part_470_1138616347.1505259317215--