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

From: "Eli Mesika" <emesika(a)redhat.com&gt; To: "Dead Horse" <deadhorseconsulting(a)gmail.com&gt; Cc: users(a)ovirt.org Sent: Sunday, July 22, 2012 10:46:48 AM Subject: Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue ----- Original Message ----- > From: "Dead Horse" <deadhorseconsulting(a)gmail.com&gt; > To: "Robert Middleswarth" <robert(a)middleswarth.net&gt; > Cc: "Eli Mesika" <emesika(a)redhat.com&gt;, users(a)ovirt.org > Sent: Friday, July 20, 2012 6:04:26 PM > Subject: Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue > > 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(a)gmail.com > wrote: > > > If it helps here is the contents of "upgrade.sh.log": I had succeeded to simulate the problem, will update you ASAP.

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