Project

General

Profile

PostgreSQL » History » Version 25

azend, 01/02/2016 04:44 AM

1 1 sph
h1. PostgreSQL
2 1 sph
3 1 sph
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. 
4 1 sph
5 19 Tobu
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).
6 8 pennywise
7 1 sph
h2. Requirements
8 8 pennywise
9 17 Tobu
 * PostgreSQL server version 8.3 or greater (version 9.2 or greater strongly recommended)
10 24 sjefen6
 * Qt PostgreSQL client libraries (libqt4-sql-psql or libqt5sql5-psql (quassel 0.12.2 and newer) on Debian/Ubuntu, qt4-psql on FreeBSD)
11 1 sph
12 1 sph
h2. Preparing the database
13 1 sph
14 1 sph
We will assume you installed PostgreSQL and properly ran the initdb script.
15 1 sph
16 20 HappyHappyMan
Login using the database account (in my case postgres)
17 20 HappyHappyMan
> <pre>$ sudo -u postgres psql</pre>
18 1 sph
19 2 EgS
Now let's create the quassel database and assign an account.
20 20 HappyHappyMan
> <pre>postgres=# CREATE USER quassel ENCRYPTED PASSWORD 'somepassword';
21 2 EgS
CREATE ROLE
22 2 EgS
postgres=# CREATE DATABASE quassel WITH OWNER quassel ENCODING 'UTF8';
23 2 EgS
CREATE DATABASE
24 1 sph
</pre>
25 1 sph
26 7 pennywise
h3. Gentoo specific
27 7 pennywise
28 7 pennywise
Read http://www.gentoo.org/doc/en/postgres-howto.xml if no postgresql-server is installed.
29 7 pennywise
To create an user and a database, just use the following:
30 7 pennywise
<pre>createuser -A -D -P -E -U postgres -W quassel
31 7 pennywise
createdb -U postgres -O quassel -E UTF8 quassel</pre>
32 7 pennywise
33 1 sph
h2. Setting up the Quassel Core
34 1 sph
35 1 sph
Now that the database is running properly, we are going to tell Quassel to use the correct backend.
36 1 sph
Use one of the two steps below and you're done!
37 1 sph
38 1 sph
h3. For a new core
39 1 sph
40 2 EgS
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.
41 1 sph
42 1 sph
h3. To migrate an existing core
43 1 sph
44 1 sph
Make sure the core is not running and then execute the following:
45 4 sph
46 3 EgS
> <pre>$ quasselcore --select-backend=PostgreSQL</pre>
47 1 sph
48 13 Tobu
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.
49 10 smithbone
50 10 smithbone
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= .
51 23 dkessel
For example Ubuntu puts the config dir in /var/lib/quassel so the command for a proper migration would be:
52 10 smithbone
53 23 dkessel
> <pre>$ quasselcore --configdir=/var/lib/quassel --select-backend=PostgreSQL</pre>
54 10 smithbone
55 10 smithbone
If your migration stops with the following message then you probably forgot the --configdir= parameter
56 10 smithbone
57 10 smithbone
<pre> 2010-02-23 18:01:36 Info: PostgreSQL Storage Backend is ready. Quassel Schema Version: 14                                                                                       
58 10 smithbone
Switched backend to: PostgreSQL                                                         
59 10 smithbone
No currently active backend. Skipping migration.                                        
60 10 smithbone
New backend does not support migration: PostgreSQL                                      
61 10 smithbone
Add a new user:                                                                         
62 10 smithbone
Username:</pre>
63 11 al
64 11 al
h2. Troubleshooting
65 11 al
66 12 al
If your migration fails with a message like this
67 11 al
<pre>
68 11 al
  Error Number: -1
69 11 al
  Error Message: "ERROR:  insert or update on table "backlog" violates foreign key constraint "backlog_bufferid_fkey"
70 11 al
DETAIL:  Key (bufferid)=(855) is not present in table "buffer".
71 11 al
QPSQL: Unable to create query"
72 11 al
</pre>
73 11 al
74 11 al
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
75 11 al
<pre>
76 11 al
$ sqlite3 quassel-storage.sqlite 
77 11 al
sqlite> delete from buffer where bufferid in (select b.bufferid from buffer b left join network n using (networkid) where n.networkid is null);
78 11 al
sqlite> delete from backlog where messageid in (select bl.messageid from backlog bl left join buffer b using (bufferid) where b.bufferid is null);
79 11 al
</pre>
80 14 miohtama
81 25 azend
If your migration fails with a message like this
82 25 azend
<pre>
83 25 azend
("QSQLITE")
84 25 azend
2016-01-02 03:27:49 Info: SQLite Storage Backend is ready. Quassel Schema Version: 18
85 25 azend
("QSQLITE")
86 25 azend
Core::selectBackend(): unsupported backend: PostgreSQL
87 25 azend
    supported backends are: SQLite
88 25 azend
</pre>
89 25 azend
90 25 azend
you've probably forgotten to install the Postgres adapter library package mentioned in the requirements above.
91 25 azend
92 22 netzvieh
h3. Restarting migration
93 22 netzvieh
94 22 netzvieh
If you have to restart your migration you'll likely get this error:
95 22 netzvieh
<pre>
96 22 netzvieh
Backend already initialized. Skipping Migration
97 22 netzvieh
</pre>
98 22 netzvieh
99 22 netzvieh
To fix that you have to drop the quassel databse and start over:
100 22 netzvieh
101 22 netzvieh
First switch back to SQLite:
102 22 netzvieh
<pre> quasselcore --configdir=/var/lib/quassel --select-backend=SQLite</pre>
103 22 netzvieh
104 22 netzvieh
Then DROP the PostgreSQL Quassel database:
105 22 netzvieh
<pre>
106 22 netzvieh
$ sudo -u postgres psql
107 22 netzvieh
DROP DATABASE quassel;
108 22 netzvieh
</pre>
109 22 netzvieh
110 22 netzvieh
And then start over with recreating the database as described above.
111 22 netzvieh
112 14 miohtama
h2. Migrating core to a new server
113 14 miohtama
114 14 miohtama
Install core on the new server.
115 14 miohtama
116 14 miohtama
Shutdown old core and new cores.
117 14 miohtama
118 14 miohtama
Dump old database without exporting credentials. In this example db is called quassel:
119 14 miohtama
120 14 miohtama
<pre>pg_dump --clean --no-owner --no-acl --file=quassel-dump.sql quassel</pre>
121 14 miohtama
122 14 miohtama
Copy SQL dump to the new server:
123 14 miohtama
124 14 miohtama
<pre>scp -C -o CompressionLevel=9 quassel-dump.sql user@newserver.com:~</pre>
125 14 miohtama
126 14 miohtama
Import dump on the new server:
127 14 miohtama
128 14 miohtama
<pre>psql -d dbname -U user  -h db1.server.com < quassel-dump.sql</pre>
129 14 miohtama
130 14 miohtama
Run --select-backend on the new server. You'll get compltain:
131 14 miohtama
132 14 miohtama
<pre>Backend already initialized. Skipping Migration</pre>
133 14 miohtama
134 14 miohtama
... don't care about ti.
135 14 miohtama
136 15 miohtama
You also might want to reset the password of your quassel user. 
137 14 miohtama
138 14 miohtama
<pre>./quasselcore-static-0.8.0 --change-userpass=quasseluser</pre>
139 14 miohtama
140 1 sph
Then start quasselcore on the new server and everything should be intact. You might need to reconfigure IRC servers.
141 16 Tobu
142 16 Tobu
h1. PostgreSQL performance and maintenance
143 16 Tobu
144 21 rikai
You need PostgreSQL 9.2 (or newer) which fixes an issue with the performance of prepared statements.  Ubuntu/Debian users can use "these instructions":https://wiki.postgresql.org/wiki/Apt#PostgreSQL_packages_for_Debian_and_Ubuntu and run pg_upgradecluster to migrate existing data.
145 16 Tobu
146 16 Tobu
<pre>
147 16 Tobu
\timing on
148 19 Tobu
SET maintenance_work_mem = '512MB';  -- temporarily give all your free ram to PostgreSQL
149 16 Tobu
VACUUM ANALYZE;
150 16 Tobu
CLUSTER backlog USING backlog_bufferid_idx;
151 16 Tobu
VACUUM ANALYZE;
152 16 Tobu
ALTER ROLE quassel SET random_page_cost TO DEFAULT;
153 16 Tobu
ALTER ROLE quassel SET work_mem TO '16MB';
154 16 Tobu
\drds
155 16 Tobu
</pre>
156 16 Tobu
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.