51

There seems to be some hardware problems on the router of the server my python software runs on. The connection to the database only is successful about every third time. So a psycopg2.connect() can take up to 5 minutes before I get a timeout exception.

2014-12-23 15:03:12,461 - ERROR - could not connect to server: Connection timed out Is the server running on host "172.20.19.1" and accepting

That's the code I'm using:

# Connection to the DB try: db = psycopg2.connect(host=dhost, database=ddatabase, user=duser, password=dpassword) cursor = db.cursor(cursor_factory=psycopg2.extras.DictCursor) except psycopg2.DatabaseError, err: print(str(err)) logging.error(str(err)) logging.info('program terminated') sys.exit(1)

I tried some timeout additions for the query, but that didn't help, since the connection didn't get established at all.

Is there a way I can stop the program immediately, when the connection couldn't be established?

Improve this question
1
  • perhaps blocked by some sort of application firewall? Commented Apr 17, 2018 at 23:06

1 Answer 1

Reset to default
88

When using the keyword arguments syntax to the connect function it is possible to use any of the libpg supported connection parameters. Among those there is connect_timeout in seconds:

db = psycopg2.connect ( host=dhost, database=ddatabase, user=duser, password=dpassword, connect_timeout=3 )

http://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS

http://initd.org/psycopg/docs/module.html

A connection time out raises an OperationalError exception.

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

4 Comments

Going to comment first, though this might merit new ques: In my case I am getting the timeout, as well as this prompt: Is the server running on host "xxxxxx.xxxxxx.us-west-1.rds.amazonaws.com" (xx.x.xxx.xxx) and accepting TCP/IP connections on port 5432?. The answer is indeed yes to those. In fact, I can make the connection in a node variation of this same tool, yet remain unable to connect via the psycopg2 version I've written.
if you can do psql "some connection string" then psycopg2.connect(dsn="some connection string" ) should also work.
What's the difference between the timeout options in PGOPTIONS and the timeout options as part of libpq connect parameters?
@kuanb You can also get this error if the user you're connecting as doesn't have an entry in the HBA file yet, or if you do have an entry in the HBA file already, you might not have had the server reload the HBA config yet. So, assuming the system's firewall already allows connections to postgres from your host on the given port, make sure that you have an entry in the database's HBA file, and after that, be sure to either SELECT pg_reload_conf(); or run pg_ctl reload to ensure that Postgres incorporates the changes.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.