pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUpgrade streaming replication and log-shipping standby servers
6+ messages / 3 participants
[nested] [flat]
* Upgrade streaming replication and log-shipping standby servers
@ 2020-06-15 10:46 Victor Sudakov <vas@sibptus.ru>
0 siblings, 1 reply; 6+ messages in thread
From: Victor Sudakov @ 2020-06-15 10:46 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Dear Colleagues,
When upgrading to a new major version, pg_upgrade documentation
https://www.postgresql.org/docs/current/pgupgrade.html#PGUPGRADE-STEP-REPLICAS
states that pg_upgrade can be used on a master server. After upgrading
the PostgreSQL version on standbys, however, one must either pull the
data from scratch with pg_basebackup, or run rsync from master to
standby.
Is running pg_upgrade on replicas not supported and why?
--
Victor Sudakov, VAS4-RIPE, VAS47-RIPN
2:5005/49@fidonet http://vas.tomsk.ru/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Upgrade streaming replication and log-shipping standby servers
@ 2020-06-16 11:48 Greg Spiegelberg <gspiegelberg@gmail.com>
parent: Victor Sudakov <vas@sibptus.ru>
0 siblings, 2 replies; 6+ messages in thread
From: Greg Spiegelberg @ 2020-06-16 11:48 UTC (permalink / raw)
To: Victor Sudakov <vas@sibptus.ru>; +Cc: pgsql-admin@lists.postgresql.org
On Mon, Jun 15, 2020 at 4:46 AM Victor Sudakov <vas@sibptus.ru> wrote:
> Dear Colleagues,
>
> When upgrading to a new major version, pg_upgrade documentation
>
> https://www.postgresql.org/docs/current/pgupgrade.html#PGUPGRADE-STEP-REPLICAS
> states that pg_upgrade can be used on a master server. After upgrading
> the PostgreSQL version on standbys, however, one must either pull the
> data from scratch with pg_basebackup, or run rsync from master to
> standby.
>
> Is running pg_upgrade on replicas not supported and why?
>
>
Hi Victor,
I agree it would be extraordinarily convenient if pg_upgrade could operate
on standbys.
Via experience and a quick code read of pg_upgrade source confirms that the
new, initialized and as yet unmodified cluster is modified in preparation
for an upgrade. Some of those new cluster modifications include execution
of analyze, freezing rows, resetting WAL, modification of
controldata(?) and restoration of the old schema. These and others are
evident running the utility in verbose mode. Setting aside a standby must
be promoted for these modifications, there is no current way to
guarantee these modifications will be consistently done on both primary and
all standbys. A standby inconsistent with the primary is no standby at all.
Perhaps not the right place but I would argue that IF pg_upgrade could
1) pause after all these new cluster modifications at or soon after
"Restoring database schemas in the new cluster",
2) permit standbys to replicate the new cluster,
3) continue on primary, and
4) run on standbys picking up from where it left off at step 2 perhaps in
coordination with primary
then it would be very nice but I'm making assumptions and painting with a
wide brush. :)
-Greg
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Upgrade streaming replication and log-shipping standby servers
@ 2020-06-16 14:50 Victor Sudakov <vas@sibptus.ru>
parent: Greg Spiegelberg <gspiegelberg@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Victor Sudakov @ 2020-06-16 14:50 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Greg Spiegelberg wrote:
>
> >
> > When upgrading to a new major version, pg_upgrade documentation
> >
> > https://www.postgresql.org/docs/current/pgupgrade.html#PGUPGRADE-STEP-REPLICAS
> > states that pg_upgrade can be used on a master server. After upgrading
> > the PostgreSQL version on standbys, however, one must either pull the
> > data from scratch with pg_basebackup, or run rsync from master to
> > standby.
> >
> > Is running pg_upgrade on replicas not supported and why?
> >
> >
> Hi Victor,
>
> I agree it would be extraordinarily convenient if pg_upgrade could operate
> on standbys.
>
> Via experience and a quick code read of pg_upgrade source confirms that the
> new, initialized and as yet unmodified cluster is modified in preparation
> for an upgrade. Some of those new cluster modifications include execution
> of analyze, freezing rows, resetting WAL, modification of
> controldata(?) and restoration of the old schema. These and others are
> evident running the utility in verbose mode. Setting aside a standby must
> be promoted for these modifications, there is no current way to
> guarantee these modifications will be consistently done on both primary and
> all standbys. A standby inconsistent with the primary is no standby at all.
Hi Greg,
Thank you for the detailed explanation.
>
> Perhaps not the right place but I would argue that IF pg_upgrade could
> 1) pause after all these new cluster modifications at or soon after
> "Restoring database schemas in the new cluster",
> 2) permit standbys to replicate the new cluster,
> 3) continue on primary, and
> 4) run on standbys picking up from where it left off at step 2 perhaps in
> coordination with primary
> then it would be very nice but I'm making assumptions and painting with a
> wide brush. :)
>
Sounds very tricky.
--
Victor Sudakov, VAS4-RIPE, VAS47-RIPN
2:5005/49@fidonet http://vas.tomsk.ru/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Upgrade streaming replication and log-shipping standby servers
@ 2020-06-16 17:00 Bruce Momjian <bruce@momjian.us>
parent: Greg Spiegelberg <gspiegelberg@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Bruce Momjian @ 2020-06-16 17:00 UTC (permalink / raw)
To: Greg Spiegelberg <gspiegelberg@gmail.com>; +Cc: Victor Sudakov <vas@sibptus.ru>; pgsql-admin@lists.postgresql.org
On Tue, Jun 16, 2020 at 05:48:10AM -0600, Greg Spiegelberg wrote:
> On Mon, Jun 15, 2020 at 4:46 AM Victor Sudakov <vas@sibptus.ru> wrote:
>
> Dear Colleagues,
>
> When upgrading to a new major version, pg_upgrade documentation
> https://www.postgresql.org/docs/current/pgupgrade.html#
> PGUPGRADE-STEP-REPLICAS
> states that pg_upgrade can be used on a master server. After upgrading
> the PostgreSQL version on standbys, however, one must either pull the
> data from scratch with pg_basebackup, or run rsync from master to
> standby.
>
> Is running pg_upgrade on replicas not supported and why?
>
>
>
> Hi Victor,
>
> I agree it would be extraordinarily convenient if pg_upgrade could operate on
> standbys.
Uh, pg_upgrade is already documented as being able to upgrade standby
servers:
https://www.postgresql.org/docs/12/pgupgrade.html
8. Prepare for standby server upgrades
Am I missing something? The sync is not sync'ing the file contents,
just the hard links. You can see the details in the docs:
f. Run rsync
When using link mode, standby servers can be quickly upgraded using
rsync. To accomplish this, from a directory on the primary server that
is above the old and new database cluster directories, run this on the
primary for each standby server:
rsync --archive --delete --hard-links --size-only --no-inc-recursive
old_cluster new_cluster remote_dir
where old_cluster and new_cluster are relative to the current directory
on the primary, and remote_dir is above the old and new cluster
directories on the standby. The directory structure under the specified
directories on the primary and standbys must match. Consult the rsync
manual page for details on specifying the remote directory, e.g.
rsync --archive --delete --hard-links --size-only --no-inc-recursive
/opt/PostgreSQL/9.5 \
/opt/PostgreSQL/9.6 standby.example.com:/opt/PostgreSQL
You can verify what the command will do using rsync's --dry-run option.
While rsync must be run on the primary for at least one standby, it is
possible to run rsync on an upgraded standby to upgrade other standbys,
as long as the upgraded standby has not been started.
What this does is to record the links created by pg_upgrade's link mode
that connect files in the old and new clusters on the primary server. It
then finds matching files in the standby's old cluster and creates links
for them in the standby's new cluster. Files that were not linked on the
primary are copied from the primary to the standby. (They are usually
small.) This provides rapid standby upgrades. Unfortunately, rsync
needlessly copies files associated with temporary and unlogged tables
because these files don't normally exist on standby servers.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Upgrade streaming replication and log-shipping standby servers
@ 2020-06-16 17:28 Greg Spiegelberg <gspiegelberg@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 6+ messages in thread
From: Greg Spiegelberg @ 2020-06-16 17:28 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Victor Sudakov <vas@sibptus.ru>; pgsql-admin@lists.postgresql.org
On Tue, Jun 16, 2020 at 11:00 AM Bruce Momjian <bruce@momjian.us> wrote:
>
> Uh, pg_upgrade is already documented as being able to upgrade standby
> servers:
>
> https://www.postgresql.org/docs/12/pgupgrade.html
>
>
I stand corrected. Next time I shall RT-Whole-FM.
Thanks Bruce.
-Greg
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Upgrade streaming replication and log-shipping standby servers
@ 2020-06-16 17:51 Bruce Momjian <bruce@momjian.us>
parent: Greg Spiegelberg <gspiegelberg@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2020-06-16 17:51 UTC (permalink / raw)
To: Greg Spiegelberg <gspiegelberg@gmail.com>; +Cc: Victor Sudakov <vas@sibptus.ru>; pgsql-admin@lists.postgresql.org
On Tue, Jun 16, 2020 at 11:28:27AM -0600, Greg Spiegelberg wrote:
> On Tue, Jun 16, 2020 at 11:00 AM Bruce Momjian <bruce@momjian.us> wrote:
>
>
> Uh, pg_upgrade is already documented as being able to upgrade standby
> servers:
>
> https://www.postgresql.org/docs/12/pgupgrade.html
>
>
>
> I stand corrected. Next time I shall RT-Whole-FM.
It is a long, complex set of instructions, and people who try to
short-circuit it can end up not using pg_upgrade to its full potential.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-06-16 17:51 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-15 10:46 Upgrade streaming replication and log-shipping standby servers Victor Sudakov <vas@sibptus.ru>
2020-06-16 11:48 ` Greg Spiegelberg <gspiegelberg@gmail.com>
2020-06-16 14:50 ` Victor Sudakov <vas@sibptus.ru>
2020-06-16 17:00 ` Bruce Momjian <bruce@momjian.us>
2020-06-16 17:28 ` Greg Spiegelberg <gspiegelberg@gmail.com>
2020-06-16 17:51 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox