22

I'm trying to setup a PostgreSQL database on an Ubuntu machine. I would like to be able to access it using pgAdmin3 from a remote machine. How do I setup this?

I have installed the PostgreSQL database on Ubuntu using:

sudo apt-get install postgresql

In my /etc/postgresql/9.1/main/pg_hba.conf have I this line:

host    all    all    all    password

so it should accept connections from all IPv4-addresses and passwords should be sent in clear text (this is for development reasons).

If I run this command to see what services is running:

sudo netstat -tulpn

I can see these lines, that is showing that PostgreSQL is accepting connections on the default port:

tcp    0    0    127.0.0.1:5432    0.0.0.0:*    LISTEN
3561/postgres

When I try to connect to this PostgreSQL server from a remote computer on the same local network I get this error message:

Server doesn't listen

The server doesn't accept connections: the connection library reports

could not connect to server: Connection refused Is the server running on host "10.0.1.7" and accepting TCP/IP connections on port 5432?

I used postgres as username and no password. But I have also tried with postgres as password. On the local server I can login using:

sudo -u postgres psql postgres

How can I connect to a PostgreSQL database running on Ubuntu from a remote machine using pgAdmin3?

25

The line in your netstat report shows that the database is only listening on localhost:5432 (127.0.0.1) for incoming tcp connections.

Proto Recv-Q Send-Q Local Address   Foreign Address  State   PID/Program name
tcp        0      0 127.0.0.1:5432  0.0.0.0:*        LISTEN  3561/postgres

So it can only accept local tcp connections regardless of what permissions you've specified in pg_hba.conf. pg_hba.conf only specifies allowed connections, but does not specify what interfaces your service will listen to.

The addresses the server listens on is specified with the listen_addresses GUC in postgresql.conf. If you want the server to listen for remote connections you should specify the ip(s) you want it to listen on or * to listen on all available interfaces on the host.

To have your postgresql server listen on all interfaces on the host, you should have the following line in postgresql.conf:

listen_addresses = '*'
  • but I have already specified all in the pg_hba.conffile... so it should accept connetions from all ip-addresses. – Jonas Jun 1 '12 at 22:10
  • Perhaps I could have said it more clearly. pg_hba doesn't specify what the database listens on, only what protocol/db/user/remote-addr connections are permitted. – dbenhur Jun 1 '12 at 22:12
  • Ok, so how do I fix this? do you have any suggestions? – Jonas Jun 1 '12 at 22:15
  • 1
    you fix it by putting a line listen_addresses = '*' in postgresql.conf and restarting your server. – dbenhur Jun 1 '12 at 22:16
  • Thanks, this solved this problem. Now I just got problems with the default password :( – Jonas Jun 1 '12 at 22:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.