If it helps here is the contents of "upgrade.sh.log":<br><br>********* QUERY **********<br>create or replace function __temp_fn_create_schema_version ()<br>returns void<br>AS $procedure$<br>begin<br>if not exists (select 1 from information_schema.tables where table_name = 'schema_version') then<br>
CREATE SEQUENCE schema_version_seq INCREMENT BY 1 START WITH 1;<br> CREATE TABLE schema_version<br> (<br> id INTEGER DEFAULT NEXTVAL('schema_version_seq') NOT NULL,<br> "version" varchar(10) NOT NULL,<br>
script varchar(255) NOT NULL,<br> checksum varchar(128),<br> installed_by varchar(30) NOT NULL,<br> started_at timestamp DEFAULT now(),<br> ended_at timestamp ,<br> state character varying(15) NOT NULL,<br>
"current" boolean NOT NULL,<br> CONSTRAINT schema_version_primary_key PRIMARY KEY (id)<br> );<br><br> insert into schema_version(version,script,checksum,installed_by,ended_at,state,current)<br> values ('03000000','upgrade/03_00_0000_add_schema_version.sql','0','postgres',now(),'INSTALLED',true);<br>
end if;<br><br>END; $procedure$<br>LANGUAGE plpgsql;<br>**************************<br><br>CREATE FUNCTION<br>********* QUERY **********<br>select __temp_fn_create_schema_version();<br>**************************<br><br> <br>
<br>********* QUERY **********<br>DROP FUNCTION __temp_fn_create_schema_version();<br>**************************<br><br>DROP FUNCTION<br>********* QUERY **********<br>CREATE OR REPLACE FUNCTION __temp_Upgrade_add_job_table()<br>
RETURNS void<br>AS $function$<br>BEGIN<br> IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name ILIKE 'job') THEN<br><br> -- Add the job table.<br> CREATE TABLE job<br> (<br>
job_id UUID NOT NULL,<br> action_type VARCHAR(50) NOT NULL,<br> description TEXT NOT NULL,<br> status VARCHAR(32) NOT NULL,<br> owner_id UUID,<br> visible BOOLEAN NOT NULL DEFAULT true,<br>
start_time TIMESTAMP WITH TIME ZONE NOT NULL,<br> end_time TIMESTAMP WITH TIME ZONE default NULL,<br> last_update_time TIMESTAMP WITH TIME ZONE default NULL,<br> correlation_id VARCHAR(50) NOT NULL,<br>
CONSTRAINT pk_jobs PRIMARY KEY(job_id)<br> )<br> WITH OIDS;<br><br> END IF;<br>END; $function$<br>LANGUAGE plpgsql;<br>**************************<br><br>CREATE FUNCTION<br>********* QUERY **********<br>
SELECT * FROM __temp_Upgrade_add_job_table();<br>**************************<br><br> <br><br>********* QUERY **********<br>DROP FUNCTION __temp_Upgrade_add_job_table();<br>**************************<br><br>DROP FUNCTION<br>
********* QUERY **********<br>insert into schema_version(version,script,checksum,installed_by,started_at,ended_at,state,current,comment)<br> values (trim('03010260'),'upgrade/03_01_0260_add_job_table.sql','c74d309cfb8f9d802e54f2ff66d560b4','postgres',<br>
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,'');<br>**************************<br>
<br>********* QUERY **********<br>create or replace function __temp_fn_create_schema_version ()<br>returns void<br>AS $procedure$<br>begin<br>if not exists (select 1 from information_schema.tables where table_name = 'schema_version') then<br>
CREATE SEQUENCE schema_version_seq INCREMENT BY 1 START WITH 1;<br> CREATE TABLE schema_version<br> (<br> id INTEGER DEFAULT NEXTVAL('schema_version_seq') NOT NULL,<br> "version" varchar(10) NOT NULL,<br>
script varchar(255) NOT NULL,<br> checksum varchar(128),<br> installed_by varchar(30) NOT NULL,<br> started_at timestamp DEFAULT now(),<br> ended_at timestamp ,<br> state character varying(15) NOT NULL,<br>
"current" boolean NOT NULL,<br> CONSTRAINT schema_version_primary_key PRIMARY KEY (id)<br> );<br><br> insert into schema_version(version,script,checksum,installed_by,ended_at,state,current)<br> values ('03000000','upgrade/03_00_0000_add_schema_version.sql','0','postgres',now(),'INSTALLED',true);<br>
end if;<br><br>END; $procedure$<br>LANGUAGE plpgsql;<br>**************************<br><br>CREATE FUNCTION<br>********* QUERY **********<br>select __temp_fn_create_schema_version();<br>**************************<br><br> <br>
<br>********* QUERY **********<br>DROP FUNCTION __temp_fn_create_schema_version();<br>**************************<br><br>DROP FUNCTION<br>********* QUERY **********<br>CREATE OR REPLACE FUNCTION __temp_Upgrade_add_job_table()<br>
RETURNS void<br>AS $function$<br>BEGIN<br> IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name ILIKE 'job') THEN<br><br> -- Add the job table.<br> CREATE TABLE job<br> (<br>
job_id UUID NOT NULL,<br> action_type VARCHAR(50) NOT NULL,<br> description TEXT NOT NULL,<br> status VARCHAR(32) NOT NULL,<br> owner_id UUID,<br> visible BOOLEAN NOT NULL DEFAULT true,<br>
start_time TIMESTAMP WITH TIME ZONE NOT NULL,<br> end_time TIMESTAMP WITH TIME ZONE default NULL,<br> last_update_time TIMESTAMP WITH TIME ZONE default NULL,<br> correlation_id VARCHAR(50) NOT NULL,<br>
CONSTRAINT pk_jobs PRIMARY KEY(job_id)<br> )<br> WITH OIDS;<br><br> END IF;<br>END; $function$<br>LANGUAGE plpgsql;<br>**************************<br><br>CREATE FUNCTION<br>********* QUERY **********<br>
SELECT * FROM __temp_Upgrade_add_job_table();<br>**************************<br><br> <br><br>********* QUERY **********<br>DROP FUNCTION __temp_Upgrade_add_job_table();<br>**************************<br><br>DROP FUNCTION<br>
********* QUERY **********<br>insert into schema_version(version,script,checksum,installed_by,started_at,ended_at,state,current,comment)<br> values (trim('03010260'),'upgrade/03_01_0260_add_job_table.sql','c74d309cfb8f9d802e54f2ff66d560b4','postgres',<br>
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,'');<br>**************************<br>
<br><br><br><div class="gmail_quote">On Thu, Jul 19, 2012 at 2:26 PM, Dead Horse <span dir="ltr"><<a href="mailto:deadhorseconsulting@gmail.com" target="_blank">deadhorseconsulting@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Eli,<br>I am following the procedure for DB backup/restore from<br>here: <a href="http://wiki.ovirt.org/wiki/Backup_engine_db" target="_blank">http://wiki.ovirt.org/wiki/Backup_engine_db</a><br>and<br>here: <a href="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" target="_blank">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</a><br>
(Which the RHEV proceadure BTW has a syntax error):<br>"psql -U postgres -d rhevm -W -f /usr/share/rhevm/db-backups/dump_RHEVDB_BACKUP_`date "+%Y%m%d_%R"`.sql"<br>should be:<br>psql -U postgres -W -f /usr/share/rhevm/db-backups/dump_RHEVDB_BACKUP_`date "+%Y%m%d_%R"`.sql<br>
<br>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.<br>
<br>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<br>
<br>Similarly I am using "/bin/dropdb" to destroy/delete the engine database.<br><br>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".<br>
<br>- DHC<div class="HOEnZb"><div class="h5"><br><br><div class="gmail_quote">On Thu, Jul 19, 2012 at 7:04 AM, Robert Middleswarth <span dir="ltr"><<a href="mailto:robert@middleswarth.net" target="_blank">robert@middleswarth.net</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div>On 07/19/2012 04:10 AM, Eli Mesika wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
----- Original Message -----<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
From: "Dead Horse" <<a href="mailto:deadhorseconsulting@gmail.com" target="_blank">deadhorseconsulting@gmail.com</a><u></u>><br>
To: <a href="mailto:users@ovirt.org" target="_blank">users@ovirt.org</a><br>
Sent: Thursday, July 19, 2012 2:22:10 AM<br>
Subject: Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue<br>
<br>
<br>
Forgot reply-all sending to ovirt-users as well.<br>
-DHC<br>
<br>
<br>
On Wed, Jul 18, 2012 at 6:03 PM, Dead Horse <<br>
<a href="mailto:deadhorseconsulting@gmail.com" target="_blank">deadhorseconsulting@gmail.com</a> > wrote:<br>
<br>
<br>
3.0 engine database dump attached as: "engine.sql.tar.gz"<br>
- DHC<br>
<br>
<br>
<br>
<br>
On Wed, Jul 18, 2012 at 1:05 PM, Eli Mesika < <a href="mailto:emesika@redhat.com" target="_blank">emesika@redhat.com</a> ><br>
wrote:<br>
<br>
<br>
<br>
<br>
<br>
<br>
----- Original Message -----<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
From: "Dead Horse" < <a href="mailto:deadhorseconsulting@gmail.com" target="_blank">deadhorseconsulting@gmail.com</a> ><br>
To: <a href="mailto:Users@ovirt.org" target="_blank">Users@ovirt.org</a><br>
Sent: Wednesday, July 18, 2012 9:08:55 AM<br>
Subject: [Users] ovirt-engine upgrade 3.0 to 3.1 issue<br>
<br>
<br>
Steps taken:<br>
<br>
Load up bare metal or a VM with FC16<br>
Install 3.0 from <a href="http://www.ovirt.org/releases/stable/fedora/16/" target="_blank">http://www.ovirt.org/releases/<u></u>stable/fedora/16/</a><br>
Setup up something minimal (EG: engine-setup then setup a basic<br>
datacenter/cluster/etc)<br>
Add a FC16 or EL based node for fun as well and some VM's if<br>
feeling<br>
ambitious.<br>
<br>
Back up database:<br>
systemctl stop jboss-as.service<br>
pg_dump -C -E UTF8 --column-inserts --disable-dollar-quoting<br>
--disable-triggers -U postgres --format=p -f "/temp/engine.sql"<br>
engine<br>
<br>
Back up the following files within /etc/pki/ovirt-engine from a 3.0<br>
install (preserve owner/group).<br>
/etc/pki/ovirt-engine/cacert.<u></u>conf<br>
/etc/pki/ovirt-engine/ca.pem<br>
/etc/pki/ovirt-engine/cert.<u></u>conf<br>
/etc/pki/ovirt-engine/certs/<u></u>01.pem<br>
/etc/pki/ovirt-engine/certs/<u></u>02.pem<br>
/etc/pki/ovirt-engine/certs/<u></u>ca.der<br>
/etc/pki/ovirt-engine/certs/<u></u>engine.cer<br>
/etc/pki/ovirt-engine/certs/<u></u>engine.der<br>
/etc/pki/ovirt-engine/<u></u>database.txt<br>
/etc/pki/ovirt-engine/<u></u>database.txt.attr<br>
/etc/pki/ovirt-engine/<u></u>database.txt.attr.old<br>
/etc/pki/ovirt-engine/<u></u>database.txt.old<br>
/etc/pki/ovirt-engine/keys/<u></u>engine_id_rsa<br>
/etc/pki/ovirt-engine/keys/<u></u>engine.ssh.key.txt<br>
/etc/pki/ovirt-engine/private/<u></u>ca.pem<br>
/etc/pki/ovirt-engine/<u></u>requests/ca.csr<br>
/etc/pki/ovirt-engine/<u></u>requests/engine.req<br>
<br>
Load up bare metal or a VM with FC17 (Upgrade from FC16 --> FC17 is<br>
still rather messy)<br>
Install 3.1 from <a href="http://www.ovirt.org/releases/beta/fedora/17/" target="_blank">http://www.ovirt.org/releases/<u></u>beta/fedora/17/</a><br>
<br>
Run engine-setup to get an initial setup.<br>
stop ovirt-engine (systemctl stop ovirt-engine.service)<br>
<br>
Copy the previously backed up /etc/pki/ovirt-engine files from<br>
above<br>
over top of the ones just generated (be sure to preserve<br>
owner/group<br>
EG: cp -a)<br>
<br>
Drop the existing engine database: dropdb -U postgres engine<br>
Create a new blank database: createdb -U postgres engine<br>
</blockquote></blockquote>
Well, the problem is that you are creating here the database with createdb.<br>
This creates all DB objects and runs all upgrade scripts.<br>
Then when you try to restore from your file , you have problems, since<br>
your file also tries to create the same objects.<br>
instead of : createdb -U postgres engine<br>
do :<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
echo "create database engine;" | psql -U postgres template1<br>
</blockquote>
Then continue on the next steps and it will work.<br>
<br>
optionally, in your backup command:<br>
instead of<br>
pg_dump -C -E UTF8 --column-inserts --disable-dollar-quoting<br>
use<br>
pg_dump --data-only -E UTF8 --column-inserts --disable-dollar-quoting<br>
<br>
then , createdb will create the db & schema and your engine.sql will include only "insert"<br>
<br>
Eli<br>
</blockquote></div></div>
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.<br>
<br>
Thanks<span><font color="#888888"><br>
Robert</font></span><div><div><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Populate with with the backup of the above 3.0 database:<br>
psql -U postgres -d engine -w < "/temp/engine.sql"<br>
</blockquote>
Can you please attach the engine.sql file so I will be able to<br>
reproduce , thanks<br>
<br>
<br>
<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Attempt to upgrade the DBschema via<br>
/usr/share/ovirt-engine/<u></u>dbscripts/upgrade.sh<br>
upgrade.sh -u postgres<br>
<br>
The DB schema upgrade will fail like so:<br>
<br>
Running upgrade script upgrade/03_01_0260_add_job_<u></u>table.sql<br>
psql:upgrade/03_01_0260_add_<u></u>job_table.sql:29: NOTICE: CREATE TABLE<br>
/<br>
PRIMARY KEY will create implicit index "pk_jobs" for table "job"<br>
CONTEXT: SQL statement "CREATE TABLE job<br>
(<br>
job_id UUID NOT NULL,<br>
action_type VARCHAR(50) NOT NULL,<br>
description TEXT NOT NULL,<br>
status VARCHAR(32) NOT NULL,<br>
owner_id UUID,<br>
visible BOOLEAN NOT NULL DEFAULT true,<br>
start_time TIMESTAMP WITH TIME ZONE NOT NULL,<br>
end_time TIMESTAMP WITH TIME ZONE default NULL,<br>
last_update_time TIMESTAMP WITH TIME ZONE default NULL,<br>
correlation_id VARCHAR(50) NOT NULL,<br>
CONSTRAINT pk_jobs PRIMARY KEY(job_id)<br>
)<br>
WITH OIDS"<br>
PL/pgSQL function "__temp_upgrade_add_job_table" line 6 at SQL<br>
statement<br>
psql:/tmp/tmp.mXz8U4xpWr:3: ERROR: column "comment" of relation<br>
"schema_version" does not exist<br>
LINE 1:<br>
...ksum,installed_by,started_<u></u>at,ended_at,state,current,<u></u>comment)<br>
<br>
My assumption here is that if the DB schema upgrade would work,<br>
that<br>
this should be the cleanest way to upgrade from 3.0 to 3.1.<br>
<br>
- DHC<br>
<br>
______________________________<u></u>_________________<br>
Users mailing list<br>
<a href="mailto:Users@ovirt.org" target="_blank">Users@ovirt.org</a><br>
<a href="http://lists.ovirt.org/mailman/listinfo/users" target="_blank">http://lists.ovirt.org/<u></u>mailman/listinfo/users</a><br>
<br>
</blockquote>
<br>
<br>
______________________________<u></u>_________________<br>
Users mailing list<br>
<a href="mailto:Users@ovirt.org" target="_blank">Users@ovirt.org</a><br>
<a href="http://lists.ovirt.org/mailman/listinfo/users" target="_blank">http://lists.ovirt.org/<u></u>mailman/listinfo/users</a><br>
<br>
</blockquote>
______________________________<u></u>_________________<br>
Users mailing list<br>
<a href="mailto:Users@ovirt.org" target="_blank">Users@ovirt.org</a><br>
<a href="http://lists.ovirt.org/mailman/listinfo/users" target="_blank">http://lists.ovirt.org/<u></u>mailman/listinfo/users</a><br>
</blockquote>
<br>
</div></div></blockquote></div><br>
</div></div></blockquote></div><br>