How to Test Drupal with PostgreSQL

Blog Topics:

On-line docs for PostgreSQL:
http://www.postgresql.org/docs/8.2/static/index.html

Taken from a post by Barry J.:

I use XAMPP on Windows for development. Here is my process for using PostgreSQL 8.3:

1. Download and run the installer.
2. Uncomment the "extension=php_pgsql.dll" line in Apache's php.ini.
3. Re-start Apache.
4. Change $db_url in settings.php.

Taken from a post by Bill M.:

Once it's installed, there's another step called "initializing
the cluster" ... which is basically creating an empty database.
Some packaging systems might do this for you. If PostgreSQL
starts, then it's done (it won't implicitly create an empty
system).

PG is designed to allow multiple version on a single machine.
I don't know how many people actually do this, but it's a godsend
when you need it. The point is that each "cluster" (as it's called)
is a directory with all the configuration and database files for
that running instance -- usually /usr/local/pgsql or something like
like that. do a "find / -name postgresql.conf" and you'll find
it.

That's the first file you want to look at. There's a lot of
esoteric tuning options, but the obvious one near the beginning
is called "listen_address" which defaults to "localhost" only.
(Although different package systems may install a custom config
file, so you should check)

PG versions greater
than 8 have unified users and groups into a single concept called
a "role". If you just think of a "user" as a role with login
permissions, you'll be OK.

If you connect with the psql command, you can issue "\du" to get
a list of users. I'm guessing by default that there's only the
default superuser on your system. To create a new one:
CREATE ROLE drupal WITH LOGIN;
Or, to create it with superuser permissions:
CREATE ROLE drupal WITH LOGIN SUPERUSER;
You can also tweak roles after creation:
ALTER ROLE drupal PASSWORD = 'somepassword';
Then create a database:
CREATE DATABASE drupal;
And tweak settings as you like:
GRANT all ON DATABASE drupal TO drupal;

PG has a two-pronged approach to security. The role system which
I just described is actually the second layer. The first layer
is called "host-based authentication". These two layers combined
basically equate to MySQL's role system.

In the same directory as the postgresql.conf file, you'll find
a pg_hba.conf file. In this are lines that control what users
from what hosts can connect to what databases. For your
purposes, you probably want lines like:

host all all 127.0.0.1/32 trust
local all all trust

The first means that any user connecting via the loopback is
allowed to connect without a password. The second means that
any user can connect to any database via the local unix socket
without a password.

You can get more specific if you want:
# Allow anyone from local network to connect to database myaccount
# as user drupal, as long as they know the password
host drupal myaccount 192.168.5.0/24 password
# Allow anyone to connect to database drupal as any user from a
# single remote machine without a password
host all drupal 172.16.56.21/32 trust

psql is your friend. Some useful commands for seeing what your
database looks like:
# Show all roles (The "u" is legacy from when they were users)
\du
# Show all databases
\l
# Show the table structure of table "users"
\d users
# Show the permissions on all objects in the database
\dp
# Show the built-in help
\?