I was trying to access a Postgres database on my WebFaction server using the PostgreSQL admin tool PgAdmin III. It took a little bit of Googling to figure it out, so here’s a quick guide for anyone in similar situations.
If your hosting provider, like mine, does not allow direct remote access to databases, you need to create an SSH tunnel so that accessing the default Postgres port, 5432, on your computer redirects to port 5432 on the server. For WebFaction accounts, that can be done like this
ssh -L 5432:127.0.0.1:5432 accountname@webXX.webfaction.com
where webXX refers to your WebFaction server.
If you see “bind: Address already in use”, it means that the postgresql service running on your computer on port 5432 is preventing the SSH tunnel from being set up on the same port. To stop your local postgresql service, on Ubuntu, you can do this
sudo service postgresql stop
When the postgresql service has stopped, you can set up the SSH tunnel.
Use the “Add server” option and provide the following configuration
- Host: 127.0.0.1
- Port: 5432
- Username: <remote Postgres username>
- Password: <remote Postgres password>
- Maintenance DB (only if you have trouble connecting to your server): <remote Postgres database name>