Note: This is a beta release of Red Hat Bugzilla 5.0. The data contained within is a snapshot of the live data so any changes you make will not be reflected in the production Bugzilla. Also email is disabled so feel free to test any aspect of the site that you want. File any problems you find or give feedback here.
Bug 1069131 - 'engine-backup --mode=restore' fails after engine-cleanup on postgres 8
Summary: 'engine-backup --mode=restore' fails after engine-cleanup on postgres 8
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: Red Hat Enterprise Virtualization Manager
Classification: Red Hat
Component: ovirt-engine
Version: 3.4.0
Hardware: Unspecified
OS: Unspecified
unspecified
unspecified
Target Milestone: ---
: 3.4.0
Assignee: Yedidyah Bar David
QA Contact: sefi litmanovich
URL:
Whiteboard: integration
Depends On: 1066654
Blocks: 1077079 rhev3.4snap1
TreeView+ depends on / blocked
 
Reported: 2014-02-24 09:52 UTC by Eli Mesika
Modified: 2014-06-12 14:11 UTC (History)
13 users (show)

Fixed In Version: av4
Doc Type: Bug Fix
Doc Text:
Clone Of: 1066654
: 1077079 (view as bug list)
Environment:
Last Closed:
oVirt Team: ---


Attachments (Terms of Use)


Links
System ID Priority Status Summary Last Updated
oVirt gerrit 25677 None MERGED packaging: engine-backup: ignore also 'CREATE PROCEDURAL LANGUAGE' Never

Description Eli Mesika 2014-02-24 09:52:29 UTC
+++ 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

Comment 1 Eli Mesika 2014-02-24 11:07:45 UTC
Moving this to didi since 

The databases are created by user postgres that gives ownership to user engine by

su - postgres -c "psql -d template1 -c \"create database engine  owner engine;\""

template1 has already plpgsql installed with postgres ownership therefor user engine can not drop this language since the owner remains postgres.

Since the cleanup SP is running with user engine, it can not handle this and it must be handled in the context of the engine-backup utility

Only way I see to resolve that in engine-backup when mode = restore :

1)manipulate the backup sql to remove the "create language" statement 
2)run createlang and ignore errors to create the language if not exists 
3)restore the database

Please keep in mind that this should be tested in PG 8.4 and 9.2.x

Comment 5 sefi litmanovich 2014-03-24 11:06:40 UTC
Verified on RHEL 6.5 with rhevm-3.4.0-0.10.beta2.el6ev.noarch, with PG8.4 according to steps in description.

after engine-backup and engine-cleanup, ran engine-backup mode=restore with old db credentials. restore was successful.

Comment 6 Itamar Heim 2014-06-12 14:11:31 UTC
Closing as part of 3.4.0


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