[Users] ovirt-engine upgrade 3.0 to 3.1 issue
Eli Mesika
emesika at redhat.com
Sun Jul 22 07:46:48 UTC 2012
----- 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.
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
>
>
>
>
>
More information about the Users
mailing list