Forum OpenACS Development: OpenACS 5.8(head) on Postgres 9.2

Posted by Keith Paskett on 10/09/12 05:56 AM

We are in the process of upgrading several OpenACS sites from v5.6 on Postgres 8.4 to 5.8(head) on Postgres 9.2.

We are finding that some of the upgrade scripts between 5.6 and 5.8 don't work or don't work properly under Postgres 9.2. Upgrade scripts don't end up giving you the same thing as the create scripts.

What is the Target Postgres version for OpenACS 5.8?

We are only a couple of days into it, and will post fixes when we get to a fairly stable tested state.

Is posting a patch on the bug tracker still the preferred way to contribute fixes?

2: Re: OpenACS 5.8(head) on Postgres 9.2 (response to 1)

Posted by Gustaf Neumann on 10/09/12 10:01 AM

openacs-core on cvs-head (aka 5.8 development) was tested with postgres 9.1. We are using OpenACS with 9.1 on production with the set of packages we are needing (100+ packages, many of these developed inhouse).

so, if you could provide patches / fixed update-scripts via the issue tracker, it would be great.

Our target for 5.8 is to get the recursive query processing in and to provide a 5.8 release rather soon.

Based on our current experiences i would still recommend 9.1 rather than 9.2 (but looking at the changes, 9.2 should work as well, but the thorough testing is missing - at least from our side).

-gustaf neumann

3: Re: OpenACS 5.8(head) on Postgres 9.2 (response to 1)

Posted by Jim Lynch on 10/25/12 08:58 PM

Gustaf (or anyone who knows),

If I build pg-9,1 or 9.2, does openacs-5.7 or 5.8 require changes to pg's config files? if so, what are they?

-Jim

4: Re: OpenACS 5.8(head) on Postgres 9.2 (response to 3)

Posted by Gustaf Neumann on 10/29/12 09:33 AM

We have done no testing with pg9.2 (which might work or not). As i wrote, the changes for pg9.1 are in oacs-head, not in oacs-5.7.

With the head version, postgres' default pg config file works (i use e.g. on mac os x the mac ports version). In case you are migrating an old postgres config file, your have to remove add_missing_from and regex_flavor, since these flags are gone since 9.0 (http://www.postgresql.org/docs/9.0/static/release-9-0.html).

You will have to use a recent version of nspostgres:
http://aolserver.cvs.sourceforge.net/viewvc/aolserver/nspostgres/

5: Re: OpenACS 5.8(head) on Postgres 9.2 (response to 4)

Posted by Jim Lynch on 10/29/12 10:55 AM

Gustaf,

So that takes care of two of the three postgresql.conf settings... what about the third, which involves whether to use oids? The pg folks have deprecated oids, presumably pg devs want to see them gone soon so it probably makes sense to at least see about removing dependence on them.

How big of a job is that? Other comments?

6: Re: OpenACS 5.8(head) on Postgres 9.2 (response to 4)

Posted by Keith Paskett on 10/29/12 11:17 PM

I have switched all of our development servers to Postgres 9.2, using OpenACS head. There are a number for 9.2 features we really want to start using, like range types, as well as significant performance improvements. We haven't had any issues with OpenACS core packages on PG 9.2. The only other packages we have used are acs-developer-support, acs-events, and acs-datetime

7: Re: OpenACS 5.8(head) on Postgres 9.2 (response to 5)

Posted by Gustaf Neumann on 10/31/12 09:53 AM

How big of a job is that? Other comments?

For most purposes in OpenACS oids are not needed. A quick grep returned the following places:

acs-content-repository/sql/postgresql/content-type.sql
acs-kernel/sql/postgresql/acs-metadata-create.sql
acs-kernel/sql/postgresql/postgresql.sql
acs-tcl/tcl/test/datamodel-test-procs.tcl
acs-subsite/www/admin/users/new-postgresql.xql 
acs-subsite/www/pvt/alerts-postgresql.xql

The config flag default_with_oids is just used for creation of new tables. So if one migrates an existing database to e.g. PG 9*, the oids are as well migrated, and one does not need the flag in the config file.

I am not sure that pg wants to get rid of OIDs, since these are used as PKs for various system tables (see datatype-oid).

For new installations, if the goal is to remove the config dependency, the simplest fix is probably to add WITH OIDS to the few tables, that still require OIDs.

8: Re: OpenACS 5.8(head) on Postgres 9.2 (response to 6)

Posted by Gustaf Neumann on 10/31/12 09:54 AM

Keith, good to hear that!

9: Re: OpenACS 5.8(head) on Postgres 9.2 (response to 1)

Posted by Claudio Pasolini on 11/04/12 04:43 PM

After installing postgresql-9.2.1, naviserver-4.99.4d and OpenACS Head I upgraded one site from OpenACS 6.

At first the process did not work because the acs-kernel upgrade script 'upgrade-5.7.0d8-5.7.0d9.sql' failed creating the function 'get_func_definition'.

In my case the solution was simple, because the following upgrade script 'upgrade-5.8.0d1-5.8.0d2.sql' contains a new valid definition of the same function.

So, if one is upgrading to 5.8, he could safely remove the wrong function from the 'upgrade-5.7.0d8-5.7.0d9.sql' script.

10: Re: OpenACS 5.8(head) on Postgres 9.2 (response to 1)

Posted by Dave Bauer on 11/04/12 04:51 PM

That will work.

In general you need to upgrade your database to the lastest compatible version for the OpenACS version that corresponds.

For example you'd upgrade OpenACS to 5.7 then upgrade PostgreSQL to PG 8.4.

Then you can upgrade the database to 9.x and upgrade OpenACS to 5.8 (HEAD).

Of course if you don't mind editing the upgrade scripts so they run cleanly you should be fine either way.

Due to the way Postgresql handles backwards compatibility (or doesn't...) this is the only technique that works reliably.