Weblog

Upgrade from PostgreSQL 9.6 to 13.1

Created by Gustaf Neumann, last modified by Gustaf Neumann 08 Oct 2021, at 09:57 PM

Here is a summary of steps to upgrade a legacy OpenACS installation (recent OpenACS, but old PostgreSQL version). In general it is possible to restore a "pg_dump" of some older version in newer versions of PostgreSQL. It is not necessary, to upgrade in small steps, version by version, but one can also skip major versions, when keeping certain things in mind. The upgrade on openacs.org was from PostgreSQL 9.6.4 to 13.1.

OIDs

Newer versions of PostgreSQL do not support OIDs anymore. OpenACS does not need OIDs in its queries, but one has to create a pg_dump without OIDs first. This can be achieved by the following commands.

 #
 # Settings of old PostgreSQL installation
 #
 PGBIN=/usr/local/pg964/bin
 PGPORT=5496

 #
 # Database from where OIDs should be removed
 #
 DB=openacs.org

 for tbl in `$PGBIN/psql -p $PGPORT -U postgres -qAt -c "select schemaname || '.' || tablename from pg_tables WHERE schemaname <> 'pg_catalog' AND schemaname <> 'information_schema';" $DB`
 do
    $PGBIN/psql -U postgres -c "alter table $tbl SET WITHOUT OIDS" $DB
 done

tsearch2

tsearch2 was an extension in PostgreSQL versions before version 10, but was dropped as an extension at this time, being now integral part of newer PostgreSQL versions. Users who have been continuously upgrading since a long time (from before 8.3) have to manually modify their databases by stripping leftovers from the extension. Otherwise, loading the dump will raise errors doe to missing tsearch2 functions in new versions (e.g. "ERROR: function "dex_init(internal)" does not exist").

By excluding the "pg_ts_*" table, the dump can be imported (although it will complain about the missing tsearch2 module). Nevertheless, the imported dump will be fully functional. 

Dump from PostgreSQL 9.6

Here is, what i've used on openacs.org for dumping. For your local installation, you have to replace the database names in the for loop by the names that you use.

  # 
  # Use variable DATE to disambiguate multiple dumps in the same directory 
  #
  DATE=`date +"%Y-%m-%d"`

  for DB in 5-9-1 dotlrn-test fisheye oacs-5-8 openacs.org openacs.org-test translate wi1.org wi1.org-test
  do
    echo "dumping to ~/$DB.dump.$DATE.gz"
    $PGBIN/pg_dump -p $PGPORT -U postgres --exclude-table=public.pg_ts_\* $DB | gzip > ~/$DB.dump.$DATE.gz
  done

Restore in PostgreSQL 13

After this, restoring went smooth:

  #
  # Adjust settings to your pg13 installation
  #
  export PG13=pg13
  export PGBIN13=/usr/local/${PG13}/bin
  export PGPORT13=5413

  for DB in 5-9-1 dotlrn-test oacs-5-8 openacs.org openacs.org-test translate wi1.org  wi1.org-test
  do
    echo "reloading from ~/$DB.dump.$DATE.gz"
    $PGBIN13/dropdb -p $PGPORT13 -U nsadmin $DB
    $PGBIN13/createdb -p $PGPORT13 -U openacs -T template0 $DB
    $PGBIN13/psql -p $PGPORT13 -U openacs $DB -tAc "create extension ltree"
    gunzip < ~/$DB.dump.$DATE.gz | $PGBIN13/psql -p $PGPORT13 -U nsadmin $DB > ~/$DB.log
  done

You might not need the line with the ltree extension in your installation.

Upgrade from PostgreSQL 13 to PostgreSQL 14

Just dump and restore.

OpenACS compatibility matrix

Created by Joel Aufrecht, last modified by Gustaf Neumann 22 Mar 2019, at 12:10 PM

OpenACS requires, at a minimum, an operating system, database, and web server to work. Many additional programs, such as a build environment, Mail Transport Agent, and source control system, are also needed for a fully effective installation.

Table 2.2. Version Compatibility Matrix

The value in the cells correspond to the last version of that release, and not necessarily to all minor releases. Empty cells denote an unknown status.

Upgrade to OpenACS 5.8

Created by Gustaf Neumann, last modified by Michael Aram 24 Apr 2017, at 05:15 PM

OpenACS (5.8) works with PostgreSQL 9.1 or newer out of the box, no special configurations in postgresql.conf are needed like with previous versions.

To work with PostgreSQL 9, one has to use an actual postgres driver:

• AOLserver 4.5.* + nspostgres (tag: nspostgres_v4_r2)
  http://aolserver.cvs.sourceforge.net/viewvc/aolserver/nspostgres/
   
• NaviServer 4.99.5 (or newer) + nsdbpg
  https://bitbucket.org/naviserver/naviserver/overview
  https://bitbucket.org/naviserver/nsdbpg

OpenACS core + commonly used packages (search, forums, xowiki, ...) have been tested with PostgreSQL 9.2.4

For new installs, OpenACS 5.8 works without further considerations. When upgrading the database to PostgreSQL 9.*, one has to keep in mind, that not only the sql-install scripts have to be SQL 9.* compatible, but as well the update scripts (migration scripts). During the work for making OpenACS compatible with PostgreSQL 9.*, we did not update (all) of the migration scripts (e.g. kernel upgrades) of earlier versions, therefore one should run the upgrade scripts to OpenACS 5.7 with an PostgresSQL 8.* database.

Therefore, the following upgrade  steps are recommended for upgrading from OpenACS 5.5 to 5.7 to OpenACS 5.8: 

• For users of PostgreSQL versions earlier than 8.4:  In case you run a version of OpenACS earlier than 5.5 then upgrade first your code to OpenACS 5.5 (oacs-5-5 branch from the CVS Repository), then dump your database and reload it into PostgreSQL 8.4 (e.g. into PostgreSQL 8.4.22, the end-of-life of which was in July 2014). Then continue with the next step below.
• For users of PostgreSQL version 8.4 (or newer before 9): Make sure, you are running Tcl 8.5, then get OpenACS 5.8 (not 5.9) and upgrade OpenACS and your used packages (e.g. via acs-admin/apm + "install packages"). Then dump the database and restore it in PostgreSQL 9.*.

Upgrade between PostgreSQL versions

Created by Gustaf Neumann, last modified by Gustaf Neumann 20 Sep 2016, at 02:06 PM

The probably easiest way to upgrade between PostgreSQL versions is as follows (described here from 9.4 to 9.5, based on the directory structure as provided by Mac Ports)

DATE=`date +"%Y-%m-%d"`
# Dump all databases from pg 9.4
PGBIN=/opt/local/lib/postgresql94/bin
sudo -u postgres $PGBIN/pg_dumpall | gzip -9 > ~/pg-dump-all-$DATE.gz

Then stop pg 9.4, start pg 9.5, then reload the dump:

# Restore all databases and roles from dump into pg 9.5
PGBIN=/opt/local/lib/postgresql95/bin
gunzip < ~/pg-dump-all-$DATE.gz |  $PGBIN/psql -U postgres

voilà, start OpenACS, and everything works fine.

In case there is a problem with restoring the dump in PostgreSQL 9.5 due to the "=>" operator of hstore, one has to update the version of hstore at least to 1.1 before dump/restore (the current version of hstore is 1.4; for details see hstore-upgrade).

In case, you want to upgrade your legacy OpenACS installation from PostgreSQL 8.* to 9, read upgrade-oacs-5-8.

Postgres 8.1.x to 9.1

Created by Rocael Hernández Rizzardini, last modified by Gustaf Neumann 08 Jul 2016, at 02:25 PM

PostgreSQL 8.x (e.g. 8.1.* and later up to 9.1) require a few changes in the configuration file for backward compatibility (see below).

Note that OpenACS 5.8 or newer works with PostgreSQL 9.1 (or newer) out of the box, the special configurations for postgresql.conf described below are not needed anymore.

1. Configure postgres8 with all compatibility ON in postgresql.conf:

 add_missing_from = on 
 regex_flavor = extended 
 default_with_oids = on

2. If you are upgrading an OpenACS site between versions 4.5.2 to 5.2 and not continuing to 5.3, then: After the createdb step to create the OpenACS database, generate the next function before you import the pre-existing OpenACS database.

From your shell prompt enter:

psql <dbname>

From the psql prompt enter the follow plpgsql code to create the bitfromint4 function:

 

 create or replace function bitfromint4 (integer) 
 returns bit varying as ' 
 begin 
    return $1::bit(32); 
 end;' language 'plpgsql' immutable strict; 

Exit psql:

\q 

This is the original thread 

For a full script on how to install PG 8.2 with ltree and tsearch2 look at Malte's install script 

hstore

Created by Michael Aram, last modified by Gustaf Neumann 12 Apr 2016, at 10:39 AM

hstore is a postgresql module, which is used optionally by newer versions of xowiki and xowf (xowiki content flow) to provide quick access to the "instance_attributes" (the per-form or per-workflow attributes not part of the native content repository data model). The supporting functions (creating indices, etc) are currently mostly part of the xowiki sources (earlier: xowf).

Under Ubuntu, you can install hstore via:

    psql -d <yourdb> -tAc "create extension hstore"

You can then verify the successful installation on the ds/shell using:

    ::xo::db::has_hstore