Upgrade from PostgreSQL 9.6 to 13.1

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.