imapsql: SQLite to PostgreSQL migration

[foxcpp]

Not adding this to maddy.email documentation since this is not really officially supported (yet). This involves some careful regex editing in the dump file.

1. Dump SQLite DB to SQL.

sqlite3 /var/lib/maddy/imapsql.db
> .output dump.sql
> .dump

2. Adjust schema types to match PostgreSQL.

INTEGER ... AUTOINCREMENT should be BIGSERIAL in PostgreSQL.

Open SQL dump with text editor.
2.1. Remove PRAGMA foreign_keys=OFF.
2.2. Update users.id definition:

CREATE TABLE users (
                        id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                        username VARCHAR(255) NOT NULL UNIQUE,

should become

CREATE TABLE users (
                        id BIGSERIAL NOT NULL PRIMARY KEY,
                        username VARCHAR(255) NOT NULL UNIQUE,

2.3. Do the same for CREATE TABLE mboxes (mboxes.id need fixing)
2.4. LONGTEXT type should be BYTEA. Find&Replace will do just fine.

3. Update blob format in dump to match PostgreSQL

SQLite writes hex-encoded blobs as X'data', PostgreSQL uses '\xdata'.

This works fine:

sed -i "s/,X'/,'\\\\x/g" ~/storage.sql

4. Remove anything that mentions sqlite_schema, sqlite_stat1. These are SQLite internals and are not relevant.

5. Convert sqlite_sequence into PostgreSQL sequences.

Somewhere near the end of dump, you will find this:

DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('users',4);
INSERT INTO sqlite_sequence VALUES('mboxes',24);

You should replace these with corresponding ALTER SEQUENCE commands, like this:

ALTER SEQUENCE mboxes_id_seq RESTART WITH 24;
ALTER SEQUENCE users_id_seq RESTART WITH 4;

6. Load dump into PostgreSQL.