[Users] ovirt-engine upgrade 3.0 to 3.1 issue

[Dead Horse]

Eli,
I am following the procedure for DB backup/restore from
here: http://wiki.ovirt.org/wiki/Backup_engine_db
and
here:
http://docs.redhat.com/docs/en-US/Red_Hat_Enterprise_Virtualization/3.0/html/Administration_Guide/Administration_Guide-Backing_Up.html#Administration_Guide-BU_Database
(Which the RHEV proceadure BTW has a syntax error):
"psql -U postgres -d rhevm -W -f
/usr/share/rhevm/db-backups/dump_RHEVDB_BACKUP_`date "+%Y%m%d_%R"`.sql"
should be:
psql -U postgres  -W -f
/usr/share/rhevm/db-backups/dump_RHEVDB_BACKUP_`date "+%Y%m%d_%R"`.sql

Either of the above procedures worked with nightly builds up until about a
month back. I was able to backup a 3.0 database and restore it per the
above on a system running the nightly build, then run the upgrade.sh script
located in /usr/share/ovirt-engine/dbscripts. There is no script in
/usr/share/ovirt-engine/dbscripts called "upgrade_db.sh" I always ran
upgrade.sh -u postgres and it worked fine.

I am not using create_db.sh script in /usr/share/ovirt-engine/dbscirpts to
create the database and objects. The createdb command I use is the standard
postgres command to create an new empty database. (/bin/createdb"). Thus
what imports is actually a clean restore of the 3.0 database

Similarly I am using "/bin/dropdb" to destroy/delete the engine database.

Thus importing the dump from 3.0 works using those procedures but results
in the previous error I noted when attempting to run
"/usr/share/ovirt-engine/dbscripts/upgrade.sh".

- DHC

On Thu, Jul 19, 2012 at 7:04 AM, Robert Middleswarth <
robert at middleswarth.net> wrote:

> On 07/19/2012 04:10 AM, Eli Mesika wrote:
>
>>
>> ----- Original Message -----
>>
>>> From: "Dead Horse" <deadhorseconsulting at gmail.com**>
>>> To: users at ovirt.org
>>> Sent: Thursday, July 19, 2012 2:22:10 AM
>>> Subject: Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue
>>>
>>>
>>> Forgot reply-all sending to ovirt-users as well.
>>> -DHC
>>>
>>>
>>> On Wed, Jul 18, 2012 at 6:03 PM, Dead Horse <
>>> deadhorseconsulting at gmail.com > wrote:
>>>
>>>
>>> 3.0 engine database dump attached as: "engine.sql.tar.gz"
>>> - DHC
>>>
>>>
>>>
>>>
>>> On Wed, Jul 18, 2012 at 1:05 PM, Eli Mesika < emesika at redhat.com >
>>> wrote:
>>>
>>>
>>>
>>>
>>>
>>>
>>> ----- Original Message -----
>>>
>>>> From: "Dead Horse" < deadhorseconsulting at gmail.com >
>>>> To: Users at ovirt.org
>>>> Sent: Wednesday, July 18, 2012 9:08:55 AM
>>>> Subject: [Users] ovirt-engine upgrade 3.0 to 3.1 issue
>>>>
>>>>
>>>> Steps taken:
>>>>
>>>> Load up bare metal or a VM with FC16
>>>> Install 3.0 from http://www.ovirt.org/releases/**stable/fedora/16/<http://www.ovirt.org/releases/stable/fedora/16/>
>>>> Setup up something minimal (EG: engine-setup then setup a basic
>>>> datacenter/cluster/etc)
>>>> Add a FC16 or EL based node for fun as well and some VM's if
>>>> feeling
>>>> ambitious.
>>>>
>>>> Back up database:
>>>> systemctl stop jboss-as.service
>>>> pg_dump -C -E UTF8 --column-inserts --disable-dollar-quoting
>>>> --disable-triggers -U postgres --format=p -f "/temp/engine.sql"
>>>> engine
>>>>
>>>> Back up the following files within /etc/pki/ovirt-engine from a 3.0
>>>> install (preserve owner/group).
>>>> /etc/pki/ovirt-engine/cacert.**conf
>>>> /etc/pki/ovirt-engine/ca.pem
>>>> /etc/pki/ovirt-engine/cert.**conf
>>>> /etc/pki/ovirt-engine/certs/**01.pem
>>>> /etc/pki/ovirt-engine/certs/**02.pem
>>>> /etc/pki/ovirt-engine/certs/**ca.der
>>>> /etc/pki/ovirt-engine/certs/**engine.cer
>>>> /etc/pki/ovirt-engine/certs/**engine.der
>>>> /etc/pki/ovirt-engine/**database.txt
>>>> /etc/pki/ovirt-engine/**database.txt.attr
>>>> /etc/pki/ovirt-engine/**database.txt.attr.old
>>>> /etc/pki/ovirt-engine/**database.txt.old
>>>> /etc/pki/ovirt-engine/keys/**engine_id_rsa
>>>> /etc/pki/ovirt-engine/keys/**engine.ssh.key.txt
>>>> /etc/pki/ovirt-engine/private/**ca.pem
>>>> /etc/pki/ovirt-engine/**requests/ca.csr
>>>> /etc/pki/ovirt-engine/**requests/engine.req
>>>>
>>>> Load up bare metal or a VM with FC17 (Upgrade from FC16 --> FC17 is
>>>> still rather messy)
>>>> Install 3.1 from http://www.ovirt.org/releases/**beta/fedora/17/<http://www.ovirt.org/releases/beta/fedora/17/>
>>>>
>>>> Run engine-setup to get an initial setup.
>>>> stop ovirt-engine (systemctl stop ovirt-engine.service)
>>>>
>>>> Copy the previously backed up /etc/pki/ovirt-engine files from
>>>> above
>>>> over top of the ones just generated (be sure to preserve
>>>> owner/group
>>>> EG: cp -a)
>>>>
>>>> Drop the existing engine database: dropdb -U postgres engine
>>>> Create a new blank database: createdb -U postgres engine
>>>>
>>> Well, the problem is that you are creating here the database with
>> createdb.
>> This creates all DB objects and runs all upgrade scripts.
>> Then when you try to restore from your file , you have problems, since
>> your file also tries to create the same objects.
>> instead of : createdb -U postgres engine
>> do :
>>
>>> echo "create database engine;" | psql -U postgres template1
>>>
>> Then continue on the next steps and it will work.
>>
>> optionally, in your backup command:
>> instead of
>> pg_dump -C -E UTF8 --column-inserts --disable-dollar-quoting
>> use
>> pg_dump --data-only -E UTF8 --column-inserts --disable-dollar-quoting
>>
>> then , createdb will create the db & schema and your engine.sql will
>> include only "insert"
>>
>> Eli
>>
> I am anything but a PG expert but exporting only the data then inserting
> it on top of the upgrade scheme is a really dumb idea.  It might work today
> if the scheme doesn't change much but it could really breaks things
> tomorrow if the scheme sees a massive change. You were correct Just create
> the database restore then run the upgrade scheme.
>
> Thanks
> Robert
>
>  Populate with with the backup of the above 3.0 database:
>>>> psql -U postgres -d engine -w < "/temp/engine.sql"
>>>>
>>> Can you please attach the engine.sql file so I will be able to
>>> reproduce , thanks
>>>
>>>
>>>
>>>
>>>  Attempt to upgrade the DBschema via
>>>> /usr/share/ovirt-engine/**dbscripts/upgrade.sh
>>>> upgrade.sh -u postgres
>>>>
>>>> The DB schema upgrade will fail like so:
>>>>
>>>> Running upgrade script upgrade/03_01_0260_add_job_**table.sql
>>>> psql:upgrade/03_01_0260_add_**job_table.sql:29: NOTICE: CREATE TABLE
>>>> /
>>>> PRIMARY KEY will create implicit index "pk_jobs" for table "job"
>>>> CONTEXT: SQL statement "CREATE TABLE job
>>>> (
>>>> job_id UUID NOT NULL,
>>>> action_type VARCHAR(50) NOT NULL,
>>>> description TEXT NOT NULL,
>>>> status VARCHAR(32) NOT NULL,
>>>> owner_id UUID,
>>>> visible BOOLEAN NOT NULL DEFAULT true,
>>>> start_time TIMESTAMP WITH TIME ZONE NOT NULL,
>>>> end_time TIMESTAMP WITH TIME ZONE default NULL,
>>>> last_update_time TIMESTAMP WITH TIME ZONE default NULL,
>>>> correlation_id VARCHAR(50) NOT NULL,
>>>> CONSTRAINT pk_jobs PRIMARY KEY(job_id)
>>>> )
>>>> WITH OIDS"
>>>> PL/pgSQL function "__temp_upgrade_add_job_table" line 6 at SQL
>>>> statement
>>>> psql:/tmp/tmp.mXz8U4xpWr:3: ERROR: column "comment" of relation
>>>> "schema_version" does not exist
>>>> LINE 1:
>>>> ...ksum,installed_by,started_**at,ended_at,state,current,**comment)
>>>>
>>>> My assumption here is that if the DB schema upgrade would work,
>>>> that
>>>> this should be the cleanest way to upgrade from 3.0 to 3.1.
>>>>
>>>> - DHC
>>>>
>>>> ______________________________**_________________
>>>> Users mailing list
>>>> Users at ovirt.org
>>>> http://lists.ovirt.org/**mailman/listinfo/users<http://lists.ovirt.org/mailman/listinfo/users>
>>>>
>>>>
>>>
>>> ______________________________**_________________
>>> Users mailing list
>>> Users at ovirt.org
>>> http://lists.ovirt.org/**mailman/listinfo/users<http://lists.ovirt.org/mailman/listinfo/users>
>>>
>>>  ______________________________**_________________
>> Users mailing list
>> Users at ovirt.org
>> http://lists.ovirt.org/**mailman/listinfo/users<http://lists.ovirt.org/mailman/listinfo/users>
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.ovirt.org/pipermail/users/attachments/20120719/cc325e3f/attachment-0001.html>