On Mon, Jun 12, 2017 at 06:31:11PM +0300, Vladimir Borodin wrote: > What about the following sequence? > > 1. Run pg_upgrade on master, > 2. Start it in single-user mode and stop (to get right wal_level in > pg_control), > 3. Copy pg_control somewhere, > 4. Start master, run analyze and stop. > 5. Put the control file from step 3 to replicas and rsync them according to > the > documentation. > > And I think that step 10.f in the documentation [1] should be fixed to mention > starting in single-user mode or with disabled autovacuum. > > [1] https://www.postgresql.org/docs/devel/static/pgupgrade.html
First, I want to apologize for not getting involved in this thread earlier, and I want to thank everyone for the huge amount of detective work in finding the cause of this bug. Let me see if I can replay how the standby server upgrade instructions evolved over time. Initially we knew that we had to set wal_level to replica+ so that when you reconnect to the standby servers, the WAL would have the right contents. (We are basically simulating pg_start/stop backup with rsync.) There was a desire to have those instructions inside a documentation block dedicated to standby server upgrades, so the wal_level adjustment and new server start/stop was added to that block. I assumed a start/stop could not modify the WAL, or at least nothing important would happen, but obviously I was wrong. (pg_upgrade takes steps to ensure that nothing happens.) Adding ANALYZE in there just made it worse, but the problem always existed. I sure hope others haven't had a problem with this. Now, it seems we later added a doc section early on that talks about "Verify standby servers" so I have moved the wal_level section into that block, which should be safe. There is now no need to start/stop the new server since pg_upgrade will do that safely already. I plan to patch this back to 9.5 where these instructions were added. I will mention that this should be in the minor release notes. -- Bruce Momjian <br...@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 +
diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml new file mode 100644 index bf58a0a..18e6af3 *** a/doc/src/sgml/ref/pgupgrade.sgml --- b/doc/src/sgml/ref/pgupgrade.sgml *************** NET STOP postgresql-9.0 *** 317,331 **** </step> <step> ! <title>Verify standby servers</title> <para> ! If you are upgrading Streaming Replication and Log-Shipping standby ! servers, verify that the old standby servers are caught up by running ! <application>pg_controldata</> against the old primary and standby ! clusters. Verify that the <quote>Latest checkpoint location</> ! values match in all clusters. (There will be a mismatch if old ! standby servers were shut down before the old primary.) </para> </step> --- 317,338 ---- </step> <step> ! <title>Prepare for standby server upgrades</title> <para> ! If you are upgrading standby servers (as outlined in section <xref ! linkend="pgupgrade-step-replicas">), verify that the old standby ! servers are caught up by running <application>pg_controldata</> ! against the old primary and standby clusters. Verify that the ! <quote>Latest checkpoint location</> values match in all clusters. ! (There will be a mismatch if old standby servers were shut down ! before the old primary.) ! </para> ! ! <para> ! Also, if upgrading standby servers, change <varname>wal_level</> ! to <literal>replica</> in the <filename>postgresql.conf</> file on ! the new cluster. </para> </step> *************** pg_upgrade.exe *** 410,416 **** </para> </step> ! <step> <title>Upgrade Streaming Replication and Log-Shipping standby servers</title> <para> --- 417,423 ---- </para> </step> ! <step id="pgupgrade-step-replicas"> <title>Upgrade Streaming Replication and Log-Shipping standby servers</title> <para> *************** pg_upgrade.exe *** 471,486 **** </para> </step> - <step> - <title>Start and stop the new master cluster</title> - - <para> - In the new master cluster, change <varname>wal_level</> to - <literal>replica</> in the <filename>postgresql.conf</> file - and then start and stop the cluster. - </para> - </step> - <step> <title>Run <application>rsync</></title> --- 478,483 ----
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers