Migrate to PostgreSQL

Openfire Openfire Support
1

I just wanted to share my experiences in migrating from embedded-db to PostgreSQL. I use Openfire 3.9.10, on Debian 9 (kernel 3.16, OpenJDK 1.17, PostgreSQL 9.4).

First, an amendment: The “migration” was more like “backup embedded-db, uninstall, reinstall and restore on PSQL”. I couldn’t find any way to make my already configured and working OF installation switch from using embedded-db to PSQL, more’s the pity.

So, this is what I did:

  1. Backup “openfire.script” from embedded-db: As explained on Migration from embedded-db to MySQL, Oracle, MSSQL, … , you should stop OF, and backup the script “openfire.script”, which in my installation were in /usr/share/openfire/embedded-db.
  2. Backup the script “/usr/share/openfire/resources/database/openfire_postgresql.sql”, you will need it later to setup the new database.
  3. Edit “openfire.script”:
  4. Look for the line “SET SCHEMA PUBLIC” and delete everything from the beginning until that line.
  5. psql will not run the script because the statements are not terminated with a semicolon (";"). You need to correct this first, I used this sed snippet: “sed -i -e ‘s=$=;=’ openfire.script”
  6. The script encoding was ASCII, so the first time I ran the script on my PostgreSQL with UTF-8 encoding I got a lot of “\ux000” gibberish. To get the script to show correct UTF-8 chars, I used the perl snippet here: linux - How to convert \uXXXX unicode to UTF-8 using console tools in *nix - Stack Overflow
  7. Remove openfire: On Debian, you could do that with “apt-get --purge remove openfire”, so you nuke all OF configs at the same time.
  8. Install and configure PostgreSQL. Maybe you should consider to create a separate database only for OF, and a separate user to manage it instead of user “postgres”.
  9. Start to prepare the new database for OF, as explained here: Openfire: Database Installation Guide
  10. Before you run the “openfire_postgresql.sql” script, edit it and remove the last lines, the ones with the "INSERT"s, because they will collide with the same INSERTs on “openfire.script”
  11. Run “openfire_postgresql.sql”
  12. Then run your modified “openfire.script” file
  13. Install OF and configure it to use PostgreSQL, as explained here: Openfire: Database Installation Guide
  14. Skip the “Set admin password” step, you already got the same admin password you set on your previous setup.

Well, that’s it. Hope it helps somebody!

2 Likes