Migrate MojoMojo Database from SQLite to PostgreSQL

Overview

You may have been like me and chose the easy database route¹ when first starting your MojoMojo instance by choosing SQLite. Furthermore, you may decide that you’d like to change from SQLite to PostgreSQL at some point in time. This paper talks about how to do that.

Dump your SQLite MojoMojo Database to SQL

The first step is to get the data out of the SQLite3 database. This is done by dumping to SQL as follows:

sqlite3 mojomojo.db .dump > mojomojo.sql

Prepare Database into PostgreSQL

Now we need to prepare a PostgreSQL database to store the wiki tables.

Create it

From with psql

create database mojomojo;

Add sequence ids

PostgreSQL uses sequences to perform it’s auto-incremented of primary keys when the are of the serial type. We need to equip the PostgreSQL database with these sequences so the tables will behave correctly on insert of a new record.

Tables Involved

Sequences are needed for the following the id in each of the following tables:

• attachment
• comment
• entry
• link
• page
• person
• photo
• role
• tag
• wanted_page

Max value per table

In order to create the sequences we need to know where to start off counting from since some id integers are already used. We’ll get the current max id and start from it⁸.

select max(id) from attachment;

Sequence Creation

We create a sequence (for atttachment table) as follows

CREATE SEQUENCE attachment_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 39
  CACHE 1;
ALTER TABLE attachment_id_seq OWNER TO mojomojo;

Apply Sequence

Once we’ve created the sequences we can apply them to the existing id columns:

alter table public.attachment
alter column id 
set default nextval('attachment_id_seq');

SQL Generation Helper Script

I’ve created a helper script to assist the process of generating the SQL code to install the sequences. One needs to find the max id value for each of the tables. The subroutine select_max_id() helps with this. Then one can run the create_sequence() and make_serial_id() subroutines to generate the SQL code that will create and apply the sequences.

(Attempt to) Import into PostgreSQL

\connect mojomojo
\i wiki.sql

My next step was to import the data by executing the SQL dump from within psql. This is when I discovered that additional preparation was necessary because of complaints (errors) during import/execute of SQL⁴. In my case, I had to do the following things:

• Edit the Dump - doing a search and replace on one un-recognized data type.
• Handle Records that Violated Constraints.
• Set up Proper Pg environment for executing the SQL INSERTs

Edit Dump

After the dump you may need to edit the SQL a little bit.

In my case I had to replace BIGINT(100) with just BIGINT since PostgreSQL didn’t recognize the former

Delete Duplicate Login Records with Null where Number required.

There were a few records in person table where the ‘active’ column was NULL when it needed an integer value. I just deleted those records since they were duplicates⁶ anyway and non-active.

Inform Pg to Treat Strings Entirely as Literal

When I first attempted run the .sql dump using psql I encountered an error that was a bit perplexing. When trying to import (run) the SQL dumped from SQLite I got the following:

ERROR:  invalid byte sequence for encoding "UTF8": 0x80 

The above error message was not due to unicode directly but rather backslashes being interpreted as part of a metacharacter and created what looked like bad unicode. In effect, in a string like \2008-2009 the \200 was being interpreted as being part of an Octal UTF-8 byte sequence. Further, when a correct sequence (such as \302\200) was not found thus the error³. I needed to inform Pg to treat backslashes as literals.

The key to resolving this error type was the setting²:

set standard_conforming_strings=on;

Create user Account for MojoMojo

Now that we’ve got the data into the database, let’s create a mojomojo user specific for this database.

create user mojomojo with password 'BoxOfSquash';

Let’s give the mojomojo user all privileges on the mojomojo database.

grant all privileges on database mojomojo to mojomojo;

The above didn’t work for me⁷ so I granted privileges table by table as such:

postgres=# \connect mojomojo
grant all on table page to mojomojo;                                   
grant all on table attachment to mojomojo;                                         
grant all on table comment to mojomojo;
grant all on table content to mojomojo;
grant all on table entry to mojomojo;
grant all on table journal to mojomojo;           
grant all on table link to mojomojo; 
grant all on table page_version to mojomojo; 
grant all on table path_permissions to mojomojo;
grant all on table person to mojomojo;
grant all on table photo to mojomojo;
grant all on table preference to mojomojo;
grant all on table role to mojomojo;
grant all on table role_member to mojomojo;
grant all on table role_privilege to mojomojo;
grant all on table tag to mojomojo;
grant all on table wanted_page to mojomojo;

Edit MojoMojo Database Connection String

Given a database and a user accout, we can now inform MojoMojo of the new database connection to use. Within mojomojo_local.conf⁵, edit the database connection:

connect_info    dbi:Pg:dbname=mojomojo;host=localhost;
connect_info    mojomojo
connect_info    password

Summary

In summary, we’ve dumped a SQLite database and brought it into PostgreSQL. We’ve informed MojoMojo of the new database connection including the newly created database role (account). Now it is time to restart my MojoMojo instance so it will be running off the PostgreSQL database we just created from the SQLite one.

Footnotes

¹ No configuration required.

² Big Thanks to RhodiumToad on #postgresql for pointing out the standard_conforming_strings setting.

³ 0x80 is a hex representation of the octal 200.

⁴ It may take multiple import attempts to find all the errors that need fixing.

⁵ You may only have mojomojo.conf which is fine to edit as well.

⁶ Some earlier versions of MojoMojo did not enforce unique logins or emails. If you used one of these pre 0.999028 versions of MojoMojo you may find person records with duplicate logins or email addresses. Delete the inactive duplicate records.

⁷ The specific error I received was: [error] DBIx::Class::ResultSet::find_or_create(): DBI Exception: DBD::Pg::st execute failed: ERROR: permission denied for relation preference [for Statement “SELECT me.prefkey, me.prefvalue FROM preference meWHERE ( me.prefkey = ? )” with ParamValues: 1=’default_lang’] at /usr/local/share/perl/5.10.0/MojoMojo.pm line 140

⁸ The START value in a sequence is the highest value in use (last value used) so it seems. My intuition expected it to be one higher than the max in use given the ‘START’ name.

My tags:

 

Popular tags: