When SQLite is not enough

SQLite is great for ad-hoc SQL DB-ing, but it’s not so great if you need to serve multiple processes (on writing, it locks the complete database).

It just so happens that, for a certain project, I need the ad-hocishness of SQLite *and* multiprocess write concurrency.

It appears that PostgreSQL can easily be made to run in “hassle-free” mode. Meaning: no need to ask your mum or your sysadmin anything, no need to write any config files.

Minimalism

Well, almost. I put in some extra options to disable the TCP socket, and I set some permissions on the unix socket.
For these examples, I assume bash or zsh and a unixish system. And PostgreSQL 9.0.

create a database:

initdb ~/mypostgres
You now have a directory ‘mypostgres’ in your homedir. It contains some config files with defaults. We don’t care.

start a server:

postgres -D ~/mypostgres/ -k ~/mypostgres/ --listen_addresses='' \
--unix_socket_permissions=0660 --unix_socket_group=$(id -g)
Let’s go over these options:

  • -D ~/mypostgres/ — Use ~/mypostgres as datadir (it’s where you created the database, so.)
  • -k ~/mypostgres/ — Use ~/mypostgres as the directory to store the unix socket for clients to connect to. Look closely and you’ll discover it (it starts with a dot for no good reason). Put it somewhere else (/tmp springs to mind) if you want other users to connect to your db, more on that later.

These are not strictly necessary:

  • --listen_addresses='' — Don’t listen on any TCP sockets. We don’t really authenticate users. So if you expose a TCP socket (default, on the localhost interface) any user can do anything to your databases just by running something like psql -h 127.0.0.1 -d postgres -U yourusername
  • --unix_socket_permissions=0660 — By default, permissions are o=rwx. If you put the socket in a public place such as /tmp, then with our authentication-free mode of operation, you probably do not want any and all users to be able to access your DB.
  • --unix_socket_group=$(id -g) — This is completely superfluous but it’s here so you don’t shoot yourself in the foot when blindly copy-pasting teh codez. $(id -g), of course, is your primary group ID and is what the group ownership of the socket will have been set to without this specification anyway (hence the superfluousness). So when would you use this? You’d use it when you want to let other users access your DB. You’d set the gid to some appropriate group that you and the other users belong to (and you’d put the socket in a public place, that much should be clear now).

some goodies:

  • --silent-mode=true — Daemon mode. To quit the server, get the PID from ~/mydb/postmaster.pid and send it a SIGTERM.
  • -F — Wicked fast writes mode. Also, wickedly trashed DB mode if the server is interrupted abnormally.

Create and connect

Now the server is up and running, create a database:
createdb -h ~/mypostgres mydb
and connect to it:
psql -h ~/mypostgres -d mydb
By default psql will try to log you in to the DB using the same username as your current unix username. Convenient, because that user has full privileges on the DB.
Should you need to specify the user as whom to connect, you can do so with -U.

Wrapper?

Just like with sqlite, you determine access to the DB by setting unix permissions. We do it on the socket, with SQLite you do it on the DB file itself.
So, how about a wrapper Python module to make all this just as easy as

import sqlite3
conn = connect("create_me_if_I_don't_exist.db")

Yes, one day. Fork off a db server process, terminate it using the atexit decorator. It just might be that simple.


Tags: , , , —