Remotely accessing a PostgreSQL database through PgAdmin

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.

pgAdmin

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>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s