Can't connect to postgres server over SSH through python

Question

I'm trying to SSH into a server and connect to an instance of postgres running on it. I'm able to SSH into the server and access the postgres server via termianl, and the port is 5432 in pg_settings. If it makes any difference, after SSH'g into the server with SSH_USER I have to switch users ('sudo su - postgres').

The error:

psycopg2.OperationalError: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

The code:

SSH_HOST = 'xxx.xxx.xxx.xxx'
SSH_FOREIGN_PORT = 22
SSH_USER = 'user1'
SSH_PASS = 'pass'
PORT = 5432
DBNAME = 'testdb'
USER = 'postgres'
PASS = ''

def main_psql():
    #try:
    with SSHTunnelForwarder((SSH_HOST, SSH_FOREIGN_PORT), ssh_username=SSH_USER, ssh_password=SSH_PASS, remote_bind_address=('localhost', PORT)) as server:
        server.start()
        cnx = psycopg2.connect(dbname=DB_NAME, user=USER, password=PASS)

EDIT: Error 2:

File "/Users/me/PycharmProjects/proj/src/import_psql.py", line 285, in main_psql
  cnx = psycopg2.connect(dbname=DB_NAME, user=USER, password=PASS, host='localhost', port=PORT) #, host='/var/run/postgresql')
File "/Users/me/PycharmProjects/proj/venv/lib/python2.7/site-packages/psycopg2/__init__.py", line 130, in connect
  conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: 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?

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

If I run netstat from the postgres user after SSH'g through terminal, I get:

-bash-4.2$ netstat -nl |grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN     
tcp6       0      0 :::5432                 :::*                    LISTEN     
unix  2      [ ACC ]     STREAM     LISTENING     26541    /var/run/postgresql/.s.PGSQL.5432
unix  2      [ ACC ]     STREAM     LISTENING     26543    /tmp/.s.PGSQL.5432

Zorg · Accepted Answer · 2018-03-07 01:41:11Z

1

You have to specify HOST and PORT into your psycopg2.connect(dbname=DB_NAME, user=USER, password=PASS, host=..., port=...). By default postgres client connects to Unix domain socket not TCP socket.

answered Mar 7, 2018 at 1:41

Zorg

6266 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

st2 tas Over a year ago

If I specify the host as localhost and the port as 5432 (which I believe is correct), I get the following error (added to main post).

Zorg Over a year ago

Then just test your ssh tunnel in bash. Maybe there is something wrong with SSHTunnelForwarder lib. Create a tunnel and connect to it with standard psql from the terminal.

Collectives™ on Stack Overflow

Can't connect to postgres server over SSH through python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related