Project

General

Profile

PostgreSQL » History » Version 23

dkessel, 10/31/2015 04:55 PM
Change path references for Debian/Ubuntu from /var/cache/quassel to /var/lib/quassel, as that is where the files are in all supported releases

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 13 Tobu
 * Qt PostgreSQL client libraries (libqt4-sql-psql 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 22 netzvieh
h3. Restarting migration
82 22 netzvieh
83 22 netzvieh
If you have to restart your migration you'll likely get this error:
84 22 netzvieh
<pre>
85 22 netzvieh
Backend already initialized. Skipping Migration
86 22 netzvieh
</pre>
87 22 netzvieh
88 22 netzvieh
To fix that you have to drop the quassel databse and start over:
89 22 netzvieh
90 22 netzvieh
First switch back to SQLite:
91 22 netzvieh
<pre> quasselcore --configdir=/var/lib/quassel --select-backend=SQLite</pre>
92 22 netzvieh
93 22 netzvieh
Then DROP the PostgreSQL Quassel database:
94 22 netzvieh
<pre>
95 22 netzvieh
$ sudo -u postgres psql
96 22 netzvieh
DROP DATABASE quassel;
97 22 netzvieh
</pre>
98 22 netzvieh
99 22 netzvieh
And then start over with recreating the database as described above.
100 22 netzvieh
101 14 miohtama
h2. Migrating core to a new server
102 14 miohtama
103 14 miohtama
Install core on the new server.
104 14 miohtama
105 14 miohtama
Shutdown old core and new cores.
106 14 miohtama
107 14 miohtama
Dump old database without exporting credentials. In this example db is called quassel:
108 14 miohtama
109 14 miohtama
<pre>pg_dump --clean --no-owner --no-acl --file=quassel-dump.sql quassel</pre>
110 14 miohtama
111 14 miohtama
Copy SQL dump to the new server:
112 14 miohtama
113 14 miohtama
<pre>scp -C -o CompressionLevel=9 quassel-dump.sql user@newserver.com:~</pre>
114 14 miohtama
115 14 miohtama
Import dump on the new server:
116 14 miohtama
117 14 miohtama
<pre>psql -d dbname -U user  -h db1.server.com < quassel-dump.sql</pre>
118 14 miohtama
119 14 miohtama
Run --select-backend on the new server. You'll get compltain:
120 14 miohtama
121 14 miohtama
<pre>Backend already initialized. Skipping Migration</pre>
122 14 miohtama
123 14 miohtama
... don't care about ti.
124 14 miohtama
125 15 miohtama
You also might want to reset the password of your quassel user. 
126 14 miohtama
127 14 miohtama
<pre>./quasselcore-static-0.8.0 --change-userpass=quasseluser</pre>
128 14 miohtama
129 1 sph
Then start quasselcore on the new server and everything should be intact. You might need to reconfigure IRC servers.
130 16 Tobu
131 16 Tobu
h1. PostgreSQL performance and maintenance
132 16 Tobu
133 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.
134 16 Tobu
135 16 Tobu
<pre>
136 16 Tobu
\timing on
137 19 Tobu
SET maintenance_work_mem = '512MB';  -- temporarily give all your free ram to PostgreSQL
138 16 Tobu
VACUUM ANALYZE;
139 16 Tobu
CLUSTER backlog USING backlog_bufferid_idx;
140 16 Tobu
VACUUM ANALYZE;
141 16 Tobu
ALTER ROLE quassel SET random_page_cost TO DEFAULT;
142 16 Tobu
ALTER ROLE quassel SET work_mem TO '16MB';
143 16 Tobu
\drds
144 16 Tobu
</pre>
145 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.