[Users] ovirt-engine upgrade 3.0 to 3.1 issue
Dead Horse
deadhorseconsulting at gmail.com
Fri Jul 20 15:04:26 UTC 2012
Any thoughts here on this, should I file a bug?
Per: http://wiki.ovirt.org/wiki/Second_Release
Working upgrade is one of the release criteria.
I will help in any way possible to get a 3.0 to 3.1 upgrade path working.
- DHC
On Thu, Jul 19, 2012 at 3:40 PM, Dead Horse
<deadhorseconsulting at gmail.com>wrote:
> If it helps here is the contents of "upgrade.sh.log":
>
> ********* QUERY **********
> create or replace function __temp_fn_create_schema_version ()
> returns void
> AS $procedure$
> begin
> if not exists (select 1 from information_schema.tables where table_name =
> 'schema_version') then
> CREATE SEQUENCE schema_version_seq INCREMENT BY 1 START WITH 1;
> CREATE TABLE schema_version
> (
> id INTEGER DEFAULT NEXTVAL('schema_version_seq') NOT NULL,
> "version" varchar(10) NOT NULL,
> script varchar(255) NOT NULL,
> checksum varchar(128),
> installed_by varchar(30) NOT NULL,
> started_at timestamp DEFAULT now(),
> ended_at timestamp ,
> state character varying(15) NOT NULL,
> "current" boolean NOT NULL,
> CONSTRAINT schema_version_primary_key PRIMARY KEY (id)
> );
>
> insert into
> schema_version(version,script,checksum,installed_by,ended_at,state,current)
> values
> ('03000000','upgrade/03_00_0000_add_schema_version.sql','0','postgres',now(),'INSTALLED',true);
> end if;
>
> END; $procedure$
> LANGUAGE plpgsql;
> **************************
>
> CREATE FUNCTION
> ********* QUERY **********
> select __temp_fn_create_schema_version();
> **************************
>
>
>
> ********* QUERY **********
> DROP FUNCTION __temp_fn_create_schema_version();
> **************************
>
> DROP FUNCTION
> ********* QUERY **********
> CREATE OR REPLACE FUNCTION __temp_Upgrade_add_job_table()
> RETURNS void
> AS $function$
> BEGIN
> IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name
> ILIKE 'job') THEN
>
> -- Add the job table.
>
> 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;
>
> END IF;
> END; $function$
> LANGUAGE plpgsql;
> **************************
>
> CREATE FUNCTION
> ********* QUERY **********
> SELECT * FROM __temp_Upgrade_add_job_table();
> **************************
>
>
>
> ********* QUERY **********
> DROP FUNCTION __temp_Upgrade_add_job_table();
> **************************
>
> DROP FUNCTION
> ********* QUERY **********
> insert into
> schema_version(version,script,checksum,installed_by,started_at,ended_at,state,current,comment)
> values
> (trim('03010260'),'upgrade/03_01_0260_add_job_table.sql','c74d309cfb8f9d802e54f2ff66d560b4','postgres',
> cast(trim(' 2012-07-19 15:30:52.500231-05') as
> timestamp),cast(trim(' 2012-07-19 15:30:52.565532-05') as
> timestamp),'INSTALLED',false,'');
> **************************
>
> ********* QUERY **********
> create or replace function __temp_fn_create_schema_version ()
> returns void
> AS $procedure$
> begin
> if not exists (select 1 from information_schema.tables where table_name =
> 'schema_version') then
> CREATE SEQUENCE schema_version_seq INCREMENT BY 1 START WITH 1;
> CREATE TABLE schema_version
> (
> id INTEGER DEFAULT NEXTVAL('schema_version_seq') NOT NULL,
> "version" varchar(10) NOT NULL,
> script varchar(255) NOT NULL,
> checksum varchar(128),
> installed_by varchar(30) NOT NULL,
> started_at timestamp DEFAULT now(),
> ended_at timestamp ,
> state character varying(15) NOT NULL,
> "current" boolean NOT NULL,
> CONSTRAINT schema_version_primary_key PRIMARY KEY (id)
> );
>
> insert into
> schema_version(version,script,checksum,installed_by,ended_at,state,current)
> values
> ('03000000','upgrade/03_00_0000_add_schema_version.sql','0','postgres',now(),'INSTALLED',true);
> end if;
>
> END; $procedure$
> LANGUAGE plpgsql;
> **************************
>
> CREATE FUNCTION
> ********* QUERY **********
> select __temp_fn_create_schema_version();
> **************************
>
>
>
> ********* QUERY **********
> DROP FUNCTION __temp_fn_create_schema_version();
> **************************
>
> DROP FUNCTION
> ********* QUERY **********
> CREATE OR REPLACE FUNCTION __temp_Upgrade_add_job_table()
> RETURNS void
> AS $function$
> BEGIN
> IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name
> ILIKE 'job') THEN
>
> -- Add the job table.
>
> 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;
>
> END IF;
> END; $function$
> LANGUAGE plpgsql;
> **************************
>
> CREATE FUNCTION
> ********* QUERY **********
> SELECT * FROM __temp_Upgrade_add_job_table();
> **************************
>
>
>
> ********* QUERY **********
> DROP FUNCTION __temp_Upgrade_add_job_table();
> **************************
>
> DROP FUNCTION
> ********* QUERY **********
> insert into
> schema_version(version,script,checksum,installed_by,started_at,ended_at,state,current,comment)
> values
> (trim('03010260'),'upgrade/03_01_0260_add_job_table.sql','c74d309cfb8f9d802e54f2ff66d560b4','postgres',
> cast(trim(' 2012-07-19 15:34:00.060083-05') as
> timestamp),cast(trim(' 2012-07-19 15:34:00.124151-05') as
> timestamp),'INSTALLED',false,'');
> **************************
>
>
>
>
> On Thu, Jul 19, 2012 at 2:26 PM, Dead Horse <deadhorseconsulting at gmail.com
> > wrote:
>
>> 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/20120720/5591475b/attachment-0001.html>
More information about the Users
mailing list