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 <deadhorseconsult...@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 <deadhorseconsult...@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 < >> rob...@middleswarth.net> wrote: >> >>> On 07/19/2012 04:10 AM, Eli Mesika wrote: >>> >>>> >>>> ----- Original Message ----- >>>> >>>>> From: "Dead Horse" <deadhorseconsult...@gmail.com**> >>>>> To: users@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 < >>>>> deadhorseconsult...@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 < emes...@redhat.com > >>>>> wrote: >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> ----- Original Message ----- >>>>> >>>>>> From: "Dead Horse" < deadhorseconsult...@gmail.com > >>>>>> To: Users@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@ovirt.org >>>>>> http://lists.ovirt.org/**mailman/listinfo/users<http://lists.ovirt.org/mailman/listinfo/users> >>>>>> >>>>>> >>>>> >>>>> ______________________________**_________________ >>>>> Users mailing list >>>>> Users@ovirt.org >>>>> http://lists.ovirt.org/**mailman/listinfo/users<http://lists.ovirt.org/mailman/listinfo/users> >>>>> >>>>> ______________________________**_________________ >>>> Users mailing list >>>> Users@ovirt.org >>>> http://lists.ovirt.org/**mailman/listinfo/users<http://lists.ovirt.org/mailman/listinfo/users> >>>> >>> >>> >> >
_______________________________________________ Users mailing list Users@ovirt.org http://lists.ovirt.org/mailman/listinfo/users