Database upgrade to version 0.9.35

Whitebeam has used Postgres as the main data-storage mechanism for the last few years. During that time we've kept the database schema constant. With version 0.9.35 we've had to make a few changes. Most of these are fairly rivial and simply require an increase in field size for a few fields.

The biggest change is to remove the use of large objects in the file template. This has been driven primarily by the desire to make use of one of the many Postgres replication schemes. none of the open soure schemes support the replication of large objects.

Rather than change the existing file template, we've created a new file temlate (called file2!). This means that if you're happy with your existing database and don't need replication you can just run the existing template code. Note though that any enhancements to the file template in future will not be included in the original legacy implementation.

File template version 2

This component is a drop in replacement for the original Whitebeam file template. Externally the interface to the Presentation Engine is virtually identical to the original file template.

Internally this version has removed the reliance on Postgres Large Objects (which are not supported by any replication system and which do not seem to sit well with the tandard backup and restore tool provided by Postgres).

Differences from version 1

1. The 'data OID' column in the schema has been replaced with 'data_new BYTEA'. All file template data is now stored in this column. See below for how to migrate from the original schema.
2. We've taken the opportunity to increase the maximum size of files stored in a singgle file object from 150K to 250,000 bytes.

Presentation Interface

The interface to the Presentation via the rb.file object is identical to version 1.

Migration

If you have an existing Whitebeam installation and want to make use of the new file object implementation (and replication) then you need to copy all the data from the existing Large Object storage in files.data into the BYTEA equivalent.

The 'migrate.rhtm' file in this directory will help a little with that, bbut you can alternaitvely make use of any of a number of technologies with a Postgres interface that supports large objects.

Steps::

1. Do a full backup of your Whitebeam database!
2. Add a new BYTEA column to the files table: ALTER TABLE files ADD COLUMN data_new TYPE BYTEA
3. Copy all the large object columns to the new column and delete the large objects.
4. Stop Whitebeam and the templates and build the latest version, including the new file template (file2-pgsql). 'gmake install' will copy both file1 and file2 to the install directory. Change whatever scripts you have to startthe templates so that they start file 2.
5. Run the new installation.
6. If everything is working you can delete the old Large Object OID column from the files table:
      ALTER TABLE files DROP COLUMN data

We've put together a Whitebeam Presentation Page to do most of this for you. It's in the whitebeam/templates/pgsql/file2-pgsql directory (i.e. with the new file template).

The page is called 'migrate.rhtm'. Copy this file to the file tree for an Apache virtual server. You have to have the native Postgres function built (--enable-pgsql in configure) and the virtual server you choose configured to allow access to the Whitebeam database (RBdbase directive in httpd.conf). For details on how to do this take a look at our Postgres tech note..

Assuming you have the virtual server and Whitebeam correctly built and configured you can simply use a web-browser to migrate large object data. The URL takes several obvious parameters:

• host : The host name or IP address of the database server (make sure you can attach to the server - of you have problems check pg_hba.conf in the Postgres data directory).
• dbname : The name of the Postgres database. This is usually 'whitebeam'.
• user : The Postgres username with which to attach to the database. This generally should be the database owner. We usually create a user called 'whitebeam'.
• password : Password for the username (if required).

The page should report no errors. It will create the database column, copy all the data over and delete the large objects. It doesn't do the final DROP of the 'data' column. You have to do that yourself!