Bug 976337

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

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.

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
~~~

fixed in commit : 9bc347b89b20cc6ba67c55474ce11608ff4514ff

*** Bug 1028812 has been marked as a duplicate of this bug. ***

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...

+       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"

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

For issue in comment #6 separate BZ opened - BZ1030806.

Otherwise ok is23.

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.

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?

(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

*** Bug 1028812 has been marked as a duplicate of this bug. ***

So you put '-w' into the script and now you want a user to setup PGPASSWORD environment variable. IMHO this is odd.

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].