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 8 (modified by godoy, 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.

The DSN

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

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

5432 is the default port for PostgreSQL, but may vary depending on configuration. If your PostgreSQL is listening on its default port, this can be absent from the DSN making it simpler:

postgres://user:passwd@host/dbname

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:

postgres:///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.