wiki:PostgreSql
Warning: Can't synchronize with repository "(default)" (Unsupported version control system "svn": No module named svn). Look in the Trac log for more information.

Version 3 (modified by cliff@…, 9 years ago) (diff)

--

PostgreSQL offers a lot of advantages over most other open source databases, but is somewhat less familiar to many people.

Here's a quick guide to using PostgreSQL with Turbogears.

Selecting the appropriate adapter

The Turbogears docs suggest using a DSN like

postgresql://user:passwd@host:5432/dbname

From looking at the SQLObject source however, this appears suboptimal. SQLObject tries several PostgreSQL adapters in this order

PyGreSQL (aka pgdb), psycopg2, psycopg

From my own experience, psycopg is far superior to pgdb. Therefore I suggest instead to use the scheme

psycopg://user:passwd@host:5432/dbname

This ensures that psycopg is the chosen adapter.

Using a Unix domain socket

As long as you don't require remote access, using a local socket has a lot of advantages over TCP sockets:

  • Up to 25% faster
  • Easier to secure (uses filesystem permissions)
  • Doesn't waste a TCP port

With PostgreSQL, if the hostname starts with a /, then the hostname is taken to be an absolute path to a Unix domain socket rather than a hostname. However, there are (as of this writing), bugs in the DSN parser for SQLObject that fail to separate the socket path from the database name. A DSN like this works around that issue:

psycopg:///user/dbname?host=/path/to/socket

Note that I'm not specifying a username or password. If you are sharing the socket with many users, you will want to do so, but if you are running your own instance of PostgreSQL (which I recommend) then you can forgo database-level authentication schemes and just use Unix file permissions to restrict access.

Known issues

  • Catwalk has a bug that causes a hang the first time the application is started with Catwalk mounted. The workaround is fairly straightforward: simply create the requisite Catwalk tables by hand then re-run the application.

The required SQL (at the time of this writing) is:

CREATE TABLE catwalk_state_table (
  id INTEGER PRIMARY KEY NOT NULL,
  state BYTEA
);

Use the psql command to run this query and you'll be set.