Upgrading

Database upgrade

In general you should follow the installation instructions.

However, assuming you want to keep your old Wiki data intact, you will need to upgrade the database schema without modifying the data that the database contains.

There are two general approaches to this:

  1. Examine the differences between the two schemas by hand and run the appropriate ALTER TABLE statements by hand. This is the way to go if you're a database expert.
  2. Dump out the data, recreate the database with the new schema, and reload the data. This method is more automatic, but there are potentially some perils if the two schemas are radically different.

Before trying any change, it is vital that you back up your database.

Method 1: Difference between schemas

If you're familiar with basic database administration, then the simplest method is to use pg_dump to dump out your existing schema, and compare it to the new schema (supplied in the file cocanwiki.sql in the source distribution) using diff.

$ pg_dump --no-owner -s cocanwiki > cocanwiki.sql.old
$ diff -u cocanwiki.sql.old cocanwiki.sql | less

The diff output will tell you which tables have changed, which new indexes have been created and so on. You should work out and issue the appropriate ALTER TABLE statements and issue them to the database.

Repeat the process until the diff output consists of only trivial changes. (PostgreSQL will always show OID differences, which are unimportant).

Method 2: Dump and reload the data

Update: Actually, I tried this method and it doesn't work. The fundamental problem is that pg_dump -a doesn't write out the tables in a sensible order, so you would have to drop and recreate all the constraints around the load operation. This makes it non-trivial. Method 1 above does work, so use that instead. -- Richard W.M. Jones

Almost every change made to the COCANWIKI schema is backwards compatible - normally the addition of tables, NULLable columns and indexes. This makes it practical to dump out the old data, drop the old database and create a new one from the new schema, then import the old data. This should work in almost every case, but where it fails, it requires manual intervention to fix.

Dump out the old data using:

$ pg_dump -a cocanwiki > cocanwiki.data

Drop the old database and create a new clean database:

$ dropdb cocanwiki
$ createdb -E UNICODE cocanwiki
$ psql cocanwiki < cocanwiki.sql

Try to load the old data:

$ psql cocanwiki < cocanwiki.data

Check each step carefully for errors.