Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue
- Original Message -
From: Eli Mesika emes...@redhat.com
To: Dead Horse deadhorseconsult...@gmail.com
Cc: users@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 deadhorseconsult...@gmail.com
To: Robert Middleswarth rob...@middleswarth.net
Cc: Eli Mesika emes...@redhat.com, users@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
deadhorseconsult...@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
('0300','upgrade/03_00__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
('0300','upgrade/03_00__add_schema_version.sql','0','postgres',now(),'INSTALLED',true
Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue
Sounds like a good candidate for engine_3.1 gerrit branch (after push to
master), and probably a release blocker as well.
- Original Message -
- Original Message -
From: Eli Mesika emes...@redhat.com
To: Dead Horse deadhorseconsult...@gmail.com
Cc: users@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 emes...@redhat.com
To: Dead Horse deadhorseconsult...@gmail.com
Cc: users@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 deadhorseconsult...@gmail.com
To: Robert Middleswarth rob...@middleswarth.net
Cc: Eli Mesika emes...@redhat.com, users@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
deadhorseconsult...@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
('0300','upgrade/03_00__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
Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue
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
Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue
- Original Message - From: Eli Mesika emes...@redhat.com To: Dead Horse deadhorseconsult...@gmail.com Cc: users@ovirt.org Sent: Thursday, July 19, 2012 11:10:53 AM Subject: Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue - 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/ 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. Another important thing, after you complete those steps please run upgrade_db.sh -u postgres -d engine The script is under the dbscripts directory This is mandatory in order to install 3.1 upgrade scripts 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 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
Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue
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/ 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@ovirt.org http://lists.ovirt.org/mailman/listinfo/users
Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue
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
[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 Populate with with the backup of the above 3.0 database: psql -U postgres -d engine -w /temp/engine.sql 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
