Project

General

Profile

History

PostgreSQL

This article describes how you can use Quassel with the PostgreSQL database. It is written from a FreeBSD point of view, but the process should be very similar for any other system out there.

This appeared in Quassel release 0.5.0. Using PostgreSQL 9.2 is strongly recommended, otherwise Quassel's prepared statements will have very poor performance, which as the database grows will cause timeouts and make it impossible to connect to the core (see #680 and the maintenance section below).

Requirements

  • PostgreSQL server version 8.3 or greater (version 9.2 or greater strongly recommended)
  • Qt PostgreSQL client libraries (libqt4-sql-psql or libqt5sql5-psql (quassel 0.12.2 and newer) on Debian/Ubuntu, qt5-qtbase-postgresql on Fedora, qt4-psql on FreeBSD)

Preparing the database

We will assume you installed PostgreSQL and properly ran the initdb script.

Login using the database account (in my case postgres)

$ sudo -u postgres psql

Now let's create the quassel database and assign an account.

postgres=# CREATE USER quassel ENCRYPTED PASSWORD 'somepassword';
CREATE ROLE
postgres=# CREATE DATABASE quassel WITH OWNER quassel ENCODING 'UTF8';
CREATE DATABASE

Gentoo specific

Read http://www.gentoo.org/doc/en/postgres-howto.xml if no postgresql-server is installed.
To create an user and a database, just use the following:

createuser -A -D -P -E -U postgres -W quassel
createdb -U postgres -O quassel -E UTF8 quassel

Setting up the Quassel Core

Now that the database is running properly, we are going to tell Quassel to use the correct backend.
Use one of the two steps below and you're done!

For a new core

Just connect to the core using a Quassel Client to launch the first run wizard. Select the PostgreSQL backend in the dropdown list and fill in the needed credentials to connect to the Postgres DB you just created.

To migrate an existing core

Make sure the core is not running and then execute the following:

$ quasselcore --select-backend=PostgreSQL

An interactive script will request the necessary information to migrate successfully. localhost can be replaced by /var/run/postgresql (Debian/Ubuntu FHS-compliant location) to use UNIX domain sockets and, if ident is enabled in pg_hba.conf, uid-based authentication.

If your existing database and config file are in a different location than the default then you need to specify the --configdir= parameter as well as the --select-backend= .
For example Ubuntu puts the config dir in /var/lib/quassel so the command for a proper migration would be:

$ quasselcore --configdir=/var/lib/quassel --select-backend=PostgreSQL

If your migration stops with the following message then you probably forgot the --configdir= parameter

 2010-02-23 18:01:36 Info: PostgreSQL Storage Backend is ready. Quassel Schema Version: 14                                                                                       
Switched backend to: PostgreSQL                                                         
No currently active backend. Skipping migration.                                        
New backend does not support migration: PostgreSQL                                      
Add a new user:                                                                         
Username:

Troubleshooting

If your migration fails with a message like this

  Error Number: -1
  Error Message: "ERROR:  insert or update on table "backlog" violates foreign key constraint "backlog_bufferid_fkey" 
DETAIL:  Key (bufferid)=(855) is not present in table "buffer".
QPSQL: Unable to create query"

your SQLite DB probably contains leftovers from e.g. a deleted network. Make sure you have a backup and try to clean the invalid data sets from the database by issuing

$ sqlite3 quassel-storage.sqlite 
sqlite> delete from buffer where bufferid in (select b.bufferid from buffer b left join network n using (networkid) where n.networkid is null);
sqlite> delete from backlog where messageid in (select bl.messageid from backlog bl left join buffer b using (bufferid) where b.bufferid is null);

If your migration from SQLite to PostgreSQL fails with a message like this

("QSQLITE")
2016-01-02 03:27:49 Info: SQLite Storage Backend is ready. Quassel Schema Version: 18
("QSQLITE")
Core::selectBackend(): unsupported backend: PostgreSQL
    supported backends are: SQLite

you've probably forgotten to install the Postgres adapter library package mentioned in the requirements above. Once you've completed your migration, you will need to reset passwords for each of your core users to be able to log in.

quasselcore --configdir=/var/lib/quassel --change-userpass=quasseluser

Restarting migration

If you have to restart your migration you'll likely get this error:

Backend already initialized. Skipping Migration

To fix that you have to drop the quassel databse and start over:

First switch back to SQLite:

 quasselcore --configdir=/var/lib/quassel --select-backend=SQLite

Then DROP the PostgreSQL Quassel database:

$ sudo -u postgres psql
DROP DATABASE quassel;

And then start over with recreating the database as described above.

Migrating core to a new server

Install core on the new server.

Shutdown old core and new cores.

Dump old database without exporting credentials. In this example db is called quassel:

pg_dump --clean --no-owner --no-acl --file=quassel-dump.sql quassel

Copy SQL dump to the new server:

scp -C -o CompressionLevel=9 quassel-dump.sql user@newserver.com:~

Import dump on the new server:

psql -d dbname -U user  -h db1.server.com < quassel-dump.sql

Run --select-backend on the new server. You'll get compltain:

Backend already initialized. Skipping Migration

... don't care about ti.

You also might want to reset the password of your quassel user.

./quasselcore-static-0.8.0 --change-userpass=quasseluser

Then start quasselcore on the new server and everything should be intact. You might need to reconfigure IRC servers.

PostgreSQL performance and maintenance

You need PostgreSQL 9.2 (or newer) which fixes an issue with the performance of prepared statements. Ubuntu/Debian users can use these instructions and run pg_upgradecluster to migrate existing data.

\timing on
SET maintenance_work_mem = '512MB';  -- temporarily give all your free ram to PostgreSQL
VACUUM ANALYZE;
CLUSTER backlog USING backlog_bufferid_idx;
VACUUM ANALYZE;
ALTER ROLE quassel SET random_page_cost TO DEFAULT;
ALTER ROLE quassel SET work_mem TO '16MB';
\drds

The CLUSTER and ANALYSE operations will take effect immediately, the settings changes for the quassel user will take effect the next time quasselcore or postgresql is restarted.