[Users] ovirt-engine upgrade 3.0 to 3.1 issue

[Robert Middleswarth]

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/
>>> 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/
>>>
>>> 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
>>>
>>
>>
>> _______________________________________________
>> Users mailing list
>> Users at ovirt.org
>> http://lists.ovirt.org/mailman/listinfo/users
>>
> _______________________________________________
> Users mailing list
> Users at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/users