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
