Help with patroni[raft]

[johnatannvmd]

Hello guys, recently I was trying to start patroni with raft backend. Everything going well, except that after starting patroni spams logs with messages on the replica hosts:

File "/usr/local/lib/python3.6/site-packages/patroni/postgresql/__init__.py", line 738, in get_replica_timeline
cur.execute('IDENTIFY_SYSTEM')
psycopg2.errors.InternalError_: cannot execute SQL commands in WAL sender for physical replication

In the postgres leader logs:

2021-06-30 08:44:34 UTC [56973-1] 10.24.37.216(54744) replicator@[unknown] ERROR:  cannot execute SQL commands in WAL sender for physical replication
2021-06-30 08:44:34 UTC [56974-1] 10.24.38.209(44014) replicator@[unknown] ERROR:  cannot execute SQL commands in WAL sender for physical replication
2021-06-30 08:44:44 UTC [57057-1] 10.24.37.216(54932) replicator@[unknown] ERROR:  cannot execute SQL commands in WAL sender for physical replication
2021-06-30 08:44:44 UTC [57058-1] 10.24.38.209(44184) replicator@[unknown] ERROR:  cannot execute SQL commands in WAL sender for physical replication

In the postgres replica logs:

127.0.0.1(36830) replicator@[unknown] ERROR:  cannot execute SQL commands in WAL sender for physical replication

Moreover, if I run this command on each server:

sudo -u postgres psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"

It would return correct values:

      systemid       | timeline |  xlogpos  |  dbname  
---------------------+----------+-----------+----------
 6979500111324012947 |        3 | 0/6000268 | postgres
(1 row)

Could you please help me with this?

PostgreSQL: 12 (3 hosts setup)
patroni: 2.0.2

[vitabaks]

@johnatannvmd

new version of psycopg2 was released, it seems that autocommit = True is ignored in the with connection block: psycopg/psycopg2@e5ad0ab

I can suggest to stick with psycopg2 <= 2.8.6 for now.
patroni/patroni#1969

In the next version of Patroni will be added compatibility with psycopg2 2.9 - patroni/patroni@e5bfd4f

[johnatannvmd]

Thanks a LOT! That helps:

pip3 install psycopg2-binary==2.8.6
pip3 install psycopg2==2.8.6

[vitabaks]

pip3 install psycopg2-binary==2.8.6

psycopg2-binary is not recommended for production, enough psycopg2== 2.8.6 package.

pip3 install psycopg2==2.8.6

This automation has not using PIP for for installing psycopg2 package, a package from the APT/YUM repository is used instead.
https://github.com/vitabaks/postgresql_cluster/blob/master/vars/Debian.yml#L40
https://github.com/vitabaks/postgresql_cluster/blob/master/vars/RedHat.yml#L50
By the way there is no available package version 2.9 and with this problem you would not face;)