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