On 10/16/19 2:29 AM, Vicente Juan Tomas Monserrat wrote:
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.
Observations and comments:
1) I would point out there is a Psycopg list:
https://www.postgresql.org/list/psycopg/
2) I am not sure where the 5 minutes comes in. I see LIMIT_RETRIES = 10
and a sleep of 5 sec between retries.
3) Where did this:
"got error server conn crashed? "
come from? I don't see that in the code.
|$ 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!
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
