[Users] ovirt-engine upgrade 3.0 to 3.1 issue
Eli Mesika
emesika at redhat.com
Sun Jul 22 10:24:32 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 12:41:36 PM
> Subject: Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue
>
>
>
> ----- 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.
Opened BZ 842119
patch attached
>
>
> > 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
> >
> _______________________________________________
> Users mailing list
> Users at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/users
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 0001-core-pre-upgrade-scripts-will-not-run-.-842119.patch
Type: text/x-patch
Size: 108608 bytes
Desc: not available
URL: <http://lists.ovirt.org/pipermail/users/attachments/20120722/114bb341/attachment-0001.bin>
More information about the Users
mailing list