Error Information , this’s i use haproxy shared ip and port to connect to postgres 15 on linux sytsem
[root@postgres_node1 ~]# psql -h 192.168.80.70 -p 5000 -U postgres
psql: error: connection to server at “192.168.80.70”, port 5000 failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
[root@postgres_node2 ~]# psql -h 192.168.80.70 -p 5000 -U postgres
psql: error: connection to server at “192.168.80.70”, port 5000 failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
[root@postgres_node3 ~]# psql -h 192.168.80.70 -p 5000 -U postgres
psql: error: connection to server at “192.168.80.70”, port 5000 failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
and Mysql service information in bellowing :
[root@postgres_node1 ~]# sudo systemctl -l status patroni
● patroni.service - Runners to orchestrate a high-availability PostgreSQL
Loaded: loaded (/usr/lib/systemd/system/patroni.service; disabled; vendor preset: disabled)
Active: active (running) since Thu 2023-11-23 21:06:32 EST; 22s ago
Main PID: 1716 (patroni)
CGroup: /system.slice/patroni.service
├─1716 /usr/bin/python3 /usr/bin/patroni /etc/patroni/patroni.yml
├─1754 /usr/pgsql-15/bin/postgres -D /var/lib/pgsql/15/data --config-file=/var/lib/pgsql/15/data/postgresql.conf --listen_addresses=192.168.80.71 --port=5432 --cluster_name=pg_cluster --wal_level=replica --hot_standby=on --max_connections=100 --max_wal_senders=10 --max_prepared_transactions=0 --max_locks_per_transaction=64 --track_commit_timestamp=off --max_replication_slots=10 --max_worker_processes=8 --wal_log_hints=on
├─1756 postgres: pg_cluster: logger
├─1757 postgres: pg_cluster: checkpointer
├─1758 postgres: pg_cluster: background writer
├─1764 postgres: pg_cluster: postgres postgres 192.168.80.71(45174) idle
├─1768 postgres: pg_cluster: postgres postgres 192.168.80.71(45176) idle
├─1790 postgres: pg_cluster: walwriter
├─1791 postgres: pg_cluster: autovacuum launcher
├─1792 postgres: pg_cluster: logical replication launcher
├─1826 postgres: pg_cluster: walsender replicator 192.168.80.73(52492) streaming 0/44A3BC0
└─1827 postgres: pg_cluster: walsender replicator 192.168.80.72(54434) streaming 0/44A3BC0
Nov 23 21:06:35 postgres_node1 patroni[1716]: 192.168.80.71:5432 - accepting connections
Nov 23 21:06:35 postgres_node1 patroni[1716]: 2023-11-23 21:06:35,567 INFO: Got response from postgres_node3 http://192.168.80.73:8008/patroni: {“state”: “running”, “postmaster_start_time”: “2023-11-23 21:06:34.085010-05:00”, “role”: “replica”, “server_version”: 150005, “xlog”: {“received_location”: 71973424, “replayed_location”: 71973424, “replayed_timestamp”: null, “paused”: false}, “timeline”: 7, “cluster_unlocked”: true, “dcs_last_seen”: 1700791595, “database_system_identifier”: “7303753397857303071”, “patroni”: {“version”: “3.2.0”, “scope”: “pg_cluster”, “name”: “postgres_node3”}}
Nov 23 21:06:35 postgres_node1 patroni[1716]: 2023-11-23 21:06:35,568 INFO: Got response from postgres_node2 http://192.168.80.72:8008/patroni: {“state”: “running”, “postmaster_start_time”: “2023-11-23 21:06:34.089751-05:00”, “role”: “replica”, “server_version”: 150005, “xlog”: {“received_location”: 71973424, “replayed_location”: 71973424, “replayed_timestamp”: null, “paused”: false}, “timeline”: 7, “cluster_unlocked”: true, “dcs_last_seen”: 1700791595, “database_system_identifier”: “7303753397857303071”, “patroni”: {“version”: “3.2.0”, “scope”: “pg_cluster”, “name”: “postgres_node2”}}
Nov 23 21:06:35 postgres_node1 patroni[1716]: 2023-11-23 21:06:35,647 INFO: Software Watchdog activated with 25 second timeout, timing slack 15 seconds
Nov 23 21:06:35 postgres_node1 patroni[1716]: 2023-11-23 21:06:35,661 INFO: Dropped unknown replication slot ‘postgres_node3’
Nov 23 21:06:35 postgres_node1 patroni[1716]: 2023-11-23 21:06:35,665 INFO: Dropped unknown replication slot ‘postgres_node2’
Nov 23 21:06:35 postgres_node1 patroni[1716]: 2023-11-23 21:06:35,679 INFO: promoted self to leader by acquiring session lock
Nov 23 21:06:35 postgres_node1 patroni[1716]: server promoting
Nov 23 21:06:36 postgres_node1 patroni[1716]: 2023-11-23 21:06:36,756 INFO: no action. I am (postgres_node1), the leader with the lock
Nov 23 21:06:46 postgres_node1 patroni[1716]: 2023-11-23 21:06:46,735 INFO: no action. I am (postgres_node1), the leader with the lock
[root@postgres_node2 ~]# sudo systemctl -l status patroni
● patroni.service - Runners to orchestrate a high-availability PostgreSQL
Loaded: loaded (/usr/lib/systemd/system/patroni.service; disabled; vendor preset: disabled)
Active: active (running) since Thu 2023-11-23 21:06:31 EST; 22s ago
Main PID: 1725 (patroni)
CGroup: /system.slice/patroni.service
├─1725 /usr/bin/python3 /usr/bin/patroni /etc/patroni/patroni.yml
├─1763 /usr/pgsql-15/bin/postgres -D /var/lib/pgsql/15/data --config-file=/var/lib/pgsql/15/data/postgresql.conf --listen_addresses=192.168.80.72 --port=5432 --cluster_name=pg_cluster --wal_level=replica --hot_standby=on --max_connections=100 --max_wal_senders=10 --max_prepared_transactions=0 --max_locks_per_transaction=64 --track_commit_timestamp=off --max_replication_slots=10 --max_worker_processes=8 --wal_log_hints=on
├─1765 postgres: pg_cluster: logger
├─1766 postgres: pg_cluster: checkpointer
├─1767 postgres: pg_cluster: background writer
├─1768 postgres: pg_cluster: startup recovering 000000080000000000000004
├─1774 postgres: pg_cluster: postgres postgres 192.168.80.72(37476) idle
├─1779 postgres: pg_cluster: postgres postgres 192.168.80.72(37478) idle
└─1851 postgres: pg_cluster: walreceiver streaming 0/44A3BC0
Nov 23 21:06:35 postgres_node2 patroni[1725]: 2023-11-23 21:06:35,406 INFO: Local timeline=7 lsn=0/44A3A30
Nov 23 21:06:35 postgres_node2 patroni[1725]: server signaled
Nov 23 21:06:35 postgres_node2 patroni[1725]: 2023-11-23 21:06:35,696 INFO: following a different leader because i am not the healthiest node
Nov 23 21:06:36 postgres_node2 patroni[1725]: 2023-11-23 21:06:36,476 INFO: Lock owner: postgres_node1; I am postgres_node2
Nov 23 21:06:36 postgres_node2 patroni[1725]: 2023-11-23 21:06:36,482 INFO: Local timeline=7 lsn=0/44A3A30
Nov 23 21:06:36 postgres_node2 patroni[1725]: 2023-11-23 21:06:36,489 INFO: no action. I am (postgres_node2), a secondary, and following a leader (postgres_node1)
Nov 23 21:06:46 postgres_node2 patroni[1725]: 2023-11-23 21:06:46,470 INFO: Lock owner: postgres_node1; I am postgres_node2
Nov 23 21:06:46 postgres_node2 patroni[1725]: 2023-11-23 21:06:46,479 INFO: Local timeline=8 lsn=0/44A3B88
Nov 23 21:06:46 postgres_node2 patroni[1725]: 2023-11-23 21:06:46,506 INFO: primary_timeline=8
Nov 23 21:06:46 postgres_node2 patroni[1725]: 2023-11-23 21:06:46,520 INFO: no action. I am (postgres_node2), a secondary, and following a leader (postgres_node1)
[root@postgres_node3 ~]# sudo systemctl -l status patroni
● patroni.service - Runners to orchestrate a high-availability PostgreSQL
Loaded: loaded (/usr/lib/systemd/system/patroni.service; disabled; vendor preset: disabled)
Active: active (running) since Thu 2023-11-23 21:06:31 EST; 22s ago
Main PID: 1709 (patroni)
CGroup: /system.slice/patroni.service
├─1709 /usr/bin/python3 /usr/bin/patroni /etc/patroni/patroni.yml
├─1747 /usr/pgsql-15/bin/postgres -D /var/lib/pgsql/15/data --config-file=/var/lib/pgsql/15/data/postgresql.conf --listen_addresses=192.168.80.73 --port=5432 --cluster_name=pg_cluster --wal_level=replica --hot_standby=on --max_connections=100 --max_wal_senders=10 --max_prepared_transactions=0 --max_locks_per_transaction=64 --track_commit_timestamp=off --max_replication_slots=10 --max_worker_processes=8 --wal_log_hints=on
├─1749 postgres: pg_cluster: logger
├─1750 postgres: pg_cluster: checkpointer
├─1751 postgres: pg_cluster: background writer
├─1752 postgres: pg_cluster: startup recovering 000000080000000000000004
├─1758 postgres: pg_cluster: postgres postgres 192.168.80.73(57974) idle
├─1765 postgres: pg_cluster: postgres postgres 192.168.80.73(57980) idle
└─1834 postgres: pg_cluster: walreceiver streaming 0/44A3BC0
Nov 23 21:06:35 postgres_node3 patroni[1709]: 2023-11-23 21:06:35,399 INFO: Local timeline=7 lsn=0/44A3A30
Nov 23 21:06:35 postgres_node3 patroni[1709]: server signaled
Nov 23 21:06:35 postgres_node3 patroni[1709]: 2023-11-23 21:06:35,712 INFO: following a different leader because i am not the healthiest node
Nov 23 21:06:36 postgres_node3 patroni[1709]: 2023-11-23 21:06:36,473 INFO: Lock owner: postgres_node1; I am postgres_node3
Nov 23 21:06:36 postgres_node3 patroni[1709]: 2023-11-23 21:06:36,480 INFO: Local timeline=7 lsn=0/44A3A30
Nov 23 21:06:36 postgres_node3 patroni[1709]: 2023-11-23 21:06:36,487 INFO: no action. I am (postgres_node3), a secondary, and following a leader (postgres_node1)
Nov 23 21:06:46 postgres_node3 patroni[1709]: 2023-11-23 21:06:46,475 INFO: Lock owner: postgres_node1; I am postgres_node3
Nov 23 21:06:46 postgres_node3 patroni[1709]: 2023-11-23 21:06:46,487 INFO: Local timeline=8 lsn=0/44A3B88
Nov 23 21:06:46 postgres_node3 patroni[1709]: 2023-11-23 21:06:46,514 INFO: primary_timeline=8
Nov 23 21:06:46 postgres_node3 patroni[1709]: 2023-11-23 21:06:46,527 INFO: no action. I am (postgres_node3), a secondary, and following a leader (postgres_node1)
So . I check postgres.conf
[root@postgres_node1 ~]# grep “listen” /var/lib/pgsql/15/data/postgresql.conf
listen_addresses = ‘192.168.80.71’
[root@postgres_node2 ~]# grep “listen” /var/lib/pgsql/15/data/postgresql.conf
listen_addresses = ‘192.168.80.72’
[root@postgres_node3 ~]# grep “listen” /var/lib/pgsql/15/data/postgresql.conf
listen_addresses = ‘192.168.80.73’
[root@postgres_node3 ~]# grep “port” /var/lib/pgsql/15/data/postgresql.conf
port = ‘5432’
primary_conninfo = ‘user=replicator passfile=/tmp/pgpass host=192.168.80.71 port=5432 sslmode=prefer application_name=postgres_node3 gssencmode=prefer channel_binding=prefer’
[root@postgres_node2 ~]# grep “port” /var/lib/pgsql/15/data/postgresql.conf
port = ‘5432’
primary_conninfo = ‘user=replicator passfile=/tmp/pgpass host=192.168.80.71 port=5432 sslmode=prefer application_name=postgres_node2 gssencmode=prefer channel_binding=prefer’
[root@postgres_node1 ~]# grep “port” /var/lib/pgsql/15/data/postgresql.conf
port = ‘5432’
and network Port information ,
[root@postgres_node1 ~]# netstat -anp | more
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 192.168.80.71:2380 0.0.0.0:* LISTEN 1148/etcd
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1143/sshd
tcp 0 0 0.0.0.0:7000 0.0.0.0:* LISTEN 1842/haproxy
tcp 0 0 192.168.80.71:5432 0.0.0.0:* LISTEN 1754/postgres
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1331/master
tcp 0 0 0.0.0.0:6432 0.0.0.0:* LISTEN 1787/pgbouncer
tcp 0 0 192.168.80.70:5000 0.0.0.0:* LISTEN 1842/haproxy <<<<<<
tcp 0 0 192.168.80.71:8008 0.0.0.0:* LISTEN 1716/python3
tcp 0 0 192.168.80.70:5001 0.0.0.0:* LISTEN 1842/haproxy <<<<<
tcp 0 0 192.168.80.71:2379 0.0.0.0:* LISTEN 1148/etcd
[root@postgres_node2 ~]# netstat -anp | more
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 192.168.80.72:2379 0.0.0.0:* LISTEN 1148/etcd
tcp 0 0 192.168.80.72:2380 0.0.0.0:* LISTEN 1148/etcd
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1144/sshd
tcp 0 0 0.0.0.0:7000 0.0.0.0:* LISTEN 1865/haproxy
tcp 0 0 192.168.80.72:5432 0.0.0.0:* LISTEN 1763/postgres
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1363/master
tcp 0 0 0.0.0.0:6432 0.0.0.0:* LISTEN 1798/pgbouncer
tcp 0 0 192.168.80.70:5000 0.0.0.0:* LISTEN 1865/haproxy <<<<<
tcp 0 0 192.168.80.72:8008 0.0.0.0:* LISTEN 1725/python3
tcp 0 0 192.168.80.70:5001 0.0.0.0:* LISTEN 1865/haproxy <<<<<
[root@postgres_node3 ~]# netstat -anp | more
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 192.168.80.70:5000 0.0.0.0:* LISTEN 1848/haproxy <<<<<
tcp 0 0 192.168.80.73:8008 0.0.0.0:* LISTEN 1709/python3
tcp 0 0 192.168.80.70:5001 0.0.0.0:* LISTEN 1848/haproxy <<<<<
tcp 0 0 192.168.80.73:2379 0.0.0.0:* LISTEN 1139/etcd
tcp 0 0 192.168.80.73:2380 0.0.0.0:* LISTEN 1139/etcd
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1131/sshd
tcp 0 0 0.0.0.0:7000 0.0.0.0:* LISTEN 1848/haproxy
tcp 0 0 192.168.80.73:5432 0.0.0.0:* LISTEN 1747/postgres
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1327/master
tcp 0 0 0.0.0.0:6432 0.0.0.0:* LISTEN 1781/pgbouncer
and this’s my haproxy -vv options information
Node1:
[root@postgres_node1 ~]# haproxy -vv
HA-Proxy version 1.5.18 2016/05/10
Copyright 2000-2016 Willy Tarreau willy@haproxy.org
Build options :
TARGET = linux2628
CPU = generic
CC = gcc
CFLAGS = -O2 -g -fno-strict-aliasing -DTCP_USER_TIMEOUT=18
OPTIONS = USE_LINUX_TPROXY=1 USE_GETADDRINFO=1 USE_ZLIB=1 USE_REGPARM=1 USE_OPENSSL=1 USE_PCRE=1
Default settings :
maxconn = 2000, bufsize = 16384, maxrewrite = 8192, maxpollevents = 200
Encrypted password support via crypt(3): yes
Built with zlib version : 1.2.7
Compression algorithms supported : identity, deflate, gzip
Built with OpenSSL version : OpenSSL 1.0.2k-fips 26 Jan 2017
Running on OpenSSL version : OpenSSL 1.0.2k-fips 26 Jan 2017
OpenSSL library supports TLS extensions : yes
OpenSSL library supports SNI : yes
OpenSSL library supports prefer-server-ciphers : yes
Built with PCRE version : 8.32 2012-11-30
PCRE library supports JIT : no (USE_PCRE_JIT not set)
Built with transparent proxy support using: IP_TRANSPARENT IPV6_TRANSPARENT IP_FREEBIND
Available polling systems :
epoll : pref=300, test result OK
poll : pref=200, test result OK
select : pref=150, test result OK
Total: 3 (3 usable), will use epoll.
Node2:
[root@postgres_node2 ~]# haproxy -vv
HA-Proxy version 1.5.18 2016/05/10
Copyright 2000-2016 Willy Tarreau willy@haproxy.org
Build options :
TARGET = linux2628
CPU = generic
CC = gcc
CFLAGS = -O2 -g -fno-strict-aliasing -DTCP_USER_TIMEOUT=18
OPTIONS = USE_LINUX_TPROXY=1 USE_GETADDRINFO=1 USE_ZLIB=1 USE_REGPARM=1 USE_OPENSSL=1 USE_PCRE=1
Default settings :
maxconn = 2000, bufsize = 16384, maxrewrite = 8192, maxpollevents = 200
Encrypted password support via crypt(3): yes
Built with zlib version : 1.2.7
Compression algorithms supported : identity, deflate, gzip
Built with OpenSSL version : OpenSSL 1.0.2k-fips 26 Jan 2017
Running on OpenSSL version : OpenSSL 1.0.2k-fips 26 Jan 2017
OpenSSL library supports TLS extensions : yes
OpenSSL library supports SNI : yes
OpenSSL library supports prefer-server-ciphers : yes
Built with PCRE version : 8.32 2012-11-30
PCRE library supports JIT : no (USE_PCRE_JIT not set)
Built with transparent proxy support using: IP_TRANSPARENT IPV6_TRANSPARENT IP_FREEBIND
Available polling systems :
epoll : pref=300, test result OK
poll : pref=200, test result OK
select : pref=150, test result OK
Total: 3 (3 usable), will use epoll.
Node3:
[root@postgres_node3 ~]# haproxy -vv
HA-Proxy version 1.5.18 2016/05/10
Copyright 2000-2016 Willy Tarreau willy@haproxy.org
Build options :
TARGET = linux2628
CPU = generic
CC = gcc
CFLAGS = -O2 -g -fno-strict-aliasing -DTCP_USER_TIMEOUT=18
OPTIONS = USE_LINUX_TPROXY=1 USE_GETADDRINFO=1 USE_ZLIB=1 USE_REGPARM=1 USE_OPENSSL=1 USE_PCRE=1
Default settings :
maxconn = 2000, bufsize = 16384, maxrewrite = 8192, maxpollevents = 200
Encrypted password support via crypt(3): yes
Built with zlib version : 1.2.7
Compression algorithms supported : identity, deflate, gzip
Built with OpenSSL version : OpenSSL 1.0.2k-fips 26 Jan 2017
Running on OpenSSL version : OpenSSL 1.0.2k-fips 26 Jan 2017
OpenSSL library supports TLS extensions : yes
OpenSSL library supports SNI : yes
OpenSSL library supports prefer-server-ciphers : yes
Built with PCRE version : 8.32 2012-11-30
PCRE library supports JIT : no (USE_PCRE_JIT not set)
Built with transparent proxy support using: IP_TRANSPARENT IPV6_TRANSPARENT IP_FREEBIND
Available polling systems :
epoll : pref=300, test result OK
poll : pref=200, test result OK
select : pref=150, test result OK
Total: 3 (3 usable), will use epoll.
Please , Any someone help me , tanks.