Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
Hi -hackers.
I'm reading https://www.postgresql.org/docs/10/static/pgupgrade.html to try to
understand how to upgrade standby-servers using pg_upgrade with pg10.
The text in step 10 sais:
"You will not be running pg_upgrade on the standby servers, but rather rsync",
which to me sounds like rsync, in step 10-f, should be issued on the standy
servers. Is this the case? If so I don't understand how the standby's data is
upgraded and what "remote_dir" is. If rsync is supposed to be issued on the
primary then I think it should be explicitly mentioned, and step 10-f should
provide a clarer example with more detailed values for the directory-structures
involved.
I really think section 10 needs improvement as I'm certainly not comfortable
upgrading standbys following the existing procedure.
Thanks.
--
Andreas Joseph Krogh
On Fri, Jul 28, 2017 at 10:35 AM, Andreas Joseph Krogh
<andreas@visena.com> wrote:
I'm reading https://www.postgresql.org/docs/10/static/pgupgrade.html to try
to understand how to upgrade standby-servers using pg_upgrade with pg10.The text in step 10 sais:
"You will not be running pg_upgrade on the standby servers, but rather
rsync", which to me sounds like rsync, in step 10-f, should be issued on the
standy servers. Is this the case? If so I don't understand how the standby's
data is upgraded and what "remote_dir" is. If rsync is supposed to be issued
on the primary then I think it should be explicitly mentioned, and step 10-f
should provide a clarer example with more detailed values for the
directory-structures involved.I really think section 10 needs improvement as I'm certainly not comfortable
upgrading standbys following the existing procedure.
Yeah, I don't understand it either, and I have never been convinced
that there's any safe way to do it other than recloning the standbys
from the upgraded master.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Jul 31, 2017 at 6:13 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Jul 28, 2017 at 10:35 AM, Andreas Joseph Krogh
<andreas@visena.com> wrote:I'm reading https://www.postgresql.org/docs/10/static/pgupgrade.html to try
to understand how to upgrade standby-servers using pg_upgrade with pg10.The text in step 10 sais:
"You will not be running pg_upgrade on the standby servers, but rather
rsync", which to me sounds like rsync, in step 10-f, should be issued on the
standy servers. Is this the case? If so I don't understand how the standby's
data is upgraded and what "remote_dir" is. If rsync is supposed to be issued
on the primary then I think it should be explicitly mentioned, and step 10-f
should provide a clarer example with more detailed values for the
directory-structures involved.I really think section 10 needs improvement as I'm certainly not comfortable
upgrading standbys following the existing procedure.Yeah, I don't understand it either, and I have never been convinced
that there's any safe way to do it other than recloning the standbys
from the upgraded master.
Here are my 2c on the matter. 10-f means that the upgraded node may
have generated WAL with wal_level = minimal, which, at least it seems
to me, that we have a risk of having inconsistent data pages if only a
rsync is used on the old standbys. Like Robert, the flow we used in
the products I work on is to re-create standbys from scratch after the
upgrade using a fresh backup, with a VM cloning. An upgrade here is an
in-place process not only linked to Postgres, so standby VMs are made
of many services, some are being linked to Postgres. So this choice is
mainly decided by those dependencies, still it feels safer anyway.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Aug 3, 2017 at 11:37:32AM +0200, Michael Paquier wrote:
On Mon, Jul 31, 2017 at 6:13 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Jul 28, 2017 at 10:35 AM, Andreas Joseph Krogh
<andreas@visena.com> wrote:I'm reading https://www.postgresql.org/docs/10/static/pgupgrade.html to try
to understand how to upgrade standby-servers using pg_upgrade with pg10.The text in step 10 sais:
"You will not be running pg_upgrade on the standby servers, but rather
rsync", which to me sounds like rsync, in step 10-f, should be issued on the
standy servers. Is this the case? If so I don't understand how the standby's
data is upgraded and what "remote_dir" is. If rsync is supposed to be issued
on the primary then I think it should be explicitly mentioned, and step 10-f
should provide a clarer example with more detailed values for the
directory-structures involved.I really think section 10 needs improvement as I'm certainly not comfortable
upgrading standbys following the existing procedure.Yeah, I don't understand it either, and I have never been convinced
that there's any safe way to do it other than recloning the standbys
from the upgraded master.Here are my 2c on the matter. 10-f means that the upgraded node may
have generated WAL with wal_level = minimal, which, at least it seems
to me, that we have a risk of having inconsistent data pages if only a
rsync is used on the old standbys. Like Robert, the flow we used in
the products I work on is to re-create standbys from scratch after the
upgrade using a fresh backup, with a VM cloning. An upgrade here is an
in-place process not only linked to Postgres, so standby VMs are made
of many services, some are being linked to Postgres. So this choice is
mainly decided by those dependencies, still it feels safer anyway.
I have applied the attached doc patch back to 9.5 to clarify
pg_upgrade's rsync instructions and explain how it works.
Improvements?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +Attachments:
difftext/x-diff; charset=us-asciiDownload+40-40
På tirsdag 12. september 2017 kl. 19:19:22, skrev Bruce Momjian <
bruce@momjian.us <mailto:bruce@momjian.us>>:
On Thu, Aug 3, 2017 at 11:37:32AM +0200, Michael Paquier wrote:
On Mon, Jul 31, 2017 at 6:13 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Jul 28, 2017 at 10:35 AM, Andreas Joseph Krogh
<andreas@visena.com> wrote:I'm reading https://www.postgresql.org/docs/10/static/pgupgrade.html to
try
to understand how to upgrade standby-servers using pg_upgrade with pg10.
The text in step 10 sais:
"You will not be running pg_upgrade on the standby servers, but rather
rsync", which to me sounds like rsync, in step 10-f, should be issued on
the
standy servers. Is this the case? If so I don't understand how the
standby's
data is upgraded and what "remote_dir" is. If rsync is supposed to be
issued
on the primary then I think it should be explicitly mentioned, and step
10-f
should provide a clarer example with more detailed values for the
directory-structures involved.I really think section 10 needs improvement as I'm certainly not
comfortable
upgrading standbys following the existing procedure.
Yeah, I don't understand it either, and I have never been convinced
that there's any safe way to do it other than recloning the standbys
from the upgraded master.Here are my 2c on the matter. 10-f means that the upgraded node may
have generated WAL with wal_level = minimal, which, at least it seems
to me, that we have a risk of having inconsistent data pages if only a
rsync is used on the old standbys. Like Robert, the flow we used in
the products I work on is to re-create standbys from scratch after the
upgrade using a fresh backup, with a VM cloning. An upgrade here is an
in-place process not only linked to Postgres, so standby VMs are made
of many services, some are being linked to Postgres. So this choice is
mainly decided by those dependencies, still it feels safer anyway.
I have applied the attached doc patch back to 9.5 to clarify
pg_upgrade's rsync instructions and explain how it works.
Improvements?
Thanks, that certainly improves things.
But; I still find the rsync-command in f) confusing;
1. Why --size-only? From rsync manual: "skip files that match in size", is
this safe??
2. Why is old_pgdata in the rsync-command, why is it needed to sync it?
There are many ways to do/configure things it seems, resulting in many ifs and
buts which makes section 10 rather confusing. I really think a complete
example, with absolute paths, would be clarifying.
I'm afraid many will still re-create standbys from scratch without a really
good and complete example to follow.
--
Andreas Joseph Krogh
On Tue, Sep 12, 2017 at 2:59 PM, Andreas Joseph Krogh
<andreas@visena.com> wrote:
There are many ways to do/configure things it seems, resulting in many ifs
and buts which makes section 10 rather confusing. I really think a complete
example, with absolute paths, would be clarifying.I'm afraid many will still re-create standbys from scratch without a really
good and complete example to follow.
And I'm afraid that they won't.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
På tirsdag 12. september 2017 kl. 21:11:45, skrev Robert Haas <
robertmhaas@gmail.com <mailto:robertmhaas@gmail.com>>:
On Tue, Sep 12, 2017 at 2:59 PM, Andreas Joseph Krogh
<andreas@visena.com> wrote:
There are many ways to do/configure things it seems, resulting in many ifs
and buts which makes section 10 rather confusing. I really think a complete
example, with absolute paths, would be clarifying.I'm afraid many will still re-create standbys from scratch without a really
good and complete example to follow.
And I'm afraid that they won't.
Yea. Put it that way - me too:-)
The consequences of not re-creating standbys from scratch and not
understanding section 10, and doing it wrong, are far worse...
--
Andreas Joseph Krogh
On Tue, Sep 12, 2017 at 08:59:05PM +0200, Andreas Joseph Krogh wrote:
Improvements?
Thanks, that certainly improves things.
But; I still find the rsync-command in f) confusing;
1. Why�--size-only? From rsync manual: "skip files that match in size", is this
safe??
2. Why is old_pgdata in the rsync-command, why is it needed to sync it?
If the file exists under the same name, it doesn't need to be checked at
all --- it is the same. We don't want to check the file modification
time because it will probably be different because of replay delay or
clock drift. We could use checksums, but there is no need since there is
no way the file contents could be different.
There are many ways to do/configure things it seems, resulting in many ifs and
buts which makes section 10 rather confusing. I really think a complete
example, with absolute paths, would be clarifying.
You mean a full rsync command, e.g.:
rsync --archive --delete --hard-links --size-only \
/opt/PostgreSQL/9.5 /opt/PostgreSQL/9.6 standby:/opt/PostgreSQL
Does that help?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
På tirsdag 12. september 2017 kl. 23:52:02, skrev Bruce Momjian <
bruce@momjian.us <mailto:bruce@momjian.us>>:
On Tue, Sep 12, 2017 at 08:59:05PM +0200, Andreas Joseph Krogh wrote:
Improvements?
Thanks, that certainly improves things.
But; I still find the rsync-command in f) confusing;
1. Why --size-only? From rsync manual: "skip files that match in size", is
this
safe??
2. Why is old_pgdata in the rsync-command, why is it needed to sync it?
If the file exists under the same name, it doesn't need to be checked at
all --- it is the same. We don't want to check the file modification
time because it will probably be different because of replay delay or
clock drift. We could use checksums, but there is no need since there is
no way the file contents could be different.
So you're saying that if the file exists (has the same name) on the standby
(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?
There are many ways to do/configure things it seems, resulting in many ifs
and
buts which makes section 10 rather confusing. I really think a complete
example, with absolute paths, would be clarifying.
You mean a full rsync command, e.g.:
rsync --archive --delete --hard-links --size-only \
/opt/PostgreSQL/9.5 /opt/PostgreSQL/9.6 standby:/opt/PostgreSQL
Does that help?
It seems some non-obvious assumptions (to me at least) are made here.
This example seems only valid when using pg_upgrade --link, correct? If so it
would be clearer to the reader if explicitly stated.
1. Why do you have to rsync both /opt/PostgreSQL/9.5 AND /opt/PostgreSQL/9.6,
wouldn't /opt/PostgreSQL/9.6 suffice? Or does this assume "pg_upgrade --link"
AND "rsync --hard-links" and therefore it somewhat needs to transfer less data?
2. What would the rsync command look like if pg_upgrade wasn't issued with
--link?
3. What if the directory-layout isn't the same on primary and standby, ie.
tablespaces are located differently?
Thanks.
--
Andreas Joseph Krogh
On Wed, Sep 13, 2017 at 12:40:32AM +0200, Andreas Joseph Krogh wrote:
P� tirsdag 12. september 2017 kl. 23:52:02, skrev Bruce Momjian <
bruce@momjian.us>:On Tue, Sep 12, 2017 at 08:59:05PM +0200, Andreas Joseph Krogh wrote:
� � �Improvements?
Thanks, that certainly improves things.
But; I still find the rsync-command in f) confusing;
1. Why�--size-only? From rsync manual: "skip files that match in size",is this
safe??
2. Why is old_pgdata in the rsync-command, why is it needed to sync it?
If the file exists under the same name, it doesn't need to be checked at
all --- it is the same.� We don't want to check the file modification
time because it will probably be different because of replay delay or
clock drift.� We could use checksums, but there is no need since there is
no way the file contents could be different.�
�
So you're saying that if the file exists (has the same name) on the standby (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. 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. The new cluster will be empty so we will copy all of that (no
need for a checksum there since there are no files). 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.
There are many ways to do/configure things it seems, resulting in many
ifs and
buts which makes section 10 rather confusing. I really think a complete
example, with absolute paths, would be clarifying.You mean a full rsync command, e.g.:
� rsync --archive --delete --hard-links --size-only \
� � � /opt/PostgreSQL/9.5 /opt/PostgreSQL/9.6 standby:/opt/PostgreSQLDoes that help?
�
�
It seems some non-obvious assumptions (to me at least) are made here.
This example seems only valid when using�pg_upgrade --link, correct? 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�/opt/PostgreSQL/9.5 AND /opt/PostgreSQL/9.6,
wouldn't�/opt/PostgreSQL/9.6 suffice? Or does this assume "pg_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. I thought the doc patch was clear on
that, but obviously not. :-( Suggestions? (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 with
--link?
It would look like:
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, 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. If
your tbldir is different on the primary and standby, rsync will still
work. Everything _under_ the standby dir must be laid out the same, but
the directories above it can be different.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
På onsdag 13. september 2017 kl. 01:00:20, skrev Bruce Momjian <bruce@momjian.us
<mailto:bruce@momjian.us>>:
On Wed, Sep 13, 2017 at 12:40:32AM +0200, Andreas Joseph Krogh wrote:
På tirsdag 12. september 2017 kl. 23:52:02, skrev Bruce Momjian <
bruce@momjian.us>:On Tue, Sep 12, 2017 at 08:59:05PM +0200, Andreas Joseph Krogh wrote:
> Improvements?
>
> Thanks, that certainly improves things.
> But; I still find the rsync-command in f) confusing;
> 1. Why --size-only? From rsync manual: "skip files that match in
size",
is this
> safe??> 2. Why is old_pgdata in the rsync-command, why is it needed to sync
it?
If the file exists under the same name, it doesn't need to be checked at
all --- it is the same. We don't want to check the file modification
time because it will probably be different because of replay delay or
clock drift. We could use checksums, but there is no need since there
is
no way the file contents could be different.
So you're saying that if the file exists (has the same name) on the standby
(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. 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. The new cluster will be empty so we will copy all of that (no
need for a checksum there since there are no files). 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.
> There are many ways to do/configure things it seems, resulting in many
ifs and
> buts which makes section 10 rather confusing. I really think a
complete
> example, with absolute paths, would be clarifying.
You mean a full rsync command, e.g.:
rsync --archive --delete --hard-links --size-only \
/opt/PostgreSQL/9.5 /opt/PostgreSQL/9.6 standby:/opt/PostgreSQLDoes that help?
It seems some non-obvious assumptions (to me at least) are made here.
This example seems only valid when using pg_upgrade --link, correct? 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 /opt/PostgreSQL/9.5 AND
/opt/PostgreSQL/9.6,
wouldn't /opt/PostgreSQL/9.6 suffice? Or does this assume "pg_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. I thought the doc patch was clear on
that, but obviously not. :-( Suggestions? (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 with
--link?
It would look like:
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, 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. If
your tbldir is different on the primary and standby, rsync will still
work. Everything _under_ the standby dir must be laid out the same, but
the directories above it can be different.
(I know this isn't exactly -hackers food, but it seems natural to end this
thread here)
Ok, thanks.
It is clearer what happens now that you've explained that there's a clever
"rsync-trick" involving 2 directories and making rsync preserving
hard-links that way on the destination-server. Maybe it's because I'm not a
native English speaker but it wasn't obvious to me...
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 see now
that I don't need the pg-version in my directory-structure):
/storage/fast_ssd/9.6/tablespaces/<customer>/PG_9.6_201608131
I understand, I hope, that without link-mode rsyncing tablespaces would be
like this:
rsync --archive /path/to/tablespace_basedir standby:/path/to/tablespace_basedir
What would the equivalent be in link-mode, for transferring most efficiently?
The reason I ask is that it's not immediately obvious to me what "old_datadir"
and "new_datadir" when rsync'ing tablespaces and pg_wal dirs outside the
"pg-dirs".
Speaking of pg_wal, how should this be rsynced now that it's changed its name
(from pg_xlog), just rsync pg_xlog and rename it?
I know I'm being a little nitty-gritty here, but if it helps me understand it
might help others.
Thanks.
--
Andreas Joseph Krogh
Bruce, all,
* Bruce Momjian (bruce@momjian.us) wrote:
On Wed, Sep 13, 2017 at 12:40:32AM +0200, Andreas Joseph Krogh wrote:
På tirsdag 12. september 2017 kl. 23:52:02, skrev Bruce Momjian <
bruce@momjian.us>:On Tue, Sep 12, 2017 at 08:59:05PM +0200, Andreas Joseph Krogh wrote:
Improvements?
Thanks, that certainly improves things.
But; I still find the rsync-command in f) confusing;
1. Why --size-only? From rsync manual: "skip files that match in size",is this
safe??
2. Why is old_pgdata in the rsync-command, why is it needed to sync it?
If the file exists under the same name, it doesn't need to be checked at
all --- it is the same. We don't want to check the file modification
time because it will probably be different because of replay delay or
clock drift. We could use checksums, but there is no need since there is
no way the file contents could be different.
So you're saying that if the file exists (has the same name) on the standby (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. 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. The new cluster will be empty so we will copy all of that (no
need for a checksum there since there are no files). I think you need
size-only even without link since that old cluster is going to be listed
for rsync.
The above is correct- the old and new are required to get rsync to build
the same hard-link tree on the replica as exists on the primary, post
pg_upgrade. Also, if --link isn't used with pg_upgrade then you'd want
--size-only with the existing command or you'd end up probably copying
both the old and new clusters and that'd be a lot of additional work.
Other points of clarification here:
Rsync, by default, does *not* use checksums.
The data files on the replica and the data files on the primary do *not*
match bit-for-bit, --checksum will never work (or, rather, it'll always
end up copying everything except in extremely rare circumstances that
would be pure luck). What matters, however, is that the differences
aren't interesting to PG, any more than they are when it comes to doing
WAL replay.
If --link is *not* used with pg_upgrade, then there's not much point in
using this rsync as it shouldn't be particularly different from just
doing the typical:
rsync --archive new_pgdata remote_dir
post pg_upgrade, though of course that would incur a large amount of
data transfer across the network.
I wouldn't suggest trying to copy the old data dir on the remote to the
new data dir and then doing an rsync- that way lies madness as you would
be copying over catalog files from the old data dir and those could end
up having the same size as the same catalog files post-upgrade on the
primary and then you end up with some odd mix between the two. That's
bad. You'd have to identify the catalog files independently and be sure
to exclude them from the copy and that isn't something I would encourage
anyone to try and do. The rsync --hard-link method with pg_upgrade
--link will get this correct, to be clear.
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.
For my 2c, at least, I would have specifically pointed out that this
method is really only for when you're using --link mode with pg_upgrade.
If you're not using --link then there's other ways to do this which
would be more efficient than an rsync and which could be done after the
primary is back online (such as doing a backup/restore to rebuild the
replica, or similar).
There are many ways to do/configure things it seems, resulting in many
ifs and
buts which makes section 10 rather confusing. I really think a complete
example, with absolute paths, would be clarifying.You mean a full rsync command, e.g.:
rsync --archive --delete --hard-links --size-only \
/opt/PostgreSQL/9.5 /opt/PostgreSQL/9.6 standby:/opt/PostgreSQLDoes that help?
It seems some non-obvious assumptions (to me at least) are made here.
This example seems only valid when using pg_upgrade --link, correct? 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.
The rsync will recreate the hard links *and* copy the new catalog data
files over from the upgraded primary. It will specifically *not* copy
over into the new cluster anything from the old data dir, and that's
important.
I agree that --hard-links should be harmless if you're not using --link,
but as I say above, this approach doesn't really make sense if you're
not using --link and it can clearly be confusing to people to not have
this method caveated in that way.
1. Why do you have to rsync both /opt/PostgreSQL/9.5 AND /opt/PostgreSQL/9.6,
wouldn't /opt/PostgreSQL/9.6 suffice? Or does this assume "pg_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. I thought the doc patch was clear on
that, but obviously not. :-( Suggestions? (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.)
Indeed, this is a method I've used previously, with good success, to
speed up getting a replica back online following a pg_upgrade. There's
some additional caveats on it that we haven't even discussed yet here:
Unlogged tables will end up getting copied by this rsync. That's not
the end of the world and won't harm anything, afaik, but having all the
unlogged data copied to the replicas ends up using space on the replicas
unnecessairly and will make the transfer of data take longer as well.
2. What would the rsync command look like if pg_upgrade wasn't issued with
--link?It would look like:
rsync --archive /opt/PostgreSQL/9.6 standby:/opt/PostgreSQL/9.6
Right.
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.
Well, the above rsync would go over the network whereas a traditional
'cp' won't.
I tend to agree that we don't really need a lot of documentation around
"copy the resulting cluster to the replica while the server is down",
but that then goes against your argument above that this approach is
good for both --link and without --link.
Maybe we should recommend rsync only for pg_upgrade --link mode?
Yes, I think we should.
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
reached the same point prior to the pg_upgrade. If they didn't, then
things could go badly as there's unplayed WAL that the primary got
through and the replica didn't.
- Not copy over unlogged data, or any other information that shouldn't
be copied across.
- Allow the directory structures to be more different between the
primary and the replica than rsync allows (wouldn't have to have a
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. If
your tbldir is different on the primary and standby, rsync will still
work. 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. (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. 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
På onsdag 13. september 2017 kl. 01:38:40, skrev Stephen Frost <
sfrost@snowman.net <mailto: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
reached the same point prior to the pg_upgrade. If they didn't, then
things could go badly as there's unplayed WAL that the primary got
through and the replica didn't.
- Not copy over unlogged data, or any other information that shouldn't
be copied across.
- Allow the directory structures to be more different between the
primary and the replica than rsync allows (wouldn't have to have a
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. If
your tbldir is different on the primary and standby, rsync will still
work. 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. (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. 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
Thanks for th explaination.
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 then making it
standby again after pg_upgrade is finished?
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
Andreas,
* Andreas Joseph Krogh (andreas@visena.com) wrote:
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 then making it
standby again after pg_upgrade is finished?
I don't think that we could be guaranteed that the catalog tables would
be the same on the replica as on the primary if they were actually
created by pg_upgrade.
The catalog tables *must* be identical between the primary and the
replica because they are updated subsequently through WAL replay, not
through SQL commands (which is how pg_upgrade creates them in the first
place).
Perhaps we could have some mode for pg_upgrade where it handles the
update to replicas (with the additional checks that I outlined and using
the methodology discussed for rsync --hard-links), but that would still
require solving the communicate-over-the-network problem between the
primary and the replicas, which is the hard part. Whether it's an
independent utility or something built into pg_upgrade isn't really that
big of a distinction, though it doesn't seem to me like there'd be much
code reuse there.
Thanks!
Stephen
På onsdag 13. september 2017 kl. 01:54:15, skrev Stephen Frost <
sfrost@snowman.net <mailto:sfrost@snowman.net>>:
Andreas,
* Andreas Joseph Krogh (andreas@visena.com) wrote:
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 then making
it
standby again after pg_upgrade is finished?
I don't think that we could be guaranteed that the catalog tables would
be the same on the replica as on the primary if they were actually
created by pg_upgrade.
The catalog tables *must* be identical between the primary and the
replica because they are updated subsequently through WAL replay, not
through SQL commands (which is how pg_upgrade creates them in the first
place).
Perhaps we could have some mode for pg_upgrade where it handles the
update to replicas (with the additional checks that I outlined and using
the methodology discussed for rsync --hard-links), but that would still
require solving the communicate-over-the-network problem between the
primary and the replicas, which is the hard part. Whether it's an
independent utility or something built into pg_upgrade isn't really that
big of a distinction, though it doesn't seem to me like there'd be much
code reuse there.
Thanks!
Stephen
Thanks.
--
Andreas Joseph Krogh
On Tue, Sep 12, 2017 at 07:54:15PM -0400, Stephen Frost wrote:
Andreas,
* Andreas Joseph Krogh (andreas@visena.com) wrote:
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 then making it
standby again after pg_upgrade is finished?I don't think that we could be guaranteed that the catalog tables would
be the same on the replica as on the primary if they were actually
created by pg_upgrade.
FYI, the other problem is that standby can't go into write mode or it
would diverge from the primary.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Sep 13, 2017 at 01:35:17AM +0200, Andreas Joseph Krogh wrote:
P� onsdag 13. september 2017 kl. 01:00:20, skrev Bruce Momjian <
bruce@momjian.us>:
(I know this isn't exactly -hackers food, but it seems natural to end this
thread here)
�
Ok, thanks.
It is clearer what happens now that you've explained that there's a clever
"rsync-trick" involving�2 directories and making rsync preserving
hard-links�that way on the destination-server. Maybe it's because I'm not a
native English speaker but it wasn't obvious to me...
�
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 see now
that I don't need the pg-version in my directory-structure):
/storage/fast_ssd/9.6/tablespaces/<customer>/PG_9.6_201608131
�
I understand, I hope, that without link-mode rsyncing tablespaces would be like
this:
rsync --archive /path/to/tablespace_basedir standby:/path/to/tablespace_basedir
�
What would the equivalent be in link-mode, for transferring most efficiently?
The reason I ask is that it's not immediately obvious to me what "old_datadir"
and "new_datadir" when rsync'ing tablespaces and pg_wal dirs outside the
"pg-dirs".
�
Speaking of pg_wal, how should this be rsynced now that it's changed its name
(from pg_xlog), just rsync pg_xlog and rename it?
�
I know I'm being a little nitty-gritty here, but if it helps me understand it
might help others.
I have applied the attached patch to show examples of using rsync on
PGDATA and tablespaces, documented that rsync is only useful when in
link mode, and explained more clearly how rsync handles links. You can
see the results here:
http://momjian.us/pgsql_docs/pgupgrade.html
Any more improvements?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +Attachments:
difftext/plain; charset=us-asciiDownload+64-64
På onsdag 13. september 2017 kl. 15:26:27, skrev Bruce Momjian <bruce@momjian.us
<mailto:bruce@momjian.us>>:
On Wed, Sep 13, 2017 at 01:35:17AM +0200, Andreas Joseph Krogh wrote:
[snip]
I know I'm being a little nitty-gritty here, but if it helps me understand
it
might help others.
I have applied the attached patch to show examples of using rsync on
PGDATA and tablespaces, documented that rsync is only useful when in
link mode, and explained more clearly how rsync handles links. You can
see the results here:
http://momjian.us/pgsql_docs/pgupgrade.html
Any more improvements?
Very nice!
For sake of completeness I think an example of running rsync when
having pg_wal located outside the data directories would be helpful. Especially
an example upgrading from 9.6 to 10 because of the name-change of pg_xlog ->
pg_wal.
--
Andreas Joseph Krogh
Bruce,
* Bruce Momjian (bruce@momjian.us) wrote:
I have applied the attached patch to show examples of using rsync on
PGDATA and tablespaces, documented that rsync is only useful when in
link mode, and explained more clearly how rsync handles links. You can
see the results here:http://momjian.us/pgsql_docs/pgupgrade.html
Any more improvements?
First off, I'd strongly suggest that we make "Step 1" in the pg_upgrade
process be "take a full backup and verify that you're able to restore it
successfully and without corruption."
I don't particularly care for how this seems to imply that the Rsync
method is "the" method to use when --link mode is used with pg_upgrade.
I'd reword the section title to be along these lines:
If you have streaming replicas or log-shipping standby servers then they
will also need to be updated. The simplest way to accomplish this is to
simply rebuild the replicas from scratch once the primary is back
online. Unfortunately, that can take a long time for larger systems as
the data has to be copied from the primary to each replica in the
environment. If --link mode was used with pg_upgrade, the Latest
checkpoint location matches between the primary and the replica(s) (as
discussed in Step 8), the rsync utility is available, and the existing
data directory and new data directory on the replica are able to be in a
common directory on the same filesystem (as is required on the primary
for --link mode to be used), then an alternative method may be used to
update the replicas using rsync which will generally require much less
time.
Note that this method will end up needlessly copying across temporary
files and unlogged tables. If these make up a large portion of your
database, then rebuilding the replicas from scratch may be a better
option.
With this method, you will not be running pg_upgrade on the standby
servers, but rather rsync on the primary to sync the replicas to match
the results of the pg_upgrade on the primary. Do not start any servers
yet. If you did not use link mode, skip the instructions in this
section and simply recreate the standby servers.
This method requires that the *old* data directory on the replica be in
place as rsync will be creating a hard-link tree between the old data
files on the replica and the new data directory on the replica (as was
done by pg_upgrade on the primary).
a. Install the new PostgreSQL binaries on standby servers.
...
b. Make sure the new standby data directories do not exist
If initdb was run on the replica to create a new data directory, remove
that new data directory (the rsync will recreate it). Do *not* remove
the existing old data directory.
c. Install custom shared object files
** I would probably move this up to be step 'b' instead, and make step
'b' be step 'c' instead.
d. Stop standby servers
...
*new*
e. Verify/re-verify that Latest checkpoint location in pg_controldata
on the replica matches that of the primary (from before the primary
was upgraded with pg_upgrade).
f. Save configuration files
** this should have a caveat that it's only necessary if the config
files are in the data directory.
g. Run rsync
** I am having a hard time figuring out why --delete makes sense here.
There shouldn't be anything in the new data directory, and we don't
actually need to delete anything in the old data directory on the
replica, so what are we doing suggesting --delete be used? Strikes me
as unnecessairly adding risk, should someone end up doing the wrong
command. Also, again, if I was doing this, I'd absolutely run rsync
with --dry-run for starters and review what it is going to do and make
sure that's consistent with what I'd expect.
Thanks!
Stephen
On Tue, Sep 12, 2017 at 07:38:40PM -0400, Stephen Frost wrote:
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
reached the same point prior to the pg_upgrade. If they didn't, then
things could go badly as there's unplayed WAL that the primary got
through and the replica didn't.- Not copy over unlogged data, or any other information that shouldn't
be copied across.- Allow the directory structures to be more different between the
primary and the replica than rsync allows (wouldn't have to have a
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...?).
Maybe pg_rewind's mechanism could be partially reused for this as it
seems to accomplish something vaguely similar AIUI?
Michael
--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de
credativ GmbH, HRB M�nchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 M�nchengladbach
Gesch�ftsf�hrung: Dr. Michael Meskes, J�rg Folz, Sascha Heuer
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers