On 2/5/18 19:09, David Fetter wrote:
> - Add a new script--possibly Perl or Bash, which would:
> - Initdb a new cluster with the new version of PostgreSQL and a
> different port.
This will need integration with the packaging system. You'll want to
carry over settings from the old instance. You might want to put the
new instance on a different host.
> - Start logical replication from the old version to the new
> version.
There is a step missing that does the DDL sync. And various features
are not handled by logical replication. So you'll need a pre-check mode
like pg_upgrade.
Also, you need to do this for each database, so you'll need to decide
whether you'll do it all in parallel or sequentially, where you will
continue when it fails part way through, etc.
> - Poll until a pre-determined default amount of replication lag was observed, then:
Probably the replication lag should be zero, or perhaps you'll even want
to switch to synchronous replication. Or perhaps you'll switch the
writing node while replication is still catching up. A lot of that
depends on the application.
> * Issue an ALTER SYSTEM on the new server to change its port to the old server's
Or you use a connection proxy and have that handle redirecting the
traffic, so you don't need to restart anything.
> * Issue a pg_ctl stop -w to the old server
> * Issue a pg_ctl restart on the new server
You can't use pg_ctl when using systemd.
> Does this seem worth coding up in its current form?
Logically, this should be a ten line script. But I fear making it
actually work in a variety of practical scenarios will probably require
dozens of options and end up very complicated.
At this point, it might be worth more to actually try this procedure by
hand first and work out the details, e.g., how do you do the DDL sync,
how to you convert the configuration files, etc.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services