Description of problem:postgres upgrade script fails for 5.6 to 5.7 replicated setup Version-Release number of selected component (if applicable):5.7.0.9 How reproducible:100% Steps to Reproduce: 1.setup 5.6 appliances with pglogical replication 2.upgrade to 5.7 using repo's 3.logout/in to reload ruby 4.run vmdb 5.run rake db:migrate 6.run rake evm:automate:reset 7.run /usr/bin/miq_postgres_upgrade.sh Actual results:script fails to run correctly Expected results:script completes upgrading postgres to 9.5 Additional info: pastebin of script error: http://pastebin.test.redhat.com/426643
This is happening because 'pglogical' is not present in the postgres.conf file after we run initdb. Then during the processing of pg_upgrade, when we are importing the schema into the new database (9.5) we fail because we can't find the pglogical libs. We can't just move the file over between initdb and pg_upgrade because we have some settings which have been removed in postgres 9.5 so the server will not start up at all. I think the best fix is to amend the preload_shared_libraries value in the default file created by initdb to include pglogical then move the existing files over after the upgrade.
https://github.com/ManageIQ/manageiq-appliance/pull/98
New commit detected on ManageIQ/manageiq-appliance/master: https://github.com/ManageIQ/manageiq-appliance/commit/ddea5bd7539be96cc75a05df1d3d554b99134f9b commit ddea5bd7539be96cc75a05df1d3d554b99134f9b Author: Nick Carboni <ncarboni> AuthorDate: Tue Nov 8 11:26:08 2016 -0500 Commit: Nick Carboni <ncarboni> CommitDate: Tue Nov 8 11:26:08 2016 -0500 Add pglogical to the shared preload libraries before upgrade Not having this was causing us to fail when trying to enable the extension during the upgrade (because the extension was enabled in the previous database) It is safe to assume the structure of the default postgres.conf and change it as we need to because we will overwrite it later with the user's postgres.conf anyway. https://bugzilla.redhat.com/show_bug.cgi?id=1391693 LINK/usr/bin/miq_postgres_upgrade.sh | 2 ++ 1 file changed, 2 insertions(+)
New commit detected on ManageIQ/manageiq-appliance/master: https://github.com/ManageIQ/manageiq-appliance/commit/d3506dd4ef2c4e12f54b54e840d619df47c5f18d commit d3506dd4ef2c4e12f54b54e840d619df47c5f18d Author: Nick Carboni <ncarboni> AuthorDate: Tue Nov 8 17:13:08 2016 -0500 Commit: Nick Carboni <ncarboni> CommitDate: Tue Nov 8 17:13:08 2016 -0500 Record then recreate logical replication metadata across the upgrade Replication slots are not persisted across pg_upgrade invocations. I'm not sure for the reason for this, but parts may be because the new, upgraded, database is create first by initdb and then later populated with the upgraded data or because the slot state is stored on the filesystem. Either way, we need to have the slot in place after upgrade for pglogical to work properly. Additionally PostgreSQL 9.5 introduced a concept called replication origins (https://www.postgresql.org/docs/9.5/static/replication-origins.html) which allow for replication progress tracking from the consumer. For pglogical subscriptions to work with 9.5 we need to also create a replication origin record using the subscription's slot name. https://bugzilla.redhat.com/show_bug.cgi?id=1391693 LINK/usr/bin/miq_postgres_upgrade.sh | 35 +++++++++++++++++++++++++++++++++++ 1 file changed, 35 insertions(+)
Hello, I´m also hitting this problem in 5.7.0.17... is there any workaround to solve this?
Verified in 5.8.0.3