[Users] ovirt-engine upgrade 3.0 to 3.1 issue
Eli Mesika
emesika at redhat.com
Sun Jul 22 09:41:36 UTC 2012
----- Original Message -----
> From: "Eli Mesika" <emesika at redhat.com>
> To: "Dead Horse" <deadhorseconsulting at gmail.com>
> Cc: users at 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 at gmail.com>
> > To: "Robert Middleswarth" <robert at middleswarth.net>
> > Cc: "Eli Mesika" <emesika at redhat.com>, users at 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 at 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.
Hi again
Problem was resolved
1) pre-upgrade scripts did not run because no md5 change was found in files after restore
2) pre-upgrade scripts did not run in the correct order after fixing 1)
I will issue a patch upstream for that, please let me know if you want me to send you the patch before if you want to test it on your environment.
> 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 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/
> > 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
> >
> >
> >
> >
> >
> _______________________________________________
> Users mailing list
> Users at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/users
>
More information about the Users
mailing list