accessing PostgreSQL from a remote client

Previously I went through the steps I took to install and configure PostgreSQL. I set this up on my web server machine through SSH.

My Mac also has the psql client installed on it (out of the box!) but by default PostgreSQL is configured to refuse connections from remote hosts.

Changing this was fairly simple, although I did encounter one gotcha.

First, you need to change the server config file to tell PostgreSQL where to listen, I found mine in /etc/postgresql/8.4/main/postgresql.conf, about 3 ‘screens’ down is this line:

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)

I removed the # and rather than changing localhost to *, I entered the external IP of the server. It doesn’t really make a lot of difference, but I try to keep things as specific as I can (when I remember).

The last comment was the gotcha. It took me a few round-trips and puzzled stares until I realised that this is the difference between:

$ sudo /etc/init.d/postgresql reload


$ sudo /etc/init.d/postgresql restart

for some applications, these are synonyms, but not this one.

our old friend /etc/postgresql/8.4/main/pg_hba.conf also needs tweaking to tell PostgreSQL what kind of users to let in:


# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all       md5
#host   all         all          md5 is my home static IP (it’s not actually, but that’s where I typed my actual IP address) so only connections from my home network will be let in. This time, just reloading the config works for these settings:

$ sudo /etc/init.d/postgresql reload

Now I can connect to my database directly, rather than having to SSH in first. I’m not sure it makes much difference in the grand scheme of things, but it feels neater.