Connecting to postgres databases on remote Windows machines

There are three things you need to do to connect to a postgres server on a different Windows machine.

The first two are clearly documented, the third is assumed. However, depending on how your environment is configured, the third may be an utter pain of headbanging annoyance until you realise how simple it is.

Also, since some of the documentation I’ve read is all for linux, with a small addendum for Windows users that can be summarised as “Ha ha! L0sers!” I figured I’d write this reminder here, for any future versions of myself that forget this.

1. Configure pg_hba.conf

This is the file that tells your postgres server which machines it can accept connections from. You can use subnet masks to get lots of machines, or do them individually. Individually is a right pain, as you’ll need to restart the postgres instance before changes to pg_hba.conf take effect. Which will make you sad.

2. Configure postgres.conf

By default, postgres doesn’t listen for remote connections. You need to look for the line including “listen addresses” and uncomment it.

3. Open your port!

By default, postgres is listening on port 5432. By default, your Windows firewall settings may be configured to allow no network traffic except for all specially configured ports. So you can configure postgres settings until you’re blue in the face: if you don’t set up a rule in Windows Firewall to do this, your postgres server will never hear any connection requests, and attempts to connect from the other machine will fail. And fail. And fail.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.