Connecting to AWS Postgres database using Python: connection refused error

Question

I'm trying to query from an online Postgres database (slave) via SQLAlchemy. I'm able to create an SSH-tunnel and creating engine and session (see code below). However when trying to setup the cursor (cursor = session.connection().connection.cursor()), we get a connection refused error. Do you know what we should change in order to connect?

Some more considerations:

When I'm connection to this databases via DBeaver with same settings and credentials, it doesn't throw any errors
A colleague of mine is able to connect with same script (see below) and same SSH-key without any errors.
PostgreSQL 9.6.18 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
The database is managed by external party, so I'm not able to make changes directly, however on request we can.

The error:

sqlalchemy.exc.OperationalError: (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?

(Background on this error at: http://sqlalche.me/e/13/e3q8)

my_script.py:

from connection import create_connection

db_name = 'myDB'

session, engine = create_connection(
    db_name, ssh=True)

cursor = session.connection().connection.cursor()
cursor.execute(''' SELECT * from file limit 10 ''')

data = cursor.fetchall()

print(data[0])

session.close()
cursor.close()

connection.py:

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
import config

def create_connection(db_name, ssh=False):
    connection_id_db = config.CONNECTION_IDS[db_name]['DB_CONNECTION']

    if ssh:
        connection_id_ssh = config.CONNECTION_IDS[db_name]['SSH_CONNECTION']
        with sshtunnel.SSHTunnelForwarder(
                # Remote server IP and SSH port
                connection_id_ssh['REMOTE_SERVER'],
                ssh_username=connection_id_ssh['SSH_USERNAME'],
                ssh_private_key=connection_id_ssh['SSH_PRIVATE_KEY'],
                remote_bind_address=connection_id_ssh['REMOTE_BIND_ADDRESS'],
                local_bind_address=connection_id_ssh['LOCAL_BIND_ADDRESS']
        ) as server:  # PostgreSQL server IP and sever port on remote machine
            server.start()
    
    # connect to PostgreSQL
    engine = create_engine('postgresql://' +
                           connection_id_db['DB_USERNAME'] + ':' +
                           connection_id_db['DB_PASSWORD'] + '@' +
                           connection_id_db['DB_LOCAL_ADDRESS'] + ':' +
                           connection_id_db['DB_LOCAL_PORT'] + '/' +
                           connection_id_db['DB_NAME'], use_batch_mode=True)
    Session = sessionmaker(bind=engine)
    session = Session()
    print('Database session created')
    return session, engine

And the config.py:

CONNECTION_IDS = {
    'myDB': {
        'SSH_CONNECTION': {
            'REMOTE_SERVER': ('XXX.XXX.nl', 22),
            'SSH_USERNAME': 'ubuntu',
            'SSH_PRIVATE_KEY': '/location/to/.ssh/My_ssh_key.pem',
            'REMOTE_BIND_ADDRESS': ('XXX.XXX.eu-west-1.rds.amazonaws.com', 5432),
            'LOCAL_BIND_ADDRESS': ('localhost', 1234)
        },

        'DB_CONNECTION': {
            'DB_USERNAME': '<username>',
            'DB_PASSWORD': '<password>',
            'DB_LOCAL_ADDRESS': 'localhost',
            'DB_LOCAL_PORT': '5432',
            'DB_NAME': 'myDB'
        }
    },
}

'LOCAL_BIND_ADDRESS': ('localhost', 1234), 'DB_LOCAL_PORT': '5432', Shouldn't these be equal? — jjanes
– jjanes, Commented Dec 24, 2020 at 15:34

Daniel Vérité · Accepted Answer · 2020-12-24 15:36:38Z

1

The SSH tunnel is set up to accept connections on port 1234 locally, but the connection is attempted at port 5432.

If you don't have a PostgreSQL instance running locally (according to the error message, you don't), the port 5432 is available, so change in config.py:

'LOCAL_BIND_ADDRESS': ('localhost', 1234)

into:

'LOCAL_BIND_ADDRESS': ('localhost', 5432)

answered Dec 24, 2020 at 15:36

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

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

1 Comment

PaulH Over a year ago

This unfortunately doesn't do the trick. I still get the same error. Furthermore my colleague is able to connect using this localhost set on 1234. We changed it on purpose to different value than 5432, but can't recall why exactly.

Collectives™ on Stack Overflow

Connecting to AWS Postgres database using Python: connection refused error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related