Project

General

Profile

PostgreSQL » History » Version 18

Tobu, 06/24/2013 01:58 PM

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 18 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).
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 1 sph
Login using the database account (in my case pgsql)
17 1 sph
> <pre># su pgsql</pre>
18 1 sph
19 1 sph
Now let's create the quassel database and assign an account.
20 2 EgS
> <pre>$ psql
21 2 EgS
postgres=# CREATE USER quassel ENCRYPTED PASSWORD 'somepassword';
22 2 EgS
CREATE ROLE
23 2 EgS
postgres=# CREATE DATABASE quassel WITH OWNER quassel ENCODING 'UTF8';
24 2 EgS
CREATE DATABASE
25 1 sph
</pre>
26 1 sph
27 7 pennywise
h3. Gentoo specific
28 7 pennywise
29 7 pennywise
Read http://www.gentoo.org/doc/en/postgres-howto.xml if no postgresql-server is installed.
30 7 pennywise
To create an user and a database, just use the following:
31 7 pennywise
<pre>createuser -A -D -P -E -U postgres -W quassel
32 7 pennywise
createdb -U postgres -O quassel -E UTF8 quassel</pre>
33 7 pennywise
34 1 sph
h2. Setting up the Quassel Core
35 1 sph
36 1 sph
Now that the database is running properly, we are going to tell Quassel to use the correct backend.
37 1 sph
Use one of the two steps below and you're done!
38 1 sph
39 1 sph
h3. For a new core
40 1 sph
41 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.
42 1 sph
43 1 sph
h3. To migrate an existing core
44 1 sph
45 1 sph
Make sure the core is not running and then execute the following:
46 4 sph
47 3 EgS
> <pre>$ quasselcore --select-backend=PostgreSQL</pre>
48 1 sph
49 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.
50 10 smithbone
51 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= .
52 10 smithbone
For example Ubuntu puts the config dir in /var/cache/quassel so the command for a proper migration would be:
53 10 smithbone
54 10 smithbone
> <pre>$ quasselcore --configdir=/var/cache/quassel --select-backend=PostgreSQL</pre>
55 10 smithbone
56 10 smithbone
If your migration stops with the following message then you probably forgot the --configdir= parameter
57 10 smithbone
58 10 smithbone
<pre> 2010-02-23 18:01:36 Info: PostgreSQL Storage Backend is ready. Quassel Schema Version: 14                                                                                       
59 10 smithbone
Switched backend to: PostgreSQL                                                         
60 10 smithbone
No currently active backend. Skipping migration.                                        
61 10 smithbone
New backend does not support migration: PostgreSQL                                      
62 10 smithbone
Add a new user:                                                                         
63 10 smithbone
Username:</pre>
64 11 al
65 11 al
h2. Troubleshooting
66 11 al
67 12 al
If your migration fails with a message like this
68 11 al
<pre>
69 11 al
  Error Number: -1
70 11 al
  Error Message: "ERROR:  insert or update on table "backlog" violates foreign key constraint "backlog_bufferid_fkey"
71 11 al
DETAIL:  Key (bufferid)=(855) is not present in table "buffer".
72 11 al
QPSQL: Unable to create query"
73 11 al
</pre>
74 11 al
75 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
76 11 al
<pre>
77 11 al
$ sqlite3 quassel-storage.sqlite 
78 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);
79 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);
80 11 al
</pre>
81 14 miohtama
82 14 miohtama
h2. Migrating core to a new server
83 14 miohtama
84 14 miohtama
Install core on the new server.
85 14 miohtama
86 14 miohtama
Shutdown old core and new cores.
87 14 miohtama
88 14 miohtama
Dump old database without exporting credentials. In this example db is called quassel:
89 14 miohtama
90 14 miohtama
<pre>pg_dump --clean --no-owner --no-acl --file=quassel-dump.sql quassel</pre>
91 14 miohtama
92 14 miohtama
Copy SQL dump to the new server:
93 14 miohtama
94 14 miohtama
<pre>scp -C -o CompressionLevel=9 quassel-dump.sql user@newserver.com:~</pre>
95 14 miohtama
96 14 miohtama
Import dump on the new server:
97 14 miohtama
98 14 miohtama
<pre>psql -d dbname -U user  -h db1.server.com < quassel-dump.sql</pre>
99 14 miohtama
100 14 miohtama
Run --select-backend on the new server. You'll get compltain:
101 14 miohtama
102 14 miohtama
<pre>Backend already initialized. Skipping Migration</pre>
103 14 miohtama
104 14 miohtama
... don't care about ti.
105 14 miohtama
106 15 miohtama
You also might want to reset the password of your quassel user. 
107 14 miohtama
108 14 miohtama
<pre>./quasselcore-static-0.8.0 --change-userpass=quasseluser</pre>
109 14 miohtama
110 1 sph
Then start quasselcore on the new server and everything should be intact. You might need to reconfigure IRC servers.
111 16 Tobu
112 16 Tobu
h1. PostgreSQL performance and maintenance
113 16 Tobu
114 16 Tobu
You need PostgreSQL 9.2 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.
115 16 Tobu
116 16 Tobu
<pre>
117 16 Tobu
\timing on
118 16 Tobu
SET maintenance_work_mem = '512MB';  -- give all your free ram to PostgreSQL
119 16 Tobu
VACUUM ANALYZE;
120 16 Tobu
CLUSTER backlog USING backlog_bufferid_idx;
121 16 Tobu
VACUUM ANALYZE;
122 16 Tobu
ALTER ROLE quassel SET random_page_cost TO DEFAULT;
123 16 Tobu
ALTER ROLE quassel SET work_mem TO '16MB';
124 16 Tobu
\drds
125 16 Tobu
</pre>
126 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.