So I’ve been playing around with postgres for a week or so and this morning was finally ready to do an apache benchmark to test out how well it’ll work in something approximating a production environment. I ran mysql-bench on a comparable mysql database and tried to run it on my postgres database, but there were statements in the tests that postgres couldn’t handle, so it bombed. The really important test is how well the database works when behind apache, though, so I prepared my postgres database for such a test. As part of the benchmarking, I decided to separate the database server from the Web server so that load on one service doesn’t skew measurements of load on the other. So mysql and postgres are running on one box and apache’ll handle connections on another box that connects to the database box.

Some time back, I built a mysql database with four or five tables that are all pretty intimately related and that’ll see a lot of joins in production. I populated each of these with a million more or less random rows of data. Yesterday, I built a comparable postgres database and populated it with roughly the same dataset — the same size dataset, at any rate. Luckily, the Web service I had written to hit the mysql database used pretty basic queries that’ll work seamlessly in the postgres database. I had to add some code to my PHP database abstraction library so it could connect to a postgres database, but that was a cinch. So I get all that done and run some apache benchmarks on the mysql database. No problem. Then I change one value in my PHP file to make it hit the postgres database, and my connection times out, giving me an error like the following:

psql: could not connect to server: Connection refused
        Is the server running on host "server.com" and accepting
        TCP/IP connections on port 5432?

Well of course it’s running, I thought, because I can access it from the command line with no problem. And it’s definitely running on port 5432; when I rewrote the startup script to use the version of postgres I installed (in place of the old version), I actually remember verifying this. So I went to a terminal window on my local system and tried to telnet to port 5432 on the server. No bones. We’ve got a firewall running, so I checked the rules, and of course postgres isn’t in the default rules. I added it, restarted the firewall, and voila, I can telnet to the port. But I still can’t connect. This time, I get an error about the pg_hba.conf file not existing. It turns out that in postgres, you have to associate IPs with users and databases in this conf file in order to be able to connect remotely. Not a big deal. In mysql, you have to do something similar, though it’s in a user table rather than in a conf file that requires the server to be restarted after changes (just sending a SIGHUP didn’t do it for me, though it allegedly will). So I tested this out by adding my IP and user/database associations, and I can suddenly connect from my desktop. I added my server’s IP so that my pb_hga.conf looked something like this:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust
# IPv4 remote connections:
host    all         dbuser    69.1.45.140/32        trust
host    all         dbuser    1.1.1.167/32       trust

But I still couldn’t connect from my apache box either through a PHP script or directly from the command line. It seemed to be timing out. Ah, but then I remembered that I’ve got a firewall on the Web server box too. I punched a hole through the firewall for connections going out from port 5432, and all of a sudden, my apache box can connect to and retrieve data from my database box. Woo hoo. Now I’m off to do the actual benchmark.