View · Index

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 was from PostgreSQL 9.6.4 to 13.1.


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

 # Database from where OIDs should be removed

 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`
    $PGBIN/psql -U postgres -c "alter table $tbl SET WITHOUT OIDS" $DB



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 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 translate
    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

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 translate
    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

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

Upgrade from PostgreSQL 13 to PostgreSQL 14

Just dump and restore.

previous September 2023
Sun Mon Tue Wed Thu Fri Sat
27 28 29 30 31 1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

Popular tags

17 , 5.10 , 5.10.0 , 5.9.0 , 5.9.1 , ad_form , ADP , ajax , aolserver , asynchronous , bgdelivery , bootstrap , bugtracker , CentOS , COMET , compatibility , CSP , CSRF , cvs , debian , docker , docker-compose , emacs , engineering-standards , exec , fedora , FreeBSD , guidelines , host-node-map , hstore
No registered users in community xowiki
in last 30 minutes