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

Description of problem:

After executing engine-cleanup, engine-backup --mode=restore does not work unless steps are performed that were supposed to be completed by engine-cleanup.

The ovirt-engine service is not stopped and the database is not properly removed or emptied.

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

ovirt 3.3.3 on CentOS 6.5

How reproducible:

100%

Steps to Reproduce:
1. Backup using engine-backup

$ engine-backup --mode=backup --scope=all --file=/tmp/engine.tar.bz2 --log=/tmp/engine-backup.log

2. Run engine-cleanup answering Yes and OK to all prompts

3. Attempt to restore engine which fails with "FATAL: Engine service is active - can not restore backup"

$ engine-backup --mode=restore --scope=all --file=/tmp/engine.tar.bz2 --log=/tmp/engine-backup.log --change-db-credentials --db-host=localhost --db-port=5432 --db-user=engine --db-password=<PASS> --db-name=engine

4. Stop the ovirt-engine service (even though engine-cleanup output stated this was done)

5. Attempt restore again which fails with "FATAL: Database is not empty"

$ engine-backup --mode=restore --scope=all --file=/tmp/engine.tar.bz2 --log=/tmp/engine-backup.log --change-db-credentials --db-host=localhost --db-port=5432 --db-user=engine --db-password=<PASS> --db-name=engine

6. Drop engine database and re-create

$ su - postgres -c "dropdb engine"
$ su - postgres -c "psql -c \"create database engine owner engine template template0 encoding 'UTF8' lc_collate 'en_US.UTF-8' lc_ctype 'en_US.UTF-8'\""

7. Attempt restore, and this time it will succeed

$ engine-backup --mode=restore --scope=all --file=/tmp/engine.tar.bz2 --log=/tmp/engine-backup.log --change-db-credentials --db-host=localhost --db-port=5432 --db-user=engine --db-password=<PASS>--db-name=engine

Actual results:

After engine-cleanup the ovirt-engine service is not stopped and the engine database is not empty.

Based on mailing list suggestion, here is output from pg_dump

$ su - postgres -c "pg_dump engine | grep -i ^create"
CREATE PROCEDURAL LANGUAGE plpgsql;

Expected results:

The ovirt-engine service would be completely stopped and the database completely cleared or removed so a engine-backup --mode=restore would work without manual intervention.

Additional info:

--- Additional comment from Trey Dockendorf on 2014-02-18 15:01:28 EST ---

engine database after engine-cleanup is run.

--- Additional comment from Trey Dockendorf on 2014-02-18 15:03:56 EST ---

log from engine-cleanup.  Domain name replaced with <OMIT>.

--- Additional comment from Trey Dockendorf on 2014-02-18 19:36:46 EST ---

A restore fails with ovirt-engine-3.4.0-0.7.beta2.el6.noarch as well.

Performed a backup as before then ran a full 'engine-cleanup'.  A restore fails with "FATAL: Database is not empty".  Dropping and re-creating the engine database allows restore to work.

--- Additional comment from Eli Mesika on 2014-02-23 16:10:38 EST ---

This is taken from engine-backup code 

---snip---
verifyConnection() {
        PGPASSFILE="${MYPGPASS}" psql \
                -w \
                -U "${ENGINE_DB_USER}" \
                -h "${ENGINE_DB_HOST}" \
                -p "${ENGINE_DB_PORT}" \
                -d "${ENGINE_DB_DATABASE}" \
                -c "select 1" \
                >> "${LOG}" 2>&1 \
                || logdie "Can't connect to the database. Please see '${0} --help'."

        PGPASSFILE="${MYPGPASS}" pg_dump \
                -U "${ENGINE_DB_USER}" \
                -h "${ENGINE_DB_HOST}" \
                -p "${ENGINE_DB_PORT}" \
                "${ENGINE_DB_DATABASE}" | \
                grep -vi '^create extension' | \
                grep -iq '^create' && \
                logdie "Database is not empty"
}
---snip---

Why the method used to check if DB is empty is by checking the CREATE EXTENSION in the output of pg_dump ??? This is not correct since the backup generates 
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

The db cleanup is not cleaning installed extensions and is not supposed to do so.

This is what is causing the restore to fail, please look instead for a the creation of the schema_version table 

The fix should be in the engine-backup code

--- Additional comment from Yedidyah Bar David on 2014-02-24 01:46:23 EST ---

(In reply to Eli Mesika from comment #4)
> This is taken from engine-backup code 
> 
> ---snip---
> verifyConnection() {
>         PGPASSFILE="${MYPGPASS}" psql \
>                 -w \
>                 -U "${ENGINE_DB_USER}" \
>                 -h "${ENGINE_DB_HOST}" \
>                 -p "${ENGINE_DB_PORT}" \
>                 -d "${ENGINE_DB_DATABASE}" \
>                 -c "select 1" \
>                 >> "${LOG}" 2>&1 \
>                 || logdie "Can't connect to the database. Please see '${0}
> --help'."
> 
>         PGPASSFILE="${MYPGPASS}" pg_dump \
>                 -U "${ENGINE_DB_USER}" \
>                 -h "${ENGINE_DB_HOST}" \
>                 -p "${ENGINE_DB_PORT}" \
>                 "${ENGINE_DB_DATABASE}" | \
>                 grep -vi '^create extension' | \
>                 grep -iq '^create' && \
>                 logdie "Database is not empty"
> }
> ---snip---
> 
> Why the method used to check if DB is empty is by checking the CREATE
> EXTENSION in the output of pg_dump ??? This is not correct since the backup
> generates 
> CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
> 
> The db cleanup is not cleaning installed extensions and is not supposed to
> do so.
> 
> This is what is causing the restore to fail, please look instead for a the
> creation of the schema_version table 
> 
> The fix should be in the engine-backup code

Please read carefully the code. It's doing pg_dump, then _removes_ (grep -v) from the output all 'create extension' statements, then checks if any other 'create' statements are left - and if any are found, the db is considered non-empty.

This was checked simply by creating a database and then immediately running pg_dump, to see what's always in the dump.

The current bug is not about 'CREATE EXTENSION' but about 'CREATE PROCEDURAL LANGUAGE plpgsql'. Should engine-backup code ignore this one as well? Or should engine-cleanup drop 'PROCEDURAL LANGUAGE plpgsql'? They might as well be the same thing, with different syntax due to reasons I do not know.

--- Additional comment from Eli Mesika on 2014-02-24 04:50:13 EST ---

(In reply to Yedidyah Bar David from comment #5)

> The current bug is not about 'CREATE EXTENSION' but about 'CREATE PROCEDURAL
> LANGUAGE plpgsql'. Should engine-backup code ignore this one as well? Or
> should engine-cleanup drop 'PROCEDURAL LANGUAGE plpgsql'? They might as well
> be the same thing, with different syntax due to reasons I do not know.

This is the difference between PG 8.4 and 9.x when the pgplsql language is created by the CREATE LANGUAGE in 8.4 and considered as EXTENSION in 9.x

I will handle that by making sure that the cleanup drops this language if exists as well