Questions[patroni](server)

I'm trying to run haproxy on a docker, but it doesn't work.

1-I create haproxy.cfg under /etc/haproxy/haproxy.cfg:

global log 127.0.0.1 local2

chroot      /var/lib/haproxy
pidfile     /var/run/haproxy.pid
user        haproxy
group       haproxy
daemon

stats socket /var/lib/haproxy/stats

defaults mode tcp log global retries 3 timeout queue 1m timeout connect 10s timeout client 1m timeout server 1m timeout check 10s maxconn 3000

listen stats mode http bind *:7000 stats enable stats uri /

listen cluster_pgdocker bind *:5000 option httpchk http-check expect status 200 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server pg_node1 192.168.154.243:5432 maxconn 1000 check port 8008 server pg_node2 192.168.154.245:5432 maxconn 1000 check port 8008

2-then I create my Dockerfile under /etc/haproxy/:

FROM haproxy:2.3 COPY haproxy.cfg /usr/local/etc/haproxy/haproxy.cfg RUN mkdir --parents /var/lib/haproxy && chown -R haproxy:haproxy /var/lib/haproxy RUN mkdir /var/haproxy

3- Then I execute this command for create an image: docker build -t my-haproxy . 4- docker images: my-haproxy latest d40f9f668bf9 4 seconds ago 99.3MB haproxy 2.3 2739ba6dea2e 3 days ago 99.3MB

5- then I run this image for execute a container:docker run -d --name haproxy -p 7000:7000 my-haproxy:

a6675ebf4437c4eda0d3f120826dd19cee66418926036488e88bfc84896b2cf9 docker: Error response from daemon: driver failed programming external connectivity on endpoint haproxy (7025d8dc3dedcd1c8dcc2da067837639504bec52deadb33d639cd0ba54bc7e05): (iptables failed: iptables --wait -t nat -A DOCKER -p tcp -d 0/0 --dport 7000 -j DNAT --to-destination 172.17.0.2:7000 ! -i docker0: iptables: No chain/target/match by that name. (exit status 1)).

Please Can you help me and thanks for your support.

I have a problem with patroni right here

[postgres@localhost ~]$ patroni /etc/patroni.yml
2021-05-25 11:39:56,767 INFO: Selected new etcd server http://10.102.196.138:2379
2021-05-25 11:39:56,772 INFO: No PostgreSQL configuration items changed, nothing to reload.
2021-05-25 11:39:56,831 INFO: Lock owner: None; I am etcd1
2021-05-25 11:39:56,836 INFO: trying to bootstrap a new cluster
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

creating directory /data/patroni ... initdb: error: could not create directory "/data/patroni": Permission denied
pg_ctl: database system initialization failed
2021-05-25 11:39:56,877 INFO: removing initialize key after failed attempt to bootstrap the cluster
Traceback (most recent call last):
  File "/usr/local/bin/patroni", line 8, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.6/site-packages/patroni/__init__.py", line 170, in main
    return patroni_main()
  File "/usr/local/lib/python3.6/site-packages/patroni/__init__.py", line 138, in patroni_main
    abstract_main(Patroni, schema)
  File "/usr/local/lib/python3.6/site-packages/patroni/daemon.py", line 100, in abstract_main
    controller.run()
  File "/usr/local/lib/python3.6/site-packages/patroni/__init__.py", line 108, in run
    super(Patroni, self).run()
  File "/usr/local/lib/python3.6/site-packages/patroni/daemon.py", line 59, in run
    self._run_cycle()
  File "/usr/local/lib/python3.6/site-packages/patroni/__init__.py", line 111, in _run_cycle
    logger.info(self.ha.run_cycle())
  File "/usr/local/lib/python3.6/site-packages/patroni/ha.py", line 1457, in run_cycle
    info = self._run_cycle()
  File "/usr/local/lib/python3.6/site-packages/patroni/ha.py", line 1351, in _run_cycle
    return self.post_bootstrap()
  File "/usr/local/lib/python3.6/site-packages/patroni/ha.py", line 1247, in post_bootstrap
    self.cancel_initialization()
  File "/usr/local/lib/python3.6/site-packages/patroni/ha.py", line 1240, in cancel_initialization
    raise PatroniFatalException('Failed to bootstrap cluster')
patroni.exceptions.PatroniFatalException: 'Failed to bootstrap cluster'

Here is my patroni.yml

scope: postgres
namespace: /db/
name: etcd1

restapi:
    listen: 10.102.196.138:8008
    connect_address: 10.102.196.138:8008

etcd:
    hosts: 10.102.196.138:2379,10.102.196.139:2379,10.102.196.147:2379

bootstrap:
    dcs:
        ttl: 30
        loop_wait: 10
        retry_timeout: 10
        maximum_lag_on_failover: 1048576
        postgresql:
            use_pg_rewind: true

    initdb:
    - encoding: UTF8
    - data-checksums

    pg_hba:
    - host replication all 127.0.0.1/32 md5
    - host replication all 10.102.196.138/0 md5
    - host replication all 10.102.196.139/0 md5
    - host all all 0.0.0.0/0 md5

    users:
        admin:
            password: admin
            options:
                - createrole
                - createdb
 postgresql:
    listen: 10.102.196.138:5432
    connect_address: 10.102.196.138:5432
    data_dir: /data/patroni
    pgpass: /tmp/pgpass
    authentication:
        replication:
            username: replicator
            password: password
        superuser:
            username: postgres
            password: password
    parameters:
        unix_socket_directories: '.'

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false

[postgres@localhost ~]$ ls /data/
patroni_2021-05-25-11-03-59

Can someone help?

I followed this document while configuring patroni and I have a problem with patroni.service file like this https://snapshooter.com/learn/postgresql/postgresql-cluster-patroni

[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target

[Service]
Type=simple

User=postgres
Group=postgres

ExecStart=/usr/local/bin/patroni /etc/patroni.yml

KillMode=process

TimeoutSec=30

Restart=no

[Install]
WantedBy=multi-user.targ

Here is my patroni.yml

scope: postgres
namespace: /db/
name: etcd1

restapi:
    listen: 10.102.196.138:8008
    connect_address: 10.102.196.138:8008

etcd:
    hosts: 10.102.196.138:2379,10.102.196.139:2379,10.102.196.147:2379

bootstrap:
    dcs:
        ttl: 30
        loop_wait: 10
        retry_timeout: 10
        maximum_lag_on_failover: 1048576
        postgresql:
            use_pg_rewind: true

    initdb:
    - encoding: UTF8
    - data-checksums

    pg_hba:
    - host replication all 127.0.0.1/32 md5
    - host replication all 10.102.196.138/0 md5
    - host replication all 10.102.196.139/0 md5
    - host all all 0.0.0.0/0 md5

    users:
        admin:
            password: admin
            options:
                - createrole
                - createdb
 postgresql:
    listen: 10.102.196.138:5432
    connect_address: 10.102.196.138:5432
    data_dir: /data/patroni
    pgpass: /tmp/pgpass
    authentication:
        replication:
            username: replicator
            password: password
        superuser:
            username: postgres
            password: password
    parameters:
        unix_socket_directories: '.'

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false

And I got this error:

[root@localhost ~]# sudo systemctl status patroni -l
● patroni.service - Runners to orchestrate a high-availability PostgreSQL
   Loaded: loaded (/etc/systemd/system/patroni.service; disabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Mon 2021-05-24 23:24:26 +07; 7s ago
  Process: 3307 ExecStart=/usr/local/bin/patroni /etc/patroni.yml (code=exited, status=1/FAILURE)
 Main PID: 3307 (code=exited, status=1/FAILURE)

May 24 23:24:26 localhost.localdomain patroni[3307]: self.current_event = self.state()
May 24 23:24:26 localhost.localdomain patroni[3307]: File "/usr/local/lib64/python3.6/site-packages/yaml/parser.py", line 439, in parse_block_mapping_key
May 24 23:24:26 localhost.localdomain patroni[3307]: "expected <block end>, but found %r" % token.id, token.start_mark)
May 24 23:24:26 localhost.localdomain patroni[3307]: yaml.parser.ParserError: while parsing a block mapping
May 24 23:24:26 localhost.localdomain patroni[3307]: in "/etc/patroni.yml", line 1, column 1
May 24 23:24:26 localhost.localdomain patroni[3307]: expected <block end>, but found '<block mapping start>'
May 24 23:24:26 localhost.localdomain patroni[3307]: in "/etc/patroni.yml", line 38, column 2
May 24 23:24:26 localhost.localdomain systemd[1]: patroni.service: main process exited, code=exited, status=1/FAILURE
May 24 23:24:26 localhost.localdomain systemd[1]: Unit patroni.service entered failed state.
May 24 23:24:26 localhost.localdomain systemd[1]: patroni.service failed.

How to setup/make Haproxy send read-write request coming on same port 5432 from application, to backend servers of postgres-patroni cluster based on REST method.

I have three node postgres-patroni cluster running as docker service/containers behind Haproxy. I wanna sent read/get request coming from my-application on port 5432 to relicas of my cluster and sent write/(Delete|Post|Update) request coming from my-application also on port 5432 to master server.

I have tried to achieve it with below config. But didn't succeed as of now.

Any idea How to make Haproxy achieve above scenario.

Here is my Haproxy.cfg file

global
        maxconn 100

defaults
        log     global
        mode    tcp
        retries 2
        timeout client 30m
        timeout connect 4s
        timeout server 30m
        timeout check 5s
        # never fail on address resolution
        default-server init-addr none

resolvers docker_resolver
        nameserver dns 127.0.0.11:53
listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /

frontend master_postgresql
        bind *:5432
        acl is_post method "POST|UPDATE|DELETE"
        use_backend backend_master if is_post
        default_backend backend_master

frontend replicas_postgresql
        bind *:5432
        acl is_get  method GET
        use_backend backend_replicas  if is_get
        default_backend backend_replicas

frontend patroni_api
        bind *:8008
        default_backend backend_api

backend backend_master
        balance source
        hash-type consistent
        option httpchk OPTIONS /master
        server postgres1 postgres1:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
        server postgres2 postgres2:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
        server postgres3 postgres3:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4

backend backend_replicas
        option httpchk OPTIONS /replica
        server postgres1 postgres1:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
        server postgres2 postgres2:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
        server postgres3 postgres3:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4

backend backend_api
        option httpchk OPTIONS /master
        server postgres1 postgres1:8008 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
        server postgres2 postgres2:8008 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
        server postgres3 postgres3:8008 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4

I have 3 servers with Ubuntu 19.10, PostgreSQL 12.2 and Patroni 1.6.4 and I would like to have the following setup:

I want to have two servers with synchronised replication and I want to add a 3rd server with pglogical replication just to accept inserts to specific tables. no DELETE, TRUNCATE or anything else.

I log a huge amount of purchases per day, around 300M. I want to have to servers to already have the purchases history of the last 30 days, and the 3rd server which has a really big storage, will hold all the history of the purchases.

So far I have a patroni cluster with etcd for the 2 servers with the following configuration:

This is for the first and 2nd server I have the following configuration, of course the name of the server differs in each configuration:

scope: patroni_cluster_1
name: server_X

restapi:
  listen: 0.0.0.0:8008
  connect_address: X.X.X.X:8008

etcd:
  hosts: X.X.X.X:2379
  protocol: http

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout : 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_keep_segments: 100

  initdb:
  - encoding: UTF8
  - data-checksums

  pg_hba:
  - host replication replicator 0.0.0.0/0 md5
  - host all all 0.0.0.0/0 md5

postgresql:
  listen: 0.0.0.0:5432
  connect_address: X.X.X.X:5432
  synchronous_mode: true
  synchronous_mode_strict: false
  data_dir: /var/lib/postgresql/patroni_cluster_1/server_X/data
  bin_dir: /usr/lib/postgresql/12/bin
  authentication:
    replication:
      username: replicator
      password: XXXX
    superuser:
      username: postgres
      password: XXXX
  parameters:
    shared_preload_libraries: pglogical

I added pglogical as a shared library because of the 3rd server which I would like to know if I can fully setup with Patroni.

I read about how to setup pglogical with https://blog.dbi-services.com/postgresql-logical-replication-with-pglogical/ and I would like to know if I can fully configure the 3rd server with pglogical for inserts only.

I'm new to Patroni and I'm pretty lost so any information regarding this issue would be greatly appreciated.