PostgreSQL » History » Version 14
« Previous -
Next » -
miohtama, 05/11/2012 01:25 PM
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.
- PostgreSQL server version 8.3 or greater (version 8.4 or greater recommended)
- Qt PostgreSQL client libraries (libqt4-sql-psql on Debian/Ubuntu, 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 pgsql)
# su pgsql
Now let's create the quassel database and assign an account.
$ psql postgres=# CREATE USER quassel ENCRYPTED PASSWORD 'somepassword'; CREATE ROLE postgres=# CREATE DATABASE quassel WITH OWNER quassel ENCODING 'UTF8'; CREATE DATABASE
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/cache/quassel so the command for a proper migration would be:
$ quasselcore --configdir=/var/cache/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:
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);
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 email@example.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 need to reset the psasword of your quassel user. (It is hashed by the computer?)