Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue

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(a)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/h... > (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(a)middleswarth.net&gt; wrote: > >> On 07/19/2012 04:10 AM, Eli Mesika wrote: >> >>> >>> ----- Original Message ----- >>> >>>> From: "Dead Horse" <deadhorseconsulting(a)gmail.com**&gt; >>>> To: users(a)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(a)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(a)redhat.com > >>>> wrote: >>>> >>>> >>>> >>>> >>>> >>>> >>>> ----- Original Message ----- >>>> >>>>> From: "Dead Horse" < deadhorseconsulting(a)gmail.com > >>>>> To: Users(a)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... >>>>> 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/r... >>>>> >>>>> 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(a)ovirt.org >>>>> http://lists.ovirt.org/**mailman/listinfo/users<http://lists.ovirt.org... >>>>> >>>>> >>>> >>>> ______________________________**_________________ >>>> Users mailing list >>>> Users(a)ovirt.org >>>> http://lists.ovirt.org/**mailman/listinfo/users<http://lists.ovirt.org... >>>> >>>> ______________________________**_________________ >>> Users mailing list >>> Users(a)ovirt.org >>> http://lists.ovirt.org/**mailman/listinfo/users<http://lists.ovirt.org... >>> >> >> >