Bug 1444993

Summary:	Restore to global region fails due to connection to database
Product:	Red Hat CloudForms Management Engine	Reporter:	luke couzens <lcouzens>
Component:	Appliance	Assignee:	Nick Carboni <ncarboni>
Status:	CLOSED CURRENTRELEASE	QA Contact:	luke couzens <lcouzens>
Severity:	high	Docs Contact:
Priority:	high
Version:	5.8.0	CC:	abellott, dajohnso, jhardy, ncarboni, obarenbo
Target Milestone:	GA	Keywords:	Regression, TestOnly
Target Release:	5.9.0
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:	black:replication
Fixed In Version:	5.9.0.1	Doc Type:	If docs needed, set a value
Doc Text:		Story Points:	---
Clone Of:
Clones:	1445803 (view as bug list)		Environment:
Last Closed:	2018-03-06 14:49:11 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	CFME Core	Target Upstream Version:
Embargoed:
Bug Depends On:
Bug Blocks:	1445803

Description luke couzens 2017-04-24 17:15:33 UTC

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.

Comment 2 CFME Bot 2017-04-25 22:03:29 UTC

https://github.com/ManageIQ/manageiq-gems-pending/pull/140

Comment 3 CFME Bot 2017-04-26 12:37:53 UTC

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(-)

Comment 5 CFME Bot 2017-05-04 19:44:42 UTC

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(-)

Comment 6 CFME Bot 2017-05-04 19:44:54 UTC

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(+)

Comment 7 CFME Bot 2017-05-04 19:45:04 UTC

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(-)

Comment 8 luke couzens 2017-10-12 15:37:03 UTC

Verified in 5.9.0.2

Comment 9 CFME Bot 2021-03-17 16:36:04 UTC

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(+)