Tuesday, December 29, 2009

Akonadi and PostgreSQL

As you might know, Akonadi uses a relational database system for storing meta information about the PIM data it manages. Since the first official release this has been MySQL because it provided all features we needed and was shipped with every modern Linux distribution. However there have always been users complaining about the usage of MySQL. They claim it is big, slow and has not the right license.... Leaving the FUD away, it can never be wrong to support multiple database systems, so during the last months we got several contributions from the community that allowed Akonadi to use PostgreSQL as its database. Unfortunately the patches only allowed to make use of an already running and properly configured server, which is something you can't expect from a user who just wants to read emails or looking up something in the address book.

So yesterday I took a couple of hours and checked whether it is possible to implement PostgreSQL support like it is done for MySQL, namely that Akonadi starts its own instance of the database server with a custom data directory and connects to it via unix domain sockets, so every user can have its own database server running. At the end of the day I had a working solution and after crosschecking with other PIM developers that my changes won't break anything I committed the patch this morning to trunk. If you want to test it, simply rebuild the Akonadi server from trunk, cleanup your Akonadi installation and configuration and change the content of $HOME/.config/akonadi/akonadiserverrc to

[%General]
Driver=QPSQL

[QPSQL]
StartServer=true

Now after a restart PostgreSQL should be used instead of MySQL. At this point I'd like to ask you, dear reader, for some help. The code that searches for the postgres executable has some paths predefined where the executable could be located. Unfortunately that differs from distribution to distribution. So can you tell me in the comments where the executable can be found in your distribution, please? Under Debian it is /usr/lib/postgresql/8.4/bin/, where is it under SuSE or Fedora?

Thanks for your help!

16 comments:

KAMiKAZOW said...

I thought you guys switched from MySQL to Virtuoso. At least that's what the error message tells me after every boot ("Akonadi Nepomuk disabled, because Virtuoso database is not running" or something like that).

tokoe said...

@KAMiKAZOW Nepomuk uses Virtuoso for storing its sematic data. Because Akonadi depends on some functionality of Nepomuk we make sure that Nepomuk runs properly. Of course the long time plan is to store our meta data in the SQL part of Virtuoso as well, that needs some more development though.

Thomas said...

Awesome work, Tobias :)

Now, let the profiling (memory + speed) begin :D

pprkut said...

On Slackware (unofficial package) they are in /usr/bin. I guess you already look there.

google said...

on OpenSuSE 11.2
/usr/bin/postmaster -D /var/lib/pgsql/data

Robert said...

On ArchLinux, the binaries seem to be under /usr/bin.

On Gentoo, they seem to be under /usr/lib/postgresql-8.4/bin

with symbolic links from /usr/bin

Of course, if I had postgresql-8.3 installed instead, I imagine the path would be different....

Fabrice said...

For Mandriva the postgresql binary file is located at : /usr/bin/postgres.

You may want to have a look at theses options :
-F : disable fsync ( better perf, less reliability ). This option should not be used when using a /home on NFS

-h localhost : only listen to localhost

-h : only listen on Unix socket

-k : specify the directory where the Unix socket should be created ( maybe ~/.kde4/share/apps/akonadi )

More here : http://www.postgresql.org/docs/8.2/static/app-postgres.html

To search in mail body, you may want to use full-text search. For a postgresql 8.4 : http://www.postgresql.org/docs/8.4/interactive/textsearch.html

PostgreSQL performances tips : http://wiki.postgresql.org/wiki/Performance_Optimization

For any other help concerning PosggreSQL ( notably queries tuning ), please feel free to contact me.

Taurnil said...

That's great news. Lunar-Linux puts all the postgresql executables in /usr/bin

Ruurd said...

Uhm, one of the first things I did was to let akonadi use an already running MySQL. Much better startup time. So. I would like to be able to use an already running PostgreSQL instance...

tokoe said...

Thanks for all the replies so far, it seems all, except Debian, put the executable (or at least a symlink) under /usr/bin, so the current code should work with all major distributions.

@Ruurd You can still use a running server instead of starting a separated one. Just set 'StartServer=false' and 'Host' and 'Port' to the according values.

Kromain said...

let's not forget windows ;)

actually there we have a somewhat controlled environment so we can ensure that the executable will be in the path or in kde's bin directory.

Thanks for the work !

Federico Cáceres said...

Hi there, just in case, Fedora (11 64bits) also puts the postgres binary under /usr/bin.

Keep up the good work :D.

Lafriks said...

Nothing to add more, just wanted to say that these are great news! Keep up great work! :)

Kevin Kofler said...

I confirm that Fedora installs the PostgreSQL server to /usr/bin/postgres (checked 12 and Rawhide). (It's in the postgresql-server package.)

mschuerig said...

I don't quite see why each user ought to have their own database server running. If a server is already running anyway, it is a waste of resources to run another one unless there a very good reasons.

The only reason I can think of right now is that the global database server would need proper access rights to read/write the user's database wherever it may be.

PostgreSQL has the concept of a tablespace that allows to put a database at a non-standard location in the file system, for instance somewhere in the user's home directory.

MySQL, presumably, has a similar facility.

lala said...

Thanks for your post and welcome to check: here
.