Thread: connection timeout with psycopg2

Hi there,

I have been testing out the following architecture for PostgreSQL HA.

             +---------+      +-----+   VIP   +----+      |     +---------+    |      |                    |
+------v-------+     +------v-------+
|  pgBouncer   |     |  pgBouncer   |
|      +       |     |      +       |
| keepalived   |     |  keepalived  |
+------+-------+     +------+-------+      |                    |      |                    |      |                    |
+------v-------+     +------v-------+
|              |     |              |
|   HAProxy    |     |   HAProxy    |
|              |     |              |
+------+-------+     +------+-------+      |                    |      +--------------------+      |                    |      |                    | +----v----+          +----v----+ |         |          |         | |         |          |         | |  PG01   |          |  PG02   | |         |          |         | |(patroni)|          |(patroni)| |         |          |         | +---------+          +---------+

I'm using this python script for checking the failover events in pgBouncer, HAProxy and Patroni (PostgreSQL HA solution).

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# vim:fenc=utf-8

import psycopg2

ISOLEVEL = psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT

import time
from datetime import datetime

user = 'postgres'
password = 'secretpassword'

host = 'localhost'
port = '6432'
database = 'test'
LIMIT_RETRIES = 10

class DB():   def __init__(self, user, password, host, port, database, reconnect):       self.user = user       self.password = password       self.host = host       self.port = port       self.database = database       self._connection = None       self._cursor = None       self.reconnect = reconnect       self.init()
   def connect(self,retry_counter=0):       if not self._connection:           try:               self._connection = psycopg2.connect(user = self.user, password = self.password, host = self.host, port = self.port, database = self.database, connect_timeout = 3)               retry_counter = 0               self._connection.autocommit = True               return self._connection           except psycopg2.OperationalError as error:               if not self.reconnect or retry_counter >= LIMIT_RETRIES:                   raise error               else:                   retry_counter += 1                   print("got error {}. reconnecting {}".format(str(error).strip(), retry_counter))                   time.sleep(5)                   self.connect(retry_counter)           except (Exception, psycopg2.Error) as error:               raise error
   def cursor(self):       if not self._cursor or self._cursor.closed:           if not self._connection:               self.connect()           self._cursor = self._connection.cursor()           return self._cursor
   def execute(self, query, retry_counter=0):       try:           self._cursor.execute(query)           retry_counter = 0       except (psycopg2.DatabaseError, psycopg2.OperationalError) as error:           if retry_counter >= LIMIT_RETRIES:               raise error           else:               retry_counter += 1               print("got error {}. retrying {}".format(str(error).strip(), retry_counter))               time.sleep(1)               self.reset()               self.execute(query, retry_counter)       except (Exception, psycopg2.Error) as error:           raise error
   def reset(self):       self.close()       self.connect()       self.cursor()
   def close(self):       if self._connection:           if self._cursor:               self._cursor.close()           self._connection.close()           print("PostgreSQL connection is closed")       self._connection = None       self._cursor = None
   def init(self):       self.connect()       self.cursor()

db = DB(user=user, password=password, host=host, port=port, database=database, reconnect=True)
db.execute("create table if not exists t1 (id integer);")
i = 0
while True:   db.execute("insert into t1(id) values(1);")   if i % 100 == 0:       print("%s: %d" % (datetime.now(), i))   i = i+1

When running this python script against the pgBouncer VIP it keeps inserting data into the database. Then I stop one of the HAProxy service (where the VIP lives) the connection it hangs and never goes on. The VIP is on the other node but the client/app it doesn't notice and it keeps waiting for 5 minutes and finally continues. I've been looking for some default value of 5min with no luck.

$ python insert.py 
2019-10-15 10:01:51.817585: 0
2019-10-15 10:01:51.901091: 100
2019-10-15 10:01:52.031583: 200
2019-10-15 10:01:52.126565: 300
2019-10-15 10:01:52.216502: 400
2019-10-15 10:01:52.307157: 500
2019-10-15 10:01:52.400867: 600
2019-10-15 10:01:52.497239: 700
2019-10-15 10:01:52.655689: 800
2019-10-15 10:01:52.777883: 900
got error server conn crashed?             <<<<<<<<<<<<<<<<< HAProxy stopped manually to force the VIP to move to the other node
server closed the connection unexpectedly       This probably means the server terminated abnormally       before or while processing the request.. retrying 1
PostgreSQL connection is closed
^C^C^C^C^C                                <<<<<<<<<<<<<<<<<< The connection gets stuck (kill PID)

I've tried exactly the same code logic in Java (using PostgreSQL JDBC) and dotnet core (using Npgsql) works fine with specifying this parameters socketTimeout (Java) and 'Command Timeout' (dotnet) respectively.

$ dotnet run
connection initialized
2019-10-15T08:27:28.843 0
2019-10-15T08:27:30.205 100
2019-10-15T08:27:31.566 200
got error: Exception while reading from stream. Retrying 1
connection closed
connection initialized
connection reset
2019-10-15T08:27:42.076 300             <<<<<<<<<<<< the app notices something wrong with the existing connection and does a reset
2019-10-15T08:27:43.461 400
2019-10-15T08:27:44.843 500
2019-10-15T08:27:46.244 600
2019-10-15T08:27:47.637 700
2019-10-15T08:27:49.031 800
^C

In python and using psycopg2 (v2.8.3) I've not been able to inform in any way the application to reset the connection, retry and keep going on.

As the psycopg2 it's a wrapper for libpq, I've seen that in libpq (for postgresql 12) there is a new option called tcp_user_timeout. Note this options doesn't exist in previous versions of libpq.
So I've tried to install psycopg2 (instead of psycopg2-binary) with the libpq (v12) installed, so the psycopg2 is build against libpq (v12). Even so, it's not working as expected.

Environment:
Ubuntu 16.04 LTS
PostgreSQL 9.6.15
Patroni 1.6.0
pgBouncer 1.11.0
keepalived 2.0.18
HAProxy 1.6.3

I've also tried to catch different types of exceptions with no luck.

I would appreciate any guidance on this matter. I can give you more info if needed.

Thank!