[Users] ovirt-engine upgrade 3.0 to 3.1 issue
Dead Horse
deadhorseconsulting at gmail.com
Thu Jul 19 20:40:55 UTC 2012
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 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/<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 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<http://lists.ovirt.org/mailman/listinfo/users>
>>>>>
>>>>>
>>>>
>>>> ______________________________**_________________
>>>> Users mailing list
>>>> Users at ovirt.org
>>>> http://lists.ovirt.org/**mailman/listinfo/users<http://lists.ovirt.org/mailman/listinfo/users>
>>>>
>>>> ______________________________**_________________
>>> Users mailing list
>>> Users at ovirt.org
>>> http://lists.ovirt.org/**mailman/listinfo/users<http://lists.ovirt.org/mailman/listinfo/users>
>>>
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.ovirt.org/pipermail/users/attachments/20120719/cb2bdb83/attachment-0001.html>
More information about the Users
mailing list