Connection Error while connecting to PostgreSQL as postgres user?

Question

I am not able connect to PostgreSQL remotely using python and psycopg2:

Here is my code.

>>> import psycopg2
>>> conn_string = "host='localhost' dbname='mydb' user='postgres'"
>>> print "Connecting to database\n     ->%s" % (conn_string)
    Connecting to database
      ->host='localhost' dbname='mydb' user='postgres'
>>> conn = psycopg2.connect(conn_string)

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/tools/lib/python2.7/site-packages/psycopg2/__init__.py", line 164, in connect
conn = _connect(dsn, connection_factory=connection_factory, async=async)
psycopg2.OperationalError: could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?

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

The password is not set for postgres user.

Generally, I can connect to database by running following method on host.

 1. SSH to box
 2. su - postgres
 3. psql
 4. \c mydb

The server runs PostgreSQL 9.1.

I am not able connect to psql remotely: you're not even trying with host=localhost, unless you have set up a SSH tunnel that you're not telling about. — Daniel Vérité
Well, "Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432?" ? — Craig Ringer
@DanielVérité I've found that newer users seem to say "remote server" when they're talking about ssh'ing into a box, then making a connection from code running on the remote machine to a database on the same remote host. It's a "remote server" to them, even though the remote-ness is irrelevent for the purpose of the database connection. This could be such a case, going by the notes at the end. — Craig Ringer
@IpsIds Are you running that Python code by ssh'ing into the remote server and running it there? Or is it running on your computer, rather than running on the remote server? — Craig Ringer

Craig Ringer Craig Ringer · Accepted Answer · 2014-09-26T02:06:06

You're trying to connect to PostgreSQL on localhost using a script running on your computer, but there's no PostgreSQL server running there.

For this to work, you'd have to ssh to the remote server, then run your Python script there, where the PostgreSQL server is "local" relative to the Python script.

(That's why running psql works - because you're running it on the remote server, where PostgreSQL is "local" relative to psql).

Alternately, you could:

Use an SSH tunnel to forward the PostgreSQL port from the local computer to the remote one; or
Connect directly over TCP/IP to the remote PostgreSQL server using its host name or IP address, after enabling remote connections on the server.

Note that just putting the server's IP address or host name into the connection string instead of localhost will not work unless you also configure the server to accept remote connections. You must set listen_addresses to listen for non-local connections, add any required firewall rules, set pg_hba.conf to permit connections from remote machines, and preferably set up SSL. All this is covered in the Client Authentication chapter of the PostgreSQL user manual.

You'll probably find an SSH tunnel simpler and easier to understand.

Connection Error while connecting to PostgreSQL as postgres user?

1 Answers