43

I have a huge problem: 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 successfull about every third time. So a psycopg2.connect() can take up to 5 minutes before I get an 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 helped, since the connection didn't got established at all.

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

gulden
  • 505
  • 1
  • 7
  • 13

1 Answers1

77

When using the keyword arguments syntax to the connect function it is possible to use any of the libpd 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.

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • 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. – kuanb Mar 13 '16 at 20:37
  • if you can do `psql "some connection string"` then `psycopg2.connect(dsn="some connection string" )` should also work. – Jasen Apr 17 '18 at 23:04
  • What's the difference between the timeout options in `PGOPTIONS` and the timeout options as part of libpq connect parameters? – CMCDragonkai Apr 18 '20 at 06:19