Bug 976337 - [TEXT] restore.sh woes, unable to restore engine DB without using '-u postgres'
[TEXT] restore.sh woes, unable to restore engine DB without using '-u postgres'
Status: CLOSED WONTFIX
Product: Red Hat Enterprise Virtualization Manager
Classification: Red Hat
Component: ovirt-engine (Show other bugs)
3.3.0
Unspecified Unspecified
unspecified Severity high
: ---
: 3.3.0
Assigned To: Eli Mesika
Jiri Belka
infra
:
: 1028812 (view as bug list)
Depends On:
Blocks: 960760 980042 989477 1019461 3.3snap2
  Show dependency treegraph
 
Reported: 2013-06-20 08:04 EDT by Jiri Belka
Modified: 2016-02-10 14:24 EST (History)
15 users (show)

See Also:
Fixed In Version: is23
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
: 989477 (view as bug list)
Environment:
Last Closed: 2013-11-18 09:10:20 EST
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: Infra
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)


External Trackers
Tracker ID Priority Status Summary Last Updated
oVirt gerrit 19329 None None None Never
oVirt gerrit 20929 None None None Never

  None (edit)
Description Jiri Belka 2013-06-20 08:04:09 EDT
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?
Comment 1 Jiri Belka 2013-06-20 08:53:34 EDT
if 'template0' is really from postgresql itself, then ignore my comment about this :-)
Comment 2 Alex Lourie 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.
Comment 3 Jiri Belka 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 4 Eli Mesika 2013-11-05 09:43:04 EST
fixed in commit : 9bc347b89b20cc6ba67c55474ce11608ff4514ff
Comment 5 Eli Mesika 2013-11-11 16:46:44 EST
*** Bug 1028812 has been marked as a duplicate of this bug. ***
Comment 6 Jiri Belka 2013-11-14 09:57:01 EST
Wrong escaping or usage of double quotes.

...
        2) Drop existing from root user by : su - postgres -c "psql -d template1 -c "drop database <db>;"" 
...

-%-
# su - postgres -c "psql -d template1 -c "drop database engine;""
ERROR:  syntax error at end of input
LINE 1: drop
            ^
-bash: : command not found
-%-

Either escape nested double quotes or use single quotes inside double quotes.

Same issue with following example from --help.

        3) Create a new blank db with the same name by:  su - postgres -c "psql -d template1 -c "create database <db> owner engine;"".

Why not to replace <db> to engine? If anybody wants to "clone" to different DB name, he would probably know how to do it...
Comment 7 Jiri Belka 2013-11-14 09:59:19 EST
+       printf "\t2) Drop existing from root user by : su - postgres -c \"psql -d template1 -c \"drop database <db>;\"\" \n"
+       printf "\t3) Create a new blank db with the same name by:  su - postgres -c \"psql -d template1 -c \"create database <db> owner engine;\"\".\n"

to

+       printf "\t2) Drop existing from root user by : su - postgres -c \"psql -d template1 -c \\"drop database <db>;\\"\" \n"
+       printf "\t3) Create a new blank db with the same name by:  su - postgres -c \"psql -d template1 -c \\"create database <db> owner engine;\\"\".\n"
Comment 8 Eli Mesika 2013-11-14 11:07:32 EST
Please open as separate bug/s as the above in comment 6 and comment 7 has no relation to the original bug opened 
It will be much easier to track that if the verification will check only the reported bug.

Thanks
Comment 9 Jiri Belka 2013-11-15 03:18:32 EST
For issue in comment #6 separate BZ opened - BZ1030806.

Otherwise ok is23.
Comment 10 Jiri Belka 2013-11-15 07:01:19 EST
I had modified pg_hba.conf which was not what is default after engine-setup. This made my verification incorrect.

Sorry but thus restore.sh does not work by default:

Procedure:

* install clean rhevm
* drop database with command from `restore.sh --help` output
* create new database with command from `restore.sh --help` output
* use restore.sh for restore itself

* default pg_hba.conf after rhevm installation

-%-
# egrep -v "^#|^[ \t]*$" /var/lib/pgsql/data/pg_hba.conf 
local   all         all                               ident
host    engine          engine          0.0.0.0/0               md5
host    engine          engine          ::0/0                   md5
host    all         all         127.0.0.1/32          ident
host    all         all         ::1/128               ident
-%-

My try:

-%-
# su - postgres -c "psql -d template1 -c \"drop database engine;\""
DROP DATABASE
# su - postgres -c "psql -d template1 -c \"create database engine owner engine;\""
CREATE DATABASE
# su - postgres -c 'psql -c \\l'
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 engine    | engine   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
(4 rows)
# id ; ./restore.sh -u engine -d engine -f /tmp/backup.sql 
uid=0(root) gid=0(root) groups=0(root) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
psql: FATAL:  Ident authentication failed for user "engine"
Database engine does not exist, please create an empty database named engine.
-%-

Following configuration made it work... But again, it is not default one after rhevm installation!

-%-
local   all         all                               ident
host    engine          engine          0.0.0.0/0               trust
host    engine          engine          ::0/0                   trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust
# su - postgres -c 'pg_ctl reload'
server signaled
# ./restore.sh -u engine -d engine -f /tmp/backup.sql >/dev/null ; echo $?
psql:/tmp/backup.sql:19871: WARNING:  column "storage_pool_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:/tmp/backup.sql:19871: WARNING:  column "storage_pool_name" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:/tmp/backup.sql:28742: WARNING:  no privileges could be revoked for "public"
psql:/tmp/backup.sql:28743: WARNING:  no privileges could be revoked for "public"
psql:/tmp/backup.sql:28744: WARNING:  no privileges were granted for "public"
psql:/tmp/backup.sql:28745: WARNING:  no privileges were granted for "public"
0
-%-

Changing back to 'ASSIGNED' as it does not work by default.
Comment 11 Jiri Belka 2013-11-15 07:10:26 EST
I'm not DBA but I found strange I had to change in pg_hba.conf also lines from 0.0.0.0/0 and ::0/0. This brought me to attention to what is '-h' by default. It is localhost, thus 127.0.0.1/32. But some psql commands involved in restore do not use localhost...

From output of restore.sh...

-%-
# fgrep psql /tmp/out | egrep -v '\-h|\--host' | grep -v ^psql
+++ local 'cmdline=psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1'
+++ cmdline='psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 --file=/tmp/tmp.s1yJUdvllS '
+++ cmdline='psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 --file=/tmp/tmp.s1yJUdvllS  --dbname=template1 '
+++ cmdline='psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 --file=/tmp/tmp.s1yJUdvllS  --dbname=template1  --username=engine '
+++ local 'cmdline=psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1'
+++ cmdline='psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 --file=/tmp/tmp.rHPXJgouDO '
+++ cmdline='psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 --file=/tmp/tmp.rHPXJgouDO  --dbname=engine '
+++ cmdline='psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 --file=/tmp/tmp.rHPXJgouDO  --dbname=engine  --username=engine '
+ local 'cmdline=psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1'
+ cmdline='psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 --file=upgrade/03_01_0000_set_version.sql '
+ cmdline='psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 --file=upgrade/03_01_0000_set_version.sql  --dbname=engine '
+ cmdline='psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 --file=upgrade/03_01_0000_set_version.sql  --dbname=engine  --username=engine '
+ local 'cmdline=psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1'
+ cmdline='psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 --file=/tmp/tmp.IIvxLx1a8p '
+ cmdline='psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 --file=/tmp/tmp.IIvxLx1a8p  --dbname=engine '
+ cmdline='psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 --file=/tmp/tmp.IIvxLx1a8p  --dbname=engine  --username=engine '
++ local 'cmdline=psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1'
++ cmdline='psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 --file=/tmp/tmp.ijN7TdDqbD '
++ cmdline='psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 --file=/tmp/tmp.ijN7TdDqbD  --dbname=engine '
++ cmdline='psql -w --pset=tuples_only=on --set ON_ERROR_STOP=1 --file=/tmp/tmp.ijN7TdDqbD  --dbname=engine  --username=engine '
-%-

Shouldn't --host be unified in whole scripts involved?
Comment 12 Eli Mesika 2013-11-17 09:31:42 EST
(In reply to Jiri Belka from comment #11)

you don't have to change the pg_hba.conf , leave it as is

from 3.3 you have to set the password for the DB user , i.e 

> PGPASSWORD=<password>&&restore.sh .....


see
http://www.postgresql.org/docs/9.1/static/libpq-envars.html
Comment 13 Barak 2013-11-18 00:55:43 EST
*** Bug 1028812 has been marked as a duplicate of this bug. ***
Comment 14 Jiri Belka 2013-11-18 03:38:36 EST
So you put '-w' into the script and now you want a user to setup PGPASSWORD environment variable. IMHO this is odd.
Comment 18 Alon Bar-Lev 2013-11-18 08:43:53 EST
in 3.3 dbscripts scripts should not be used by customers/gss/qa unless explicitly instructed by engineering.

backup/restore should be executed using the new engine-backup script[bug#960280].

Note You need to log in before you can comment on or make changes to this bug.