Adrian Segovia

Migrate to PostgreSQL

Discussion created by Adrian Segovia on Jun 13, 2016

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":
    1. Look for the line "SET SCHEMA PUBLIC" and delete everything from the beginning until that line.
    2. 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"
    3. 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
  4. Remove openfire: On Debian, you could do that with "apt-get --purge remove openfire", so you nuke all OF configs at the same time.
  5. 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".
  6. Start to prepare the new database for OF, as explained here: Openfire: Database Installation Guide
    1. 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"
    2. Run "openfire_postgresql.sql"
    3. Then run your modified "openfire.script" file
  7. Install OF and configure it to use PostgreSQL, as explained here: Openfire: Database Installation Guide
    1. 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!

Outcomes