Bug 989477

Summary:	[TEXT] restore.sh woes, unable to restore engine DB without using '-u postgres'
Product:	[Red Hat Storage] Red Hat Gluster Storage	Reporter:	Sahina Bose <sabose>
Component:	rhsc	Assignee:	Aravinda VK <avishwan>
Status:	CLOSED WONTFIX	QA Contact:	Sudhir D <sdharane>
Severity:	medium	Docs Contact:
Priority:	medium
Version:	2.1	CC:	acathrow, dpati, iheim, jbelka, jkt, lpeer, mgoldboi, Rhev-m-bugs, rhs-bugs, vagarwal, yeylon
Target Milestone:	---	Keywords:	ZStream
Target Release:	RHGS 2.1.2
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:	infra
Fixed In Version:		Doc Type:	Known Issue
Doc Text:	Cause: restore.sh fails when run with a user other than "postgres" Consequence: Can run restore.sh only with option -u postgres Workaround (if any): Result:	Story Points:	---
Clone Of:	976337	Environment:
Last Closed:	2013-11-25 09:55:42 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:	976337
Bug Blocks:	957769

Description Sahina Bose 2013-07-29 10:43:46 UTC

+++ This bug was initially created as a clone of Bug #976337 +++

Description of problem:
restore.sh woes, unable to restore engine DB without '-u postgres'.

the whole way how it works looks funny to me, see below:

~~~

* when 'engine' db does not exist (after rhevm-cleanup)

obviously! that's the reason why i'm doing restore, to get the DB!

# /usr/share/ovirt-engine/dbscripts/restore.sh -d engine -f /var/lib/ovirt-engine/backups/*sf17_5.sql
/usr/share/ovirt-engine/dbscripts /
Restore of database engine from /var/lib/ovirt-engine/backups/ovirt-engine_db_backup_2013_06_17_09_56_54-sf17_5.sql started...
psql: FATAL:  database "engine" does not exist
Usage: restore.sh [-h] [-s SERVERNAME] [-p PORT] -u USERNAME -d DATABASE -f FILE [-r] [-o] 

        -s SERVERNAME - The database servername for the database (def. localhost)
        -p PORT       - The database port for the database       (def. 5432)
        -u USERNAME   - The username for the database            (def. engine)
        -d DATABASE   - The database name, this must match the db name recorded in the backup file.
        -f File       - Backup file name to restore from. /var/lib/ovirt-engine/backups/ovirt-engine_db_backup_2013_06_17_09_56_54-sf17_5.sql
        -r            - Remove existing database with same name
        -o            - Omit upgrade step
        -h            - This help text.

for more options please run pg_restore --help

The recommended way for restoring your database is.
        1) Backup current database with backup.sh
        2) Run restore.sh and give new database instance name as the target
        3) Edit JBOSS standalone.xml to run the new restored database instance
        4) Verify that all tasks in the application are completed
        5) Restart JBOSS


* when 'engine' db does exists with '-r'

# /usr/share/ovirt-engine/dbscripts/restore.sh -d engine -f /var/lib/ovirt-engine/backups/*sf17_5.sql -r
/usr/share/ovirt-engine/dbscripts /
Restore of database engine from /var/lib/ovirt-engine/backups/ovirt-engine_db_backup_2013_06_17_09_56_54-sf17_5.sql started...
psql: FATAL:  database "engine" does not exist
Usage: restore.sh [-h] [-s SERVERNAME] [-p PORT] -u USERNAME -d DATABASE -f FILE [-r] [-o] 

        -s SERVERNAME - The database servername for the database (def. localhost)
        -p PORT       - The database port for the database       (def. 5432)
        -u USERNAME   - The username for the database            (def. engine)
        -d DATABASE   - The database name, this must match the db name recorded in the backup file.
        -f File       - Backup file name to restore from. /var/lib/ovirt-engine/backups/ovirt-engine_db_backup_2013_06_17_09_56_54-sf17_5.sql
        -r            - Remove existing database with same name
        -o            - Omit upgrade step
        -h            - This help text.

for more options please run pg_restore --help

The recommended way for restoring your database is.
        1) Backup current database with backup.sh
        2) Run restore.sh and give new database instance name as the target
        3) Edit JBOSS standalone.xml to run the new restored database instance
        4) Verify that all tasks in the application are completed
        5) Restart JBOSS


* when 'engine' db does exists (i manually create the DB)

# /usr/share/ovirt-engine/dbscripts/restore.sh -d engine -f /var/lib/ovirt-engine/backups/*sf17_5.sql
/usr/share/ovirt-engine/dbscripts /
Database engine exists, please use -r to force removing it.


* when 'engine' db does exists with '-r' (i manually create the DB)

# /usr/share/ovirt-engine/dbscripts/restore.sh -d engine -f /var/lib/ovirt-engine/backups/*sf17_5.sql -r
/usr/share/ovirt-engine/dbscripts /
Restore of database engine from /var/lib/ovirt-engine/backups/ovirt-engine_db_backup_2013_06_17_09_56_54-sf17_5.sql started...
psql: FATAL:  database "engine" does not exist
Usage: restore.sh [-h] [-s SERVERNAME] [-p PORT] -u USERNAME -d DATABASE -f FILE [-r] [-o] 

        -s SERVERNAME - The database servername for the database (def. localhost)
        -p PORT       - The database port for the database       (def. 5432)
        -u USERNAME   - The username for the database            (def. engine)
        -d DATABASE   - The database name, this must match the db name recorded in the backup file.
        -f File       - Backup file name to restore from. /var/lib/ovirt-engine/backups/ovirt-engine_db_backup_2013_06_17_09_56_54-sf17_5.sql
        -r            - Remove existing database with same name
        -o            - Omit upgrade step
        -h            - This help text.

for more options please run pg_restore --help

The recommended way for restoring your database is.
        1) Backup current database with backup.sh
        2) Run restore.sh and give new database instance name as the target
        3) Edit JBOSS standalone.xml to run the new restored database instance
        4) Verify that all tasks in the application are completed
        5) Restart JBOSS


* finally this works (see '-u postgres')

/usr/share/ovirt-engine/dbscripts/restore.sh -d engine -f /var/lib/ovirt-engine/backups/*sf17_5.sql -r -u postgres

~~~

And... again this scripts make files in /usr/share/ovirt-engine/dbscripts... (See BZ947344)

# find /usr/share/ovirt-engine/dbscripts -type f -mtime 0 -ls
   334    4 -rw-r--r--   1 root     root         3258 Jun 20 13:52 /usr/share/ovirt-engine/dbscripts/restore.sh.log
    84   12 -rw-r--r--   1 root     root        10244 Jun 20 13:52 /usr/share/ovirt-engine/dbscripts/upgrade.sh.log

Version-Release number of selected component (if applicable):
sf17.5

How reproducible:
100%

Steps to Reproduce:
1. rhevm-cleanup
2. put back files from backup (/etc/ovirt-engine/.pgpass)

   cat > /etc/ovirt-engine/.gpass << _EOF
   localhost:5432:*:postgres:somecoolpass
   localhost:5432:*:engine:somecoolpass
   _EOF
3. try restore.sh without using '-u postgres', see above

Actual results:
default username for database looks useless, i have to use '-u postgres' to be able to restore the DB

Expected results:
either state you MUST be postgresql admin (change --help output) or correct the script

Additional info:

* why do i need to have 'template0' db? what about if i nuke it and i want to restore 'engine' db? am i lost? wouldn't be better to have db template schema in a file and during restore dump it into postgres, then do restore of the 'engine' db, and then drop this template db?

--- Additional comment from Jiri Belka on 2013-06-20 08:53:34 EDT ---

if 'template0' is really from postgresql itself, then ignore my comment about this :-)

--- Additional comment from Alex Lourie on 2013-06-24 09:48:55 EDT ---

Jiri

If you look at script's usage, you can see that -U is a required, not optional parameter. We cannot create an object in the DB server (DB for instance) if we don't have a user to connect with.

About having to be postgresql admin - that is incorrect, all you need is a user with 'createdb' privilege (engine, for instance).

Template0 and template1 are indeed really importand DBs in postgres, please do not nuke them :-)

Let me know if this is still a relevant bug.

--- Additional comment from Jiri Belka on 2013-06-25 10:00:31 EDT ---

Feel free to close the bug as I missed '-u' is mandatory. But I still doubt it is super user friendly ;)

Why this cannot work like some other web apps? You already have database and you have grants to it and then you just populate data? This does not work.

~~~
[root@ps-rh6 ~]# createdb -E UTF8 -l en_US.UTF-8 -O engine -T template0 -U postgres -W engine
Password: 
[root@ps-rh6 ~]# psql -U engine
Password for user engine: 
psql (8.4.13)
Type "help" for help.

engine=# \q

[root@ps-rh6 ~]# /usr/share/ovirt-engine/dbscripts/restore.sh -u engine -d engine -f /root/engine_Tue_Jun_25_15\:33\:49_CEST_2013.sql
Database engine exists, please use -r to force removing it.
[root@ps-rh6 ~]# /usr/share/ovirt-engine/dbscripts/restore.sh -u engine -d engine -f /root/engine_Tue_Jun_25_15\:33\:49_CEST_2013.sql -r
Restore of database engine from /root/engine_Tue_Jun_25_15:33:49_CEST_2013.sql started...
psql: FATAL:  database "engine" does not exist
Usage: restore.sh [-h] [-s SERVERNAME] [-p PORT] -u USERNAME -d DATABASE -f FILE [-r] [-o] 

        -s SERVERNAME - The database servername for the database (def. localhost)
        -p PORT       - The database port for the database       (def. 5432)
        -u USERNAME   - The username for the database            (def. engine)
        -d DATABASE   - The database name, this must match the db name recorded in the backup file.
        -f File       - Backup file name to restore from. /root/engine_Tue_Jun_25_15:33:49_CEST_2013.sql
        -r            - Remove existing database with same name
        -o            - Omit upgrade step
        -h            - This help text.

for more options please run pg_restore --help

The recommended way for restoring your database is.
        1) Backup current database with backup.sh
        2) Drop existing DB with dropdb or use the -r flag.
        3) Create a new blank db with the same name with createdb.
        4) Run restore.sh and give new database instance name as the target
~~~

Another try... 'engine' user has 'colcreatedb'.

~~~
postgres=# select * from pg_roles where rolname = 'engine';
-[ RECORD 1 ]-+---------
rolname       | engine
rolsuper      | t
rolinherit    | t
rolcreaterole | f
rolcreatedb   | t
rolcatupdate  | t
rolcanlogin   | t
rolconnlimit  | -1
rolpassword   | ********
rolvaliduntil | 
rolconfig     | 
oid           | 18568

postgres=# \q
[root@ps-rh6 ~]# /usr/share/ovirt-engine/dbscripts/restore.sh -u engine -d engine -f /root/engine_Tue_Jun_25_15\:33\:49_CEST_2013.sql -r
Restore of database engine from /root/engine_Tue_Jun_25_15:33:49_CEST_2013.sql started...
psql: FATAL:  database "engine" does not exist
Usage: restore.sh [-h] [-s SERVERNAME] [-p PORT] -u USERNAME -d DATABASE -f FILE [-r] [-o] 

        -s SERVERNAME - The database servername for the database (def. localhost)
        -p PORT       - The database port for the database       (def. 5432)
        -u USERNAME   - The username for the database            (def. engine)
        -d DATABASE   - The database name, this must match the db name recorded in the backup file.
        -f File       - Backup file name to restore from. /root/engine_Tue_Jun_25_15:33:49_CEST_2013.sql
        -r            - Remove existing database with same name
        -o            - Omit upgrade step
        -h            - This help text.

for more options please run pg_restore --help

The recommended way for restoring your database is.
        1) Backup current database with backup.sh
        2) Drop existing DB with dropdb or use the -r flag.
        3) Create a new blank db with the same name with createdb.
        4) Run restore.sh and give new database instance name as the target
~~~

Comment 3 Scott Haines 2013-09-23 23:35:00 UTC

Targeting for 2.1.z U2 (Corbett) release.

Comment 4 Aravinda VK 2013-11-25 09:55:42 UTC

Closing this bug since the original bug is closed with the following comment.
https://bugzilla.redhat.com/show_bug.cgi?id=976337#c18