Quassel: Migrate core from Sqlite to Postgres

Migration Command

You probably want to make the migration process a little more verbose. So run it like this:

sudo -u quasselcore quasselcore --configdir=/var/lib/quassel -d --loglevel=Debug --select-backend=PostgreSQL

Duplicate buffers

During migration Postgres found inconsistent data in the Sqlite database. I am not sure this Sqlite did not respect the constraints correctly, or quassel has different schemas for the different databases. So, my migration process ended with:

2024-04-15 09:44:59 [Warn ]   Error Message: "ERROR:  duplicate key value violates unique constraint \"buffer_userid_networkid_buffercname_key\"\nDETAIL:  Key (userid, networkid, buffercname)=(8, 17, someusername) already exists.\n(23505) QPSQL: Unable to create query"

I investigated the issue and indeed found six rows in the Sqlite database that would match this combined primary key.

Some investigation later I decided that I do not know enough about the quassel schema to actually repair this issue. Since it only affected around 30 lines of backlog I decided to simply drop the duplicate buffers and corresponding backlog -entries.

My SQL is quite rusty. So I decided to do this from python instead:

#!/usr/bin/env python3

import sqlite3


def sql_query_to_list_of_dicts(con, query):
    things = con.execute(query).fetchall()
    unpacked = [{k: item[k] for k in item.keys()} for item in things]
    return unpacked


con = sqlite3.connect("quassel-storage.sqlite")
con.row_factory = sqlite3.Row

should_be_pkey = dict()
for line in sql_query_to_list_of_dicts(con, "SELECT * FROM buffer;"):
    pkey = (line["userid"], line["networkid"], line["buffercname"])
    if pkey in should_be_pkey:
        count = con.execute(f"SELECT COUNT(bufferid) FROM backlog WHERE bufferid={line['bufferid']};").fetchone()
        count = count["COUNT(bufferid)"]

        print(f"Found duplicate buffer: {pkey} (userid, networkid, buffercname). This affects {count} lines in backlog.")
        con.execute(f"DELETE FROM backlog WHERE bufferid={line['bufferid']}")
        con.execute(f"DELETE FROM buffer where bufferid={line['bufferid']}")
        con.commit()
        print("Deleted backlog and buffer :-(")
        print("---")
    else:
        should_be_pkey[pkey] = line["bufferid"]

If you decide to copy this script: Make a backup of your quassel-storage.sqlite first!