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