Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

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 +

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/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.

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/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.

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/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.

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&gt;
<https://www.visena.com&gt;

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 +

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

On Wed, Sep 13, 2017 at 04:31:09PM +0200, Andreas Joseph Krogh wrote:

P� onsdag 13. september 2017 kl. 15:26:27, skrev Bruce Momjian <
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.

I think the tablespace example is clear enough to modify for WAL and we
instruct them right below that example to do WAL.

--
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å torsdag 14. september 2017 kl. 20:39:34, skrev Bruce Momjian <
bruce@momjian.us <mailto:bruce@momjian.us>>:
On Wed, Sep 13, 2017 at 04:31:09PM +0200, Andreas Joseph Krogh wrote:

På onsdag 13. september 2017 kl. 15:26:27, skrev Bruce Momjian <
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.

I think the tablespace example is clear enough to modify for WAL and we
instruct them right below that example to do WAL.
 
Well, it's not following the exact same structure as there's no
"version-directory" in pg_xlog, so the "rsync the version-dirs into it's parent
on the target" isn't what's happening.
 
That's why I think this makes sense to mention for the sake of a complete
example:
rsync --archive --delete --hard-links --size-only /vol1/postgres/9.6/pg_xlog
\ /vol1/postgres/10/pg_wal standby.example.com:/vol1/postgres/10/pg_wal
 
Thanks.
 
--
Andreas Joseph Krogh
 

On Wed, Sep 13, 2017 at 12:16:33PM -0400, Stephen Frost wrote:

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 am hesitant to add pg_upgrade-specific nanny language but if we want
to review all upgrade methods and make recommendations, we can do that.
If we need to add more --link-specific warnings, please suggest that.
Thanks.

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.

Agreed. I have added new text in the attached patch to make it clear
that non-rsync is an option and is easier.

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).

Sorry, I didn't use any of the above text. It seems to be a step
backward in clarity.

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.

I clarified "new data directory" in the patch.

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.

Why move it? The current ordering seems more logical.

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).

I added text in the pg_controldata paragraph to mention which standby
upgrade method is references. Repeating the pg_controldata check seems
pointless here.

f. Save configuration files

** this should have a caveat that it's only necessary if the config
files are in the data directory.

I clarified "data directory" in the patch.

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.

I talked with Stephen about this on IM. The issue is that if you don't
do --delete, and there are files in the primary that are not in the
standby, they are copied, but files in the standby and not in the
primary are kept. This could lead to mixed primary/standby log files,
or worse. Using --delete means the new standby exactly matches the new
primary and all the steps you need to adjust after a base backup are the
same.

I added a mention of rsync --dry-run per Stephen's suggestion.

I have also added a paragraph from Magnus that I developed via IM that
explains that you can use rsync to upgrade one standby from another
standby, if the standby has not been started.

--
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 +

On Thu, Sep 14, 2017 at 08:49:24PM +0200, Andreas Joseph Krogh wrote:

I think the tablespace example is clear enough to modify for WAL and we
instruct them right below that example to do WAL.

�
Well, it's not following the exact same structure as there's no
"version-directory" in pg_xlog, so the "rsync the version-dirs into it's parent
on the target"�isn't what's happening.
�
That's why I think this makes sense to mention for the sake of a complete
example:

rsync --archive --delete --hard-links --size-only /vol1/postgres/9.6/pg_xlog \
/vol1/postgres/10/pg_wal standby.example.com:/vol1/postgres/10/pg_wal

Well, there is technically no need for version directories in pgdata
either --- installers just create them.

--
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 07:39:31PM +0200, Michael Banck wrote:

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?

pg_rewind works at the WAL level while this is at the file system level.

--
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å torsdag 14. september 2017 kl. 21:13:56, skrev Bruce Momjian <
bruce@momjian.us <mailto:bruce@momjian.us>>:
On Thu, Sep 14, 2017 at 08:49:24PM +0200, Andreas Joseph Krogh wrote:

     I think the tablespace example is clear enough to modify for WAL and we
     instruct them right below that example to do WAL.

 
Well, it's not following the exact same structure as there's no
"version-directory" in pg_xlog, so the "rsync the version-dirs into it's

parent

on the target" isn't what's happening.
 
That's why I think this makes sense to mention for the sake of a complete
example:

rsync --archive --delete --hard-links --size-only

/vol1/postgres/9.6/pg_xlog \

       /vol1/postgres/10/pg_wal standby.example.com:/vol1/postgres/10/pg_wal

Well, there is technically no need for version directories in pgdata
either --- installers just create them.
 
 
I tested upgrading from 9.6 to 10 now, using pg_upgrade, and pg_upgrade
creates the new data-dir with pg_wal "in it" (just like regular initdb), so
pg_upgrade seems not to care about where the old version's pg_xlog was. You
have to move (by symlinking) pg_wal to a separate location manually *after*
running pg_upgrade on the master. No special handling is needed when rsync'ing
it over to the standby, so it doesn't need any --hard-links or --size-only,
correct?
 
Given the path, on the upgraded primary, to pg_wal is /custom/path/to/pg_wal,
the rsync command will be:
 
rsync --archive --delete /custom/path/to/pg_wal
standby.example.com:/custom/path/to/pg_wal
 
I think it's useful to mention this to eliminate any doubt.
 
I also think it's worth mentioning that you have to manually move pg_wal to a
custom location after running pg_upgrade as it will not preserve/use the old
path.
 
Thanks.

--
Andreas Joseph Krogh

On Fri, Sep 15, 2017 at 8:23 AM, Andreas Joseph Krogh
<andreas@visena.com> wrote:

I tested upgrading from 9.6 to 10 now, using pg_upgrade, and pg_upgrade
creates the new data-dir with pg_wal "in it" (just like regular initdb), so
pg_upgrade seems not to care about where the old version's pg_xlog was. You
have to move (by symlinking) pg_wal to a separate location manually *after*
running pg_upgrade on the master.

That's true, this should definitely be mentioned in the documentation.
An improvement could be done as well here for pg_upgrade: when using
--link, the new PGDATA created could consider as well the source
pg_wal and create a link to it, and then clean up its contents. I am
not completely sure if this would be worth doing as people are likely
used to the current flow though. The documentation needs to outline
the matter at least.
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Michael, all,

* Michael Paquier (michael.paquier@gmail.com) wrote:

On Fri, Sep 15, 2017 at 8:23 AM, Andreas Joseph Krogh
<andreas@visena.com> wrote:

I tested upgrading from 9.6 to 10 now, using pg_upgrade, and pg_upgrade
creates the new data-dir with pg_wal "in it" (just like regular initdb), so
pg_upgrade seems not to care about where the old version's pg_xlog was. You
have to move (by symlinking) pg_wal to a separate location manually *after*
running pg_upgrade on the master.

That's true, this should definitely be mentioned in the documentation.

Uh, this seems like something that should be *fixed*, not documented.
That initdb accepts an alternative location for pg_xlog/pg_wal now
raises that to a first-class feature, in my view, and other tools should
recognize the case and deal with it correctly.

Of course, that having been said, there's some technical challenges
there. One being that we don't really want to mess with the old
cluster's WAL during pg_upgrade. Frustratingly, we have a way to deal
with that case today for tablespaces, it was just never applied to WAL
when we started allowing WAL to be stored elsewhere (formally). That
seems like it was a mistake to me.

Then again, the more I think about this, the more I wonder about putting
more-or-less everything in PGDATA into per-catalog-version directories
and making everything self-contained. Part of the ugly bit with the
rsync is exactly that we have to go up an extra level for the data
directories themselves, and users can actually put them anywhere so
there might not even *be* a common parent directory to use.

An improvement could be done as well here for pg_upgrade: when using
--link, the new PGDATA created could consider as well the source
pg_wal and create a link to it, and then clean up its contents. I am
not completely sure if this would be worth doing as people are likely
used to the current flow though. The documentation needs to outline
the matter at least.

No, one of the baseline requirements of pg_upgrade is to *not* screw
with the existing cluster. Removing its WAL or "cleaning it up"
definitely seems like it's violating that principle.

I tend to agree that it'd be good for the documentation to address this,
but this is all really getting to be a bit much for a manpage to be able
to handle, I think..

Thaks!

Stephen

On Fri, Sep 15, 2017 at 10:21 AM, Stephen Frost <sfrost@snowman.net> wrote:

No, one of the baseline requirements of pg_upgrade is to *not* screw
with the existing cluster. Removing its WAL or "cleaning it up"
definitely seems like it's violating that principle.

Not necessarily. Using --link it is game over for rollback once the
new cluster has started. My reference to clean up the contents of
pg_xlog refers to the moment the new cluster has been started. This
could be achieved with a pre-upgrade flag present on-disk that the
startup process looks at to perform actions needed. This flag of
course needs to depend on the version of the binary used to start
Postgres, and is written by pg_upgrade itself which links the new
cluster's pg_wal into the location of the old cluster. In short, if an
upgrade to PG version N is done, and that the flag related to the
cleanup of N is found, then actions happen. If Postgres is started
with a binary version N-1, nothing happens, and existing flags are
cleaned up. What I am not sure is if such extra machinery is worth
doing as things can be saved by just moving the soft link position of
the cluster after running pg_upgrade and before starting the new
cluster.

I tend to agree that it'd be good for the documentation to address this,
but this is all really getting to be a bit much for a manpage to be able
to handle, I think..

Definitely.
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Michael,

* Michael Paquier (michael.paquier@gmail.com) wrote:

On Fri, Sep 15, 2017 at 10:21 AM, Stephen Frost <sfrost@snowman.net> wrote:

No, one of the baseline requirements of pg_upgrade is to *not* screw
with the existing cluster. Removing its WAL or "cleaning it up"
definitely seems like it's violating that principle.

Not necessarily. Using --link it is game over for rollback once the
new cluster has started.

Yes, but not *before* the new cluster has started.

My reference to clean up the contents of
pg_xlog refers to the moment the new cluster has been started.

Alright, that's technically beyond the scope of pg_upgrade then...

This
could be achieved with a pre-upgrade flag present on-disk that the
startup process looks at to perform actions needed. This flag of
course needs to depend on the version of the binary used to start
Postgres, and is written by pg_upgrade itself which links the new
cluster's pg_wal into the location of the old cluster. In short, if an
upgrade to PG version N is done, and that the flag related to the
cleanup of N is found, then actions happen. If Postgres is started
with a binary version N-1, nothing happens, and existing flags are
cleaned up. What I am not sure is if such extra machinery is worth
doing as things can be saved by just moving the soft link position of
the cluster after running pg_upgrade and before starting the new
cluster.

Ugh. That strikes me as far more complication than would be good for
this..

Thanks!

Stephen

On Fri, Sep 15, 2017 at 01:23:45AM +0200, Andreas Joseph Krogh wrote:

I tested upgrading from 9.6 to 10�now, using pg_upgrade, and pg_upgrade creates
the new data-dir with pg_wal "in it" (just like regular initdb), so pg_upgrade
seems not to care about where the old version's pg_xlog was. You have to move
(by symlinking)�pg_wal to a separate location manually *after* running
pg_upgrade on the master. No special handling is needed when rsync'ing it over
to the standby, so it doesn't need any�--hard-links or --size-only, correct?

What rsync is going to do is to reproduce the directory structure of the
old cluster _in_ the standby's old cluster, and the structure of the new
cluster on the standby's new cluster. If you had the WAL directory
relocated in the new cluster, the relocation symbolic link will be
reproduced by rsync, but the directory it points _to_ will not be
copied, so it will point to nothing.

Of course, of both old and new clusters share the same WAL directory,
which I think is impossible, things would get very confusing quickly. I
will reply to this now in a later email.

--
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 Thu, Sep 14, 2017 at 09:21:25PM -0400, Stephen Frost wrote:

Michael, all,

* Michael Paquier (michael.paquier@gmail.com) wrote:

On Fri, Sep 15, 2017 at 8:23 AM, Andreas Joseph Krogh
<andreas@visena.com> wrote:

I tested upgrading from 9.6 to 10 now, using pg_upgrade, and pg_upgrade
creates the new data-dir with pg_wal "in it" (just like regular initdb), so
pg_upgrade seems not to care about where the old version's pg_xlog was. You
have to move (by symlinking) pg_wal to a separate location manually *after*
running pg_upgrade on the master.

That's true, this should definitely be mentioned in the documentation.

Uh, this seems like something that should be *fixed*, not documented.
That initdb accepts an alternative location for pg_xlog/pg_wal now
raises that to a first-class feature, in my view, and other tools should
recognize the case and deal with it correctly.

Of course, that having been said, there's some technical challenges
there. One being that we don't really want to mess with the old
cluster's WAL during pg_upgrade. Frustratingly, we have a way to deal
with that case today for tablespaces, it was just never applied to WAL
when we started allowing WAL to be stored elsewhere (formally). That
seems like it was a mistake to me.

Then again, the more I think about this, the more I wonder about putting
more-or-less everything in PGDATA into per-catalog-version directories
and making everything self-contained. Part of the ugly bit with the
rsync is exactly that we have to go up an extra level for the data
directories themselves, and users can actually put them anywhere so
there might not even *be* a common parent directory to use.

I am going to need to outline where I think we are before I can suggest
a solution.

What we did with the tablespace directory is to use the catalog version
_inside_ the tablespace directory, e.g.:

/vol1/pg_tblsp/PG_9.5_201510051
/vol1/pg_tblsp/PG_9.6_201608131

We did not do this for the WAL directory because by _default_ it is in
PGDATA, which is major-version specific. Where this breaks is when the
initdb --waldir option is used. We could have created a major version
subdirectory inside the directory specified by --waldir, but that would
have made the PGDATA contents and the --waldir differ, and I think it
would have been confusing.

What we have now is the problem that perhaps people are not creating
major-version-specific names for --waldir. I am not sure how anyone is
doing an upgrade except for dumping the data, deleteing the old cluster
and the old WAL directory, recreating the new cluster and new WAL
directory, and then loading the data. Because pg_upgrade needs to have
the old and new servers running, having different external WAL
directories for each cluster is a requirement.

We have not had any complaints about this so I am confused why it is now
an issue just because of rsync. If they created separate WAL
directories on the primary, they will need separate ones on the standby,
the symlinks will be copied, and they need to use rsync to copy stuff.
There are no hard links in there, but using the link option should be
harmless.

An improvement could be done as well here for pg_upgrade: when using
--link, the new PGDATA created could consider as well the source
pg_wal and create a link to it, and then clean up its contents. I am
not completely sure if this would be worth doing as people are likely
used to the current flow though. The documentation needs to outline
the matter at least.

No, one of the baseline requirements of pg_upgrade is to *not* screw
with the existing cluster. Removing its WAL or "cleaning it up"
definitely seems like it's violating that principle.

I tend to agree that it'd be good for the documentation to address this,
but this is all really getting to be a bit much for a manpage to be able
to handle, I think..

Yes, I am struggling with this too.

--
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 Fri, Sep 15, 2017 at 01:23:45AM +0200, Andreas Joseph Krogh wrote:

I tested upgrading from 9.6 to 10�now, using pg_upgrade, and pg_upgrade creates
the new data-dir with pg_wal "in it" (just like regular initdb), so pg_upgrade
seems not to care about where the old version's pg_xlog was. You have to move
(by symlinking)�pg_wal to a separate location manually *after* running
pg_upgrade on the master. No special handling is needed when rsync'ing it over
to the standby, so it doesn't need any�--hard-links or --size-only, correct?
�
Given the path,�on the upgraded primary,�to�pg_wal�is�/custom/path/to/pg_wal,
the rsync command will be:
�
rsync --archive --delete /custom/path/to/pg_wal standby.example.com:/custom/
path/to/pg_wal
�
I think it's useful to mention this to eliminate any doubt.
�
I also think it's worth mentioning that you have to manually move pg_wal to a
custom location after running pg_upgrade as it will not preserve/use the old
path.

Thinking some more, you are right that there is no need to rsync the
_old_ primary WAL directory since it is the same on the standby old WAL
directory, and there are no links between the old and new WAL
directories, so you could just do the new one, or just copy it and not
even use rsync.

However, I think it adds complexity to try to optimize the copy of the
WAL files and we are better just requiring them to use the same steps
for WAL copy that they _must_ use for the data directory and
tablespaces because of the links between old and new files there.

Agreed?

--
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 Thu, Sep 14, 2017 at 03:12:50PM -0400, Bruce Momjian wrote:

I talked with Stephen about this on IM. The issue is that if you don't
do --delete, and there are files in the primary that are not in the
standby, they are copied, but files in the standby and not in the
primary are kept. This could lead to mixed primary/standby log files,
or worse. Using --delete means the new standby exactly matches the new
primary and all the steps you need to adjust after a base backup are the
same.

I added a mention of rsync --dry-run per Stephen's suggestion.

I have also added a paragraph from Magnus that I developed via IM that
explains that you can use rsync to upgrade one standby from another
standby, if the standby has not been started.

Patch applied through 9.5. Updated docs at:

http://momjian.us/pgsql_docs/pgupgrade.html

--
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å lørdag 16. september 2017 kl. 17:24:14, skrev Bruce Momjian <bruce@momjian.us
<mailto:bruce@momjian.us>>:
On Fri, Sep 15, 2017 at 01:23:45AM +0200, Andreas Joseph Krogh wrote:

I tested upgrading from 9.6 to 10 now, using pg_upgrade, and pg_upgrade

creates

the new data-dir with pg_wal "in it" (just like regular initdb), so

pg_upgrade

seems not to care about where the old version's pg_xlog was. You have to

move

(by symlinking) pg_wal to a separate location manually *after* running
pg_upgrade on the master. No special handling is needed when rsync'ing it

over

to the standby, so it doesn't need any --hard-links or --size-only, correct?
 
Given the path, on the upgraded

primary, to pg_wal is /custom/path/to/pg_wal,

the rsync command will be:
 
rsync --archive --delete /custom/path/to/pg_wal standby.example.com:/custom/
path/to/pg_wal
 
I think it's useful to mention this to eliminate any doubt.
 
I also think it's worth mentioning that you have to manually move pg_wal to

a

custom location after running pg_upgrade as it will not preserve/use the old
path.

Thinking some more, you are right that there is no need to rsync the
_old_ primary WAL directory since it is the same on the standby old WAL
directory, and there are no links between the old and new WAL
directories, so you could just do the new one, or just copy it and not
even use rsync.

However, I think it adds complexity to try to optimize the copy of the
WAL files and we are better just requiring them to use the same steps
for WAL copy that they _must_ use for the data directory and
tablespaces because of the links between old and new files there.

Agreed?
 
I'm a little unsure what scenario we're trying to describe here. Copying the
pg_wal separately (for which there's no need optimizing for) is only needed if
you've moved it out of $PGDATA _after_ running pg_upgrade, IIUC. So, I think it
should be clearly pointed out that copying pg_wal is only needed in those
cases, and that it can be done with whatever network-copying procedure you're
familiar with, ie. scp/rsync. This step is not similar to the steps required
for copying tablespaces outside $PGDATA, so it's worth documenting explicitly.
Maybe also telling users to ensure the synlink (in $PGDATA) to pg_wal on
standby points to pg_wal.
 
--
Andreas Joseph Krogh
 

On Sat, Sep 16, 2017 at 06:11:17PM +0200, Andreas Joseph Krogh wrote:

I'm a little unsure what scenario we're trying to describe here. Copying the
pg_wal separately (for which there's no need optimizing for)�is only needed if
you've moved it out of $PGDATA�_after_ running pg_upgrade, IIUC. So, I think it

No. If you ran initdb with --waldir on the new primary, you will create
a symbolic link in the PGDATA directory, and a directory outside of
PGDATA for storing the WAL. When you run rsync on the new primary
PGDATA directory, you will copy the symlink in the PGDATA directory, but
it will point to probably nothing on the standby.

should be clearly pointed out that copying pg_wal is only needed in those
cases, and that it can be done with whatever network-copying procedure you're
familiar with, ie. scp/rsync. This step is not similar to the steps required
for copying tablespaces outside $PGDATA, so it's worth documenting explicitly.
Maybe also telling users to ensure the synlink (in $PGDATA) to pg_wal on
standby points to pg_wal.

Why tell them new instructions when the rsync instructions work fine?
What is the value?

--
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å lørdag 16. september 2017 kl. 18:34:51, skrev Bruce Momjian <bruce@momjian.us
<mailto:bruce@momjian.us>>:
On Sat, Sep 16, 2017 at 06:11:17PM +0200, Andreas Joseph Krogh wrote:

I'm a little unsure what scenario we're trying to describe here. Copying the
pg_wal separately (for which there's no need optimizing for) is only needed

if

you've moved it out of $PGDATA _after_ running pg_upgrade, IIUC. So, I

think it

No.  If you ran initdb with --waldir on the new primary, you will create
a symbolic link in the PGDATA directory, and a directory outside of
PGDATA for storing the WAL.  When you run rsync on the new primary
PGDATA directory, you will copy the symlink in the PGDATA directory, but
it will point to probably nothing on the standby.
 
 
The misunderstanding here comes from the fact that I used pg_upgradecluster
like this:
pg_upgradecluster --method=upgrade --link 9.6 main
 
and it didn't issue initdb with --waldir on the new cluster (because
pg_upgradecluster issues initdb for you), so pg_wal ended up in $PGDIR because
pg_upgradecluster didn't figure out the old cluster was initialized with
--xlogdir. This is why I thought i made sense mentioning that one had to move
pg_wal manually.
 
I know it's debian-stuff, but maybe it's worth mentioning pg_upgradecluster
somewhere and recommend not using it? It seems to start the new cluster
automatically and when upgrading standbys section 10 tells you not to do that.
 
 

should be clearly pointed out that copying pg_wal is only needed in those
cases, and that it can be done with whatever network-copying procedure

you're

familiar with, ie. scp/rsync. This step is not similar to the steps required
for copying tablespaces outside $PGDATA, so it's worth documenting

explicitly.

Maybe also telling users to ensure the synlink (in $PGDATA) to pg_wal on
standby points to pg_wal.

Why tell them new instructions when the rsync instructions work fine?
What is the value?
 
The rsync instructions mentioned in 10.F all address the --link scenario and
use "--delete --hard-links --size-only", and "merge 2 source-dirs into one",
which isn't relevant when copying pg_wal.
 
This sentence:
"If you have relocated pg_wal outside the data directories, rsync must be run
on those directories too."
implies one must follow the rsync pattern elsewhere in 10.F, which isn't
really true. Maybe re-wording it to:
"If you have relocated pg_wal outside the data directories you must copy it
over to the new standby, and ensure the symlink from $PGDATA points to it"
helps?

--
Andreas Joseph Krogh

On Sat, Sep 16, 2017 at 11:36:40PM +0200, Andreas Joseph Krogh wrote:

P� l�rdag 16. september 2017 kl. 18:34:51, skrev Bruce Momjian <
bruce@momjian.us>:
No.� If you ran initdb with --waldir on the new primary, you will create
a symbolic link in the PGDATA directory, and a directory outside of
PGDATA for storing the WAL.� When you run rsync on the new primary
PGDATA directory, you will copy the symlink in the PGDATA directory, but
it will point to probably nothing on the standby.

�
�
The misunderstanding here comes from the fact that I used pg_upgradecluster
like this:
pg_upgradecluster --method=upgrade --link 9.6 main
�
and it didn't issue initdb with�--waldir on the new cluster (because
pg_upgradecluster issues initdb for you), so pg_wal ended up in $PGDIR because
pg_upgradecluster didn't figure out the old cluster was initialized with
--xlogdir. This is why I thought i made sense mentioning that one had to move
pg_wal manually.
�
I know it's debian-stuff, but maybe it's worth mentioning pg_upgradecluster
somewhere and recommend not using it? It seems to start the new cluster
automatically and when upgrading standbys section 10 tells you not to do that.

So you didn't really follow the instructions, but instead are trying to
use the standby part of the instructions and found a problem with the
way pg_upgradecluster handled it. We really can't document this.

It would be good to report the bug to pg_upgradecluster developers
though.

Yes, I can see rsync not working that case.

should be clearly pointed out that copying pg_wal is only needed in those
cases, and that it can be done with whatever network-copying procedure

you're

familiar with, ie. scp/rsync. This step is not similar to the steps

required

for copying tablespaces outside $PGDATA, so it's worth documenting

explicitly.

Maybe also telling users to ensure the synlink (in $PGDATA) to pg_wal on
standby points to pg_wal.

Why tell them new instructions when the rsync instructions work fine?
What is the value?

�
The rsync instructions mentioned in 10.F all address the --link scenario and
use "--delete --hard-links --size-only", and "merge 2 source-dirs into one",
which isn't relevant when copying pg_wal.
�
This sentence:
"If you have relocated�pg_wal�outside the data directories,�rsync�must be run
on those directories too."
implies one must follow the rsync pattern elsewhere in 10.F, which isn't really
true. Maybe re-wording it to:
"If you have relocated�pg_wal�outside the data directories you must copy it
over to the new standby,�and ensure the symlink from $PGDATA points to it"
helps?

We can't document every possible configuration, especially if a
secondary tool is used in the middle.

--
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å mandag 18. september 2017 kl. 16:28:07, skrev Bruce Momjian <bruce@momjian.us
<mailto:bruce@momjian.us>>:
On Sat, Sep 16, 2017 at 11:36:40PM +0200, Andreas Joseph Krogh wrote:

På lørdag 16. september 2017 kl. 18:34:51, skrev Bruce Momjian <
bruce@momjian.us>:
     No.  If you ran initdb with --waldir on the new primary, you will create
     a symbolic link in the PGDATA directory, and a directory outside of
     PGDATA for storing the WAL.  When you run rsync on the new primary
     PGDATA directory, you will copy the symlink in the PGDATA directory, but
     it will point to probably nothing on the standby.

 
 
The misunderstanding here comes from the fact that I used pg_upgradecluster
like this:
pg_upgradecluster --method=upgrade --link 9.6 main
 
and it didn't issue initdb with --waldir on the new cluster (because
pg_upgradecluster issues initdb for you), so pg_wal ended up in $PGDIR

because

pg_upgradecluster didn't figure out the old cluster was initialized with
--xlogdir. This is why I thought i made sense mentioning that one had to

move

pg_wal manually.
 
I know it's debian-stuff, but maybe it's worth mentioning pg_upgradecluster
somewhere and recommend not using it? It seems to start the new cluster
automatically and when upgrading standbys section 10 tells you not to do

that.

So you didn't really follow the instructions, but instead are trying to
use the standby part of the instructions and found a problem with the
way pg_upgradecluster handled it.  We really can't document this.

It would be good to report the bug to pg_upgradecluster developers
though.

Yes, I can see rsync not working that case.
 
 
 
Actually I didn't know about --waldir switch of initdb and have always moved
pg_xlog manually then symlinking. 
 
 
 

     > should be clearly pointed out that copying pg_wal is only needed in

those

     > cases, and that it can be done with whatever network-copying procedure
     you're
     > familiar with, ie. scp/rsync. This step is not similar to the steps
     required
     > for copying tablespaces outside $PGDATA, so it's worth documenting
     explicitly.
     > Maybe also telling users to ensure the synlink (in $PGDATA) to pg_wal

on

     > standby points to pg_wal.

     Why tell them new instructions when the rsync instructions work fine?
     What is the value?

 
The rsync instructions mentioned in 10.F all address the --link scenario and
use "--delete --hard-links --size-only", and "merge 2 source-dirs into one",
which isn't relevant when copying pg_wal.
 
This sentence:
"If you have relocated pg_wal outside the data directories, rsync must be

run

on those directories too."
implies one must follow the rsync pattern elsewhere in 10.F, which isn't

really

true. Maybe re-wording it to:
"If you have relocated pg_wal outside the data directories you must copy it
over to the new standby, and ensure the symlink from $PGDATA points to it"
helps?

We can't document every possible configuration, especially if a
secondary tool is used in the middle.
 
 
But we're not talking about many different configurations, we're addressing
when pg_wal is located outside $PGDATA.
So it basically boils down to the last sentence in 10.F:
 
If you have relocated pg_wal outside the data directories, rsync must be run
on those directories too.
 
the word "must" here isn't correct. The point is that you have to copy the
waldir manually from the primary to the standby and ensure the symlink points
to this new location on the standby. So I still think something like this is
better: "If you have relocated pg_wal outside the data directories you must
copy it over to the new standby, and ensure the symlink from
$PGDATA/pg_wal points to it". I think it eliminates any doubt and makes the
instructions complete and easy to follow.
 
Thanks.

--
Andreas Joseph Krogh