Description of problem:Restore to global region fails due to connection to database Version-Release number of selected component (if applicable):5.8.0.11 How reproducible:100% Steps to Reproduce: 1.provision 2 appliances 2.setup with pglogical replication 3.create backup on global region (pg_dump) 4.stop evmserverd on both apps 5.run appliance_console on global 6.select option 4 restore Actual results:Restore Database From Backup Note: A database restore cannot be undone. The restore will use the file: /tmp/evm_db.backup. Are you sure you would like to restore the database? (Y/N): y Restoring the database... Database restore failed There are 1 connection preventing a database restore Press any key to continue. Expected results:Restore should complete successfully Additional info: Currently we can run restore through appliance console and it should drop the pglogical subscriptions preventing the restore method from succeeding. I believe this part is happening correctly as after the restore fails if you start evm and check the subscriptions in the ui they have all gone. I have also tried to dropping the subscriptions before running the restore on the global region with the same results. If you stop and start postgres it will close the connection so you can manually drop/create the db but by this time the connection is active again stopping a restore still.
https://github.com/ManageIQ/manageiq-gems-pending/pull/140
New commit detected on ManageIQ/manageiq-gems-pending/master: https://github.com/ManageIQ/manageiq-gems-pending/commit/8de289716f96f6bcd9c09967b4e72eb39debb7e4 commit 8de289716f96f6bcd9c09967b4e72eb39debb7e4 Author: Nick Carboni <ncarboni> AuthorDate: Tue Apr 25 17:48:30 2017 -0400 Commit: Nick Carboni <ncarboni> CommitDate: Tue Apr 25 17:51:34 2017 -0400 Remove the pglogical extension before attempting a database restore In newer versions of pglogical the manager process continues running even if there are no subscriptions on a node. This process holds open a connection that prevents us from dropping the database when we want to run a restore. This change removes that connection by completely removing the extension before the restore runs. https://bugzilla.redhat.com/show_bug.cgi?id=1444993 lib/gems/pending/util/postgres_admin.rb | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-)
New commit detected on ManageIQ/manageiq-gems-pending/master: https://github.com/ManageIQ/manageiq-gems-pending/commit/6f17bbf10fa1e92e487c8e7c8d08915144a471da commit 6f17bbf10fa1e92e487c8e7c8d08915144a471da Author: Gregg Tanzillo <gtanzill> AuthorDate: Wed May 3 14:46:44 2017 -0400 Commit: Gregg Tanzillo <gtanzill> CommitDate: Thu May 4 12:01:02 2017 -0400 Fix typo dropping pglogical extension https://bugzilla.redhat.com/show_bug.cgi?id=1445803 https://bugzilla.redhat.com/show_bug.cgi?id=1444993 lib/gems/pending/util/postgres_admin.rb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
New commit detected on ManageIQ/manageiq-gems-pending/master: https://github.com/ManageIQ/manageiq-gems-pending/commit/cdb3b1355349387cc0b41fe54477d4e620666199 commit cdb3b1355349387cc0b41fe54477d4e620666199 Author: Gregg Tanzillo <gtanzill> AuthorDate: Wed May 3 14:52:22 2017 -0400 Commit: Gregg Tanzillo <gtanzill> CommitDate: Thu May 4 12:01:25 2017 -0400 Wait for "pglogical manager" db connection to go away before allowing pg_restore to proceed https://bugzilla.redhat.com/show_bug.cgi?id=1445803 https://bugzilla.redhat.com/show_bug.cgi?id=1444993 lib/gems/pending/util/postgres_admin.rb | 15 +++++++++++++++ 1 file changed, 15 insertions(+)
New commit detected on ManageIQ/manageiq-gems-pending/master: https://github.com/ManageIQ/manageiq-gems-pending/commit/d8263c8d1ec86fffefb9ddf4397ff1793b29848b commit d8263c8d1ec86fffefb9ddf4397ff1793b29848b Author: Gregg Tanzillo <gtanzill> AuthorDate: Wed May 3 14:51:00 2017 -0400 Commit: Gregg Tanzillo <gtanzill> CommitDate: Thu May 4 12:01:20 2017 -0400 Move logic for dropping pglogical extension to a separate method https://bugzilla.redhat.com/show_bug.cgi?id=1445803 https://bugzilla.redhat.com/show_bug.cgi?id=1444993 lib/gems/pending/util/postgres_admin.rb | 10 ++++++++-- 1 file changed, 8 insertions(+), 2 deletions(-)
Verified in 5.9.0.2
New commit detected on ManageIQ/manageiq-appliance_console/master: https://github.com/ManageIQ/manageiq-appliance_console/commit/af6774277fd2e29cb71a37bbc57ca6997a7a9d87 commit af6774277fd2e29cb71a37bbc57ca6997a7a9d87 Author: Nick Carboni <ncarboni> AuthorDate: Tue Apr 25 21:48:30 2017 +0000 Commit: Nick LaMuro <nicklamuro> CommitDate: Tue Mar 16 19:25:16 2021 +0000 Remove the pglogical extension before attempting a database restore In newer versions of pglogical the manager process continues running even if there are no subscriptions on a node. This process holds open a connection that prevents us from dropping the database when we want to run a restore. This change removes that connection by completely removing the extension before the restore runs. https://bugzilla.redhat.com/show_bug.cgi?id=1444993 (transferred from ManageIQ/manageiq-gems-pending@8de289716f96f6bcd9c09967b4e72eb39debb7e4) lib/manageiq/appliance_console/postgres_admin.rb | 5 +- 1 file changed, 4 insertions(+), 1 deletion(-) https://github.com/ManageIQ/manageiq-appliance_console/commit/98701fbc84181a52c643428cf102f4480739a2f1 commit 98701fbc84181a52c643428cf102f4480739a2f1 Author: Gregg Tanzillo <gtanzill> AuthorDate: Wed May 3 18:46:44 2017 +0000 Commit: Nick LaMuro <nicklamuro> CommitDate: Tue Mar 16 19:25:16 2021 +0000 Fix typo dropping pglogical extension https://bugzilla.redhat.com/show_bug.cgi?id=1445803 https://bugzilla.redhat.com/show_bug.cgi?id=1444993 (transferred from ManageIQ/manageiq-gems-pending@6f17bbf10fa1e92e487c8e7c8d08915144a471da) lib/manageiq/appliance_console/postgres_admin.rb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) https://github.com/ManageIQ/manageiq-appliance_console/commit/ef6e1a378d9fd8bab438434bfcae5ab73376391c commit ef6e1a378d9fd8bab438434bfcae5ab73376391c Author: Gregg Tanzillo <gtanzill> AuthorDate: Wed May 3 18:51:00 2017 +0000 Commit: Nick LaMuro <nicklamuro> CommitDate: Tue Mar 16 19:25:16 2021 +0000 Move logic for dropping pglogical extension to a separate method https://bugzilla.redhat.com/show_bug.cgi?id=1445803 https://bugzilla.redhat.com/show_bug.cgi?id=1444993 (transferred from ManageIQ/manageiq-gems-pending@d8263c8d1ec86fffefb9ddf4397ff1793b29848b) lib/manageiq/appliance_console/postgres_admin.rb | 10 +- 1 file changed, 8 insertions(+), 2 deletions(-) https://github.com/ManageIQ/manageiq-appliance_console/commit/1d7e9243af30bfba1f7a469491287af831934abb commit 1d7e9243af30bfba1f7a469491287af831934abb Author: Gregg Tanzillo <gtanzill> AuthorDate: Wed May 3 18:52:22 2017 +0000 Commit: Nick LaMuro <nicklamuro> CommitDate: Tue Mar 16 19:25:16 2021 +0000 Wait for "pglogical manager" db connection to go away before allowing pg_restore to proceed https://bugzilla.redhat.com/show_bug.cgi?id=1445803 https://bugzilla.redhat.com/show_bug.cgi?id=1444993 (transferred from ManageIQ/manageiq-gems-pending@cdb3b1355349387cc0b41fe54477d4e620666199) lib/manageiq/appliance_console/postgres_admin.rb | 15 + 1 file changed, 15 insertions(+)