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 6 (modified by cliff@…, 9 years ago) (diff)

Reviewing SQLObject source, section about adapters is wrong. psycopg is the default.

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.

The DSN

The basic form of a DSN for using PostgreSQL is as follows:

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

5432 is the default port for PostgreSQL, but may vary depending on configuration.

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:///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 SERIAL PRIMARY KEY NOT NULL,
  state BYTEA
);

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