The PostgreSQL switch

WebHare switched from its builtin database server ('dbserver') to PostgreSQL in the 4.x versions. You need to complete the switchover to PostgreSQL before updating to WebHare 5.0 or higher. 

You can already start preparing for the switchover to make the switch as painless as possible (even though WebHare will keep support for both database servers for a while). Here's what you should do:

Stay up-to-date!

WebHare 4.26 is an unskippable upgrade so you should work towards updating all your servers to this version.

PostgreSQL will be a lot stricter than dbserver about text in the database and will generally reject invalid UTF-8 data unless a column is explicitly declared as binary. WebHare will try to avoid declaring binary columns as much as possible as invalid UTF-8 data is painful to work with.. and if we can avoid inserting it into the database and troublesome data will be stopped at insertion.

You should scan your installation for invalid UTF-8 data and fix it. Often you can just strip out incorrect data and reinsert it - if it was meant to be text, it was probably already corrupted anyway.

One source of invalid UTF-8 data are old hashed passwords which contained raw binary data. These may be present in WRD data, and you should scan your existing schemas using 'wh wrd:scannonutf8' (added in 4.26)

WebHare 4.27 adds a lot of deprecations for dbserver-specific code. Make sure you 'wh checkmodule' all your modules to find these warnings. 

Migration

Before you start migrating, you should:

• Ensure all your modules are up to date, especially if they use custom tables (schema definitions must properly mark NULL and BINARY columns)
• If you manually dropped tables before conversion, softreset first to make sure all rights tables are in sync

Dry run

We recommed doing a dry-run of the migration first. A dryrun will generate the postgres tables but not move then into place for a switchover:

Any records with invalid UTF-8 data will be printed.You may consider whether you can fix them or just accept a best-effort ISO-8859-15 -> UTF-8 for these fields. If some fields are still incorrect even after this fix, migration will fail.

Any converted cells will have their original values saved to webhare_internal.postgresql_migration_issues after the final migration.

Running the conversion

To actually do the conversion, remove the --dryrun parameter. This will prepare the postgres tables and WebHare will switch over to postgres database once it's restarted. If you're using docker and want to automatically restart after the conversion is done:

The time migration takes may vary (do a dry-run to have an estimate) and has taken anywhere between 5 seconds and 15 minutes for our own servers - database size and I/O speed matters a lot of course. 

We recommend switching to readonly mode during migration, and accepting that this may break some webpages or applications which try to write to the database. If you do not disable database writes, you will lose any changes that were made since the start of the migration.

You can always revert back to the old database engine by deleting the /opt/whdata/postgresql directory and restarting WebHare, but you will lose all changes made since the migration. If you are satisfied with the results of your migration and have verified your backup/restore procedures, you can delete /opt/whdata/dbase.

After the conversion

We recommend upgrading to 4.35 as soon as possible.