Connecting to remote server with pgadmin

Question

I'm using this tutorial from Linode: https://library.linode.com/databases/postgresql/pgadmin-macos-x to connect PGAdmin on my local machine to a databas on a remote server.

It provides the script copied below which I'm supposed to run by doing this

chmod +x postgresql-tunnel.pl
./postgresql-tunnel.pl start

However when I do it, I get this error message in the terminal:

michael$ ./postgresql-tunnel.pl start
michael@192.XXX.XXX.XXX's password: 
bind: Address already in use
channel_setup_fwd_listener: cannot listen to port: 5433
Could not request local forwarding.

and this error message from pg admin

The server doesn't accept connections: the connection library reports 
could not connect to server: Connection refused Is the server running on host         "localhost" (::1) and accepting TCP/IP connections on port 5433? 

I'm not sure if this is a setting I have to change on my local postgres installation or on the remote server.

Can you provide some guidance?

By the way, my local server is running when I try to connect.

These are the settings I'm using for my pgadmin connection with the exception that it's username 'michael' not 'allison' https://dl.dropboxusercontent.com/u/10328969/pgadmin.png

Note, following the instructions at this SO question Unable to connect PostgreSQL to remote database using pgAdmin, I did

/etc/postgresql/9.1/main/postgresql.conf:

listen_addresses = '*'

and

/etc/postgresql/9.1/main/pg_hba.conf:

host all all 0.0.0.0/0 md5

and the restarted the postgres server but I still get that error message.

Can you suggest how I might get the connection to work?

Connection script

#!/usr/bin/perl

# PostgreSQL Tunnel Tool for Mac OS X and Linux
# Copyright (c) 2010 Linode, LLC
# Author: Philip C. Paradis <pparadis@linode.com>
# Usage: postgresql-tunnel.pl [start|stop]
# Access a PostgreSQL database server via an SSH tunnel.

$local_ip    = "127.0.0.1";
$local_port  = "5433";
$remote_ip   = "127.0.0.1";
$remote_port = "5432";
$remote_user = "michael";
$remote_host = "192.XXX.XXX.XXX";

$a = shift;
$a =~ s/^\s+//;
$a =~ s/\s+$//;

$pid=`ps ax|grep ssh|grep $local_port|grep $remote_port`;
$pid =~ s/^\s+//;
@pids = split(/\n/,$pid);
foreach $pid (@pids)
{
 if ($pid =~ /ps ax/) { next; }
 split(/ /,$pid);
}

if (lc($a) eq "start")
{
 if ($_[0]) { print "Tunnel already running.\n"; exit 1; }
 else
 {
  system "ssh -f -L $local_ip:$local_port:$remote_ip:$remote_port $remote_user\@$remote_host -N";
  exit 0;
 }
}
elsif (lc($a) eq "stop")
{
 if ($_[0]) { kill 9,$_[0]; exit 0; }
 else { exit 1; }
}
else
{
 print "Usage: postgresql-tunnel.pl [start|stop]\n";
 exit 1;
}

Answer 1

Specify a different local port in the script. The specified port is already in use by something else, possibly a local PostgreSQL running on port 5433.

That's really just a toy wrapper around an SSH tunnel. Personally I tend to just fire up the ssh tunnel directly when I need that, or ssh into the remote machine and use psql on it locally.