Forum OpenACS Q&A: Re: 4.6 upgrade questions

Collapse
4: Re: 4.6 upgrade questions (response to 3)
Posted by Roberto Mello on 01/20/03 08:20 PM
BTW, if you are planning on moving your 4.5 databases to PG 7.2.3, to then upgrade to OpenACS 4.6, you should really read this document I wrote as a result of moving openacs.org from PG 7.1 to 7.2.3:

https://openacs.org/doc/misc/openacs-pg-migration.txt

-Roberto

Collapse
5: Re: 4.6 upgrade questions (response to 4)
Posted by Kevin Murphy on 01/21/03 03:51 AM
Roberto,

Thanks for the info.  Re: openacs-pg-migration.txt, I have some more questions:

<blockquote> 6.2. Edit dump and remove the creation of all PL/pgSQL handlers
6.3. Also remove all statements that create int_* functions.
</blockquote>

Can you be more specific, and does it really matter if these are removed from the dump if they are being substituted for by commands in _int.sql and postgresql.sql?

I assume I should do the following:

*) Get rid of the creation of plpgsql_call_handler().  I guess I should also get rid of the creation of the procedural language plpgsql also?

*) I'm a bit confused about the second one.  Looking at _int.sql, I see a bunch of functions that don't occur in my dump at all.  Also, in postgresql.sql, I see various tree/key functions, the first of which happens to be int_to_tree_key().  Am I supposed to get rid of all the key and tree functions in the dump?

Thanks,
Kevin Murphy

Collapse
6: Re: 4.6 upgrade questions (response to 4)
Posted by Kevin Murphy on 01/21/03 05:02 AM
Roberto et al,

When I loaded my dump (from which I removed just the two handler statements), I got the following unexplained error:

psql:media_db.dmp:731: ERROR:  No such attribute or function 'oid'

The relevant statement is this:

CREATE VIEW "user_col_comments" as SELECT upper(text(c.relname)) AS table_name, upper(text(a.attname)) AS column_name, d.description AS comments FROM pg_class c, (pg\
_attribute a LEFT JOIN pg_description d ON ((a.oid = d.objoid))) WHERE ((c.oid = a.attrelid) AND (a.attnum > 0));

What should I do about this?  The definition of the 7.1 user_col_comments view used by the view creation function in postgresql.org also doesn't work because of the 'missing' oid column.

I am still quite the openacs newbie, so I am out of my element here.  I'm lucky just to know how to do "find /web -name "*.sql" | xargs grep whatever".

Thanks,
Kevin

Collapse
7: Re: 4.6 upgrade questions (response to 6)
Posted by Roberto Mello on 01/21/03 10:00 PM
Kevin,

It's probably something I overlooked when writing my pg-migration document.

user_col_comments is defined in packages/acs-kernel/sql/postgresql/postgresql.sql which you should have loaded before starting the migration (as per the migration doc).

So you should get an error, but the error should be that that view is already defined. If you're getting a different error, perhaps you forgot to load the postgresql.sql file.

-Roberto

Collapse
8: Re: 4.6 upgrade questions (response to 7)
Posted by Kevin Murphy on 01/22/03 06:26 AM
Roberto,

Sorry to keep pestering.

Here's my error: psql:media-7.1.dmp:752: ERROR:  No such attribute or function 'oid'

I did indeed load postgresql.sql.

When you do:

grep ERROR errors.log | grep -v "already exists"

do you really only get three errors?

I get four:

psql:media-7.1.dmp:752: ERROR:  No such attribute or function 'oid'
psql:media-7.1.dmp:4690: ERROR:  parser: parse error at or near "SELECT"
psql:media-7.1.dmp:4698: ERROR:  parser: parse error at or near "SELECT"
psql:media-7.1.dmp:21336: ERROR:  Relation "party_approved_member_map" does not exist

Here is how I did my stuff.  I have PG 7.1 running on the usual port and PG 7.2 running on port 5424.  I rely on PGPORT to specify the port.  /web/med_dev is the OACS 4.6 tree.  My "media" database and site is OACS 4.5.

EXPORT:

nsadmin@linux:/web/med_dev> which pg_dump
/usr/local/pgsql/bin/pg_dump
nsadmin@linux:/web/med_dev> set | grep PG
nsadmin@linux:/web/med_dev> pg_dump -o media >media-7.1.dmp
nsadmin@linux:/tmp/pg> psql --version
psql (PostgreSQL) 7.1.3

- - -

IMPORT:

nsadmin@linux:/web/med_dev> . ~/pg723.sh
nsadmin@linux:/web/med_dev> set | grep PG
PGDATA=/usr/local/pgsql-7.2.3/data
PGLIB=/usr/local/pgsql-7.2.3/lib
PGPORT=5424
nsadmin@linux:/web/med_dev> export PGPATH=/usr/local/pgsql-7.2.3
nsadmin@linux:/web/med_dev> export PATH=$PGPATH/bin:$PATH
nsadmin@linux:/web/med_dev> which psql
/usr/local/pgsql-7.2.3/bin/psql
nsadmin@linux:/web/med_dev> dropdb med_dev
ERROR:  DROP DATABASE: database "med_dev" does not exist
dropdb: database removal failed
nsadmin@linux:/web/med_dev> createdb -U nsadmin med_dev
CREATE DATABASE
nsadmin@linux:/web/med_dev> psql -U nsadmin -f packages/acs-kernel/sql/postgresql/postgresql.sql med_dev
nsadmin@linux:/web/med_dev> psql -U nsadmin -f media-7.1.dmp med_dev 2> errors.log
nsadmin@linux:/web/med_dev> psql --version
psql (PostgreSQL) 7.2.3

-Kevin Murphy