Could someone provide a docker-compose.yml example?

[mths0x5f]

I know this is a completely newbie question, but I'm using Docker Swarm to deploy things and I can't "speak" Kubernetes.

For what I've found, I just need to use to setup HAProxy, etcd and spilo, right?

A docker-compose.yml more or less like the one supplied for Patroni testing is enough?

My apologies for such inconvenience but any help would be appreciated.

[mths0x5f]

I'm working on it, trying a lot of setups and figuring out some docs inconsistencies. I will add some notes while that.

• $PG_DATA and $PG_ROOT are not actually set to what is documented.

[mths0x5f]

version: '3'
services:

  etcd1:
    image: bitnami/etcd:3.4.3
    command: etcd -name etcd1
    environment:
      ETCD_ENABLE_V2: 'true'
      ALLOW_NONE_AUTHENTICATION: 'yes'
      ETCD_LISTEN_PEER_URLS: http://0.0.0.0:2380
      ETCD_INITIAL_ADVERTISE_PEER_URLS: http://10.0.0.11:2380
      ETCD_LISTEN_CLIENT_URLS: http://0.0.0.0:2379
      ETCD_ADVERTISE_CLIENT_URLS: http://10.0.0.11:2379
      ETCD_INITIAL_CLUSTER: etcd1=http://etcd1:2380,etcd2=http://etcd2:2380
      ETCD_INITIAL_CLUSTER_STATE: new
      ETCD_INITIAL_CLUSTER_TOKEN: pgEtcdCluster
    networks:
      network:
        ipv4_address: 10.0.0.11

  etcd2:
    image: bitnami/etcd:3.4.3
    command: etcd -name etcd2
    environment:
      ETCD_ENABLE_V2: 'true'
      ALLOW_NONE_AUTHENTICATION: 'yes'
      ETCD_LISTEN_PEER_URLS: http://0.0.0.0:2380
      ETCD_INITIAL_ADVERTISE_PEER_URLS: http://10.0.0.12:2380
      ETCD_LISTEN_CLIENT_URLS: http://0.0.0.0:2379
      ETCD_ADVERTISE_CLIENT_URLS: http://10.0.0.12:2379
      ETCD_INITIAL_CLUSTER: etcd1=http://etcd1:2380,etcd2=http://etcd2:2380
      ETCD_INITIAL_CLUSTER_STATE: new
      ETCD_INITIAL_CLUSTER_TOKEN: pgEtcdCluster
    networks:
      network:
        ipv4_address: 10.0.0.12

  postgres-node1:
    image: spilo:latest
    environment:
      ETCD_HOSTS: etcd1:2379,etcd2:2379
      PGPASSWORD_STANDBY: ${DB_PASS}
      PGPASSWORD_ADMIN: ${DB_PASS}
      PGPASSWORD_SUPERUSER: ${DB_PASS}
      SCOPE: pgCluster
    networks:
      network:
        ipv4_address: 10.0.0.21
    volumes:
      - db-data1:/home/postgres/pgdata

  postgres-node2:
    image: spilo:latest
    environment:
      ETCD_HOSTS: etcd1:2379,etcd2:2379
      PGPASSWORD_STANDBY: ${DB_PASS}
      PGPASSWORD_ADMIN: ${DB_PASS}
      PGPASSWORD_SUPERUSER: ${DB_PASS}
      SCOPE: pgCluster
    networks:
      network:
        ipv4_address: 10.0.0.22
    volumes:
      - db-data2:/home/postgres/pgdata

  haproxy:
    image: haproxy:2.0-alpine
    environment:
      MAX_CONN: 10000
      PG_NODE1_HOST: postgres-node1
      PG_NODE2_HOST: postgres-node2
    expose:
      - '2345'
    ports:
      - '2345:2345'
      - '7000:7000'
    networks:
      network:
    volumes:
      - ./conf/haproxy:/usr/local/etc/haproxy:ro

networks:
  network:
    ipam:
      config:
        - subnet: 10.0.0.1/24

volumes:
  db-data1:
  db-data2:

I came up with something like this, any comments about will be appreciated.

[sonix07]

@mths0x5f I do not work at zalando but I'm using this image with docker swarm. First of all this image already contains etcd so you do not need a separate one.

version: "3.6"

services:

 tsnode1:
  image: spiloimage:notlatest
  networks:
   - db-nw
  ports: 
   - "5432:5432"
   - "8008:8008"
  volumes:
   - "/home/tstest/run1:/etc/service/etcd/run"
   - "/home/tstest/spilo1/pgdata:/home/postgres/pgdata/"
   - "/home/tstest/etcd1:/tmp"
  environment:
   - PATRONI_POSTGRESQL_LISTEN=0.0.0.0:5432
   - SCOPE=sftsscope
  deploy:
   replicas: 1

 tsnode2:
  image: spiloimage:notlatest
  networks:
   - db-nw
  ports: 
   - "5433:5432"
   - "8009:8008"
  volumes:
   - "/home/tstest/run2:/etc/service/etcd/run"
   - "/home/tstest/spilo2/pgdata:/home/postgres/pgdata/"
   - "/home/tstest/etcd2:/tmp"
  environment:
   - PATRONI_POSTGRESQL_LISTEN=0.0.0.0:5432
   - SCOPE=sftsscope
  deploy:
   replicas: 1

 tsnode3:
  image: spiloimage:notlatest
  networks:
   - db-nw
  ports: 
   - "5434:5432"
   - "8010:8008"
  volumes:
   - "/home/tstest/run3:/etc/service/etcd/run"
   - "/home/tstest/spilo3/pgdata:/home/postgres/pgdata/"
   - "/home/tstest/etcd3:/tmp"
  environment:
   - PATRONI_POSTGRESQL_LISTEN=0.0.0.0:5432
   - SCOPE=sftsscope
  deploy: 
   replicas: 1


networks:
  db-nw:
    driver: overlay

and for the run file I've mounted is to bootstrap the etcd cluster inside the spilo image.

run1: (needs to be marked as executable)

#!/bin/sh -e

exec 2>&1
exec env -i /bin/etcd --data-dir /tmp/etcd.data --name tsnode1 --initial-advertise-peer-urls http://tsnode1:2380 --listen-peer-urls http://0.0.0.0:2380 --initial-cluster tsnode1=http://tsnode1:2380,tsnode2=http://tsnode2:2380,tsnode3=http://tsnode3:2380,tsnode4=http://tsnode4:2380,tsnode5=http://tsnode5:2380 --initial-cluster-state new --initial-cluster-token etcd-cluster-1 --advertise-client-urls 'http://0.0.0.0:2379,http://0.0.0.0:4001' --listen-client-urls 'http://0.0.0.0:2379,http://0.0.0.0:4001'

run2:

#!/bin/sh -e

exec 2>&1
exec env -i /bin/etcd --data-dir /tmp/etcd.data --name tsnode2 --initial-advertise-peer-urls http://tsnode2:2380 --listen-peer-urls http://0.0.0.0:2380 --initial-cluster tsnode1=http://tsnode1:2380,tsnode2=http://tsnode2:2380,tsnode3=http://tsnode3:2380,tsnode4=http://tsnode4:2380,tsnode5=http://tsnode5:2380 --initial-cluster-state new --initial-cluster-token etcd-cluster-1 --advertise-client-urls 'http://0.0.0.0:2379,http://0.0.0.0:4001' --listen-client-urls 'http://0.0.0.0:2379,http://0.0.0.0:4001'

run3:

#!/bin/sh -e

exec 2>&1
exec env -i /bin/etcd --data-dir /tmp/etcd.data --name tsnode3 --initial-advertise-peer-urls http://tsnode3:2380 --listen-peer-urls http://0.0.0.0:2380 --initial-cluster tsnode1=http://tsnode1:2380,tsnode2=http://tsnode2:2380,tsnode3=http://tsnode3:2380,tsnode4=http://tsnode4:2380,tsnode5=http://tsnode5:2380 --initial-cluster-state new --initial-cluster-token etcd-cluster-1 --advertise-client-urls 'http://0.0.0.0:2379,http://0.0.0.0:4001' --listen-client-urls 'http://0.0.0.0:2379,http://0.0.0.0:4001'

[mths0x5f]

Thank you! I hardly would've known this.

P.S.: From your example, setting PATRONI_POSTGRESQL_LISTEN is necessary?

[sonix07]

If I remember correctly I was unable to connect with pgadmin without the PATRONI_POSTGRESQL_LISTEN option, but it was quite some time ago...

[xirius]

Thank you both for sharing this info!

@mths0x5f Did you manage to successfully run your cluster and what stack do you use in the end ? Can you share it please ?

@sonix07 What image do you use (spiloimage:notlatest) ? Did you build it yourself ? Any directions how to build it or where to get it ?

Thanks in advance!

[xirius]

@sonix07 Ah I think I got it, you mean Spilo already contains etcd.

[pravnkr]

@xirius @sonix07 @mths0x5f @CyberDem0n @feikesteenbergen

1. Do we need to touch the dockerfile for this setup..what i am not getting is what is the benefit of having so many things like patroni, postgres, etcd in a single image. As per convention a container is supposed to run one process, isn't it ?
2. in the compose file above posted by @sonix07, he mounted a volume containing a run script for etcd. do we have to change something in docker file entrypoint for running the mounted script or is it something like the systemd daemon will catch it as a service and run it when the container starts.
3. Is it a production level setup for the situation where kubernetes is not an option as a orchestrator, this is the situation with us we were running docker swarm, if not can you give me some insight for seting up a 'ha postgres cluster' for docker swarm.

I am new to the cluster and postgres thing. Any help will be appreciated from anyone.

[sonix07]

@pravnkr no I did not edit the docker file. I build the image myself and it is running in production. You can run multiple processes in a container if you want, using runit in this case.

cd postgres-appliance
docker build  --build-arg PGVERSION=12  --build-arg COMPRESS=true -t  myspilobuild:1 .

If you need to have an application transparent failover process you will need to run haproxy in front of your spilo cluster. This is an example haproxy.cfg that works with a 5 node spilo setup.

global
	maxconn 100

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

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

resolvers docker_resolver
	nameserver dns 127.0.0.11:53

frontend master_postgresql
	bind *:5000
	default_backend backend_master

frontend replicas_postgresql
	bind *:5001
	default_backend backend_replicas

frontend patroni_api
	bind *:8008
	default_backend backend_api

backend backend_master
	option httpchk OPTIONS /master
	server dbnode1 spilo1:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
	server dbnode2 spilo2:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
	server dbnode3 spilo3:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
	server dbnode4 spilo4:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
	server dbnode5 spilo5:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4

backend backend_replicas
	option httpchk OPTIONS /replica
	server dbnode1 spilo1:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
	server dbnode2 spilo2:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
	server dbnode3 spilo3:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
	server dbnode4 spilo4:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
	server dbnode5 spilo5:5432 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4

backend backend_api
	option httpchk OPTIONS /master
	server dbnode1 spilo1:8008 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
	server dbnode2 spilo2:8008 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
	server dbnode3 spilo3:8008 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
	server dbnode4 spilo4:8008 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4
	server dbnode5 spilo5:8008 maxconn 100 check port 8008 resolvers docker_resolver resolve-prefer ipv4

Just one word of advice that would have saved me a lot of time, etcd is very sensitive to spikes in latency (this can cause a new master being elected).

[pravnkr]

Thanks @sonix07 for the prompt response.
so you are basically saying that I should keep a load balancer (HAProxy) in front of my spilo cluster, where the cluster have say 5 nodes(services) where each node is running a postgres, patroni and etcd in it(possibly some of these nodes may not be running the etcd but only patroni and postgres in it).

am i correct ?
just last few questions...sorry in case if you felt i am hijacking you with my questions.

1. What is HAProxy is supposed to do here. does it load balance the db request b/w the spilo nodes. if yes how does it come to know that the request is a kind of insert/update and should go to master. i just need some plain language interpretation of it.
2. I was actually exploring the spilo dockerfile where i see the image also has a pgbouncer in it. how can we use it as per architecture point of view in this cluster where should i place it in the cluster. actually we need it for sharing the connections b/w multiple micro-services. this seems complex because in case of failover the pgbouncer needs to update the connection pool as per the available postgres nodes.

@sonix07 wrote:
"Just one word of advice that would have saved me a lot of time, etcd is very sensitive to spikes in latency (this can cause a new master being elected)."
I don't get this.

just help me with these last questions if you can..That will be a great help for me.

[sonix07]

@pravnkr with this spilo the etcd service is started automatically so you will run 1 etcd per DB node anyway, maybe this can be turned off but I have not looked into it.

1. you can only perform inserts/updates on the manager node but you can still select from replica nodes, haproxy will automatically figure out who the manager and replica node is (through patroni) so you do not need to put this logic into your application. It will give you an endpoint to always talk to the manager node and an enpoint to always talk to the read/replica node.
   If a failover happens it will take a couple of seconds to transition, when you failover all the time everything becomes unresponsive, this is why I said that etcd is sensitive to latency and may require some tuning depending on your network setup and I/O latency.
2. sorry I'm not very familiar with the pgbouncer.

[xirius]

@sonix07 Hi, I updated my HAProxy to 2.2 and now it reports that backend_master, backend_replicas and backend_api are all DOWN. When I downgrade to 2.1 it works again. I guess they changed something in the interpretation of the config but I don't know HAProxy enough to figure out what is wrong.
Any idea what has to be updated in backend sections to make it work with 2.2+ ?

[sonix07]

@xirius haven't looked into it at all. maybe the changelog of haproxy could give some hint?

[xirius]

@sonix07 I looked at the logs but it doesn't give me any clue unfortunately:

[NOTICE] 236/130905 (6) : haproxy version is 2.2.2
[WARNING] 236/130905 (6) : Server be-postgres-master/dbnode3 is DOWN, reason: Layer7 invalid response, info: "TCPCHK got an empty response at step 1", check duration: 7ms. 2 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
[WARNING] 236/130905 (6) : Server be-postgres-master/dbnode2 is DOWN, reason: Layer7 invalid response, info: "TCPCHK got an empty response at step 1", check duration: 6ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
[WARNING] 236/130905 (6) : Server be-postgres-replicas/dbnode1 is DOWN, reason: Layer7 invalid response, info: "TCPCHK got an empty response at step 1", check duration: 13ms. 0 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
[ALERT] 236/130905 (6) : backend 'be-postgres-master' has no server available!

There is an announcement about HAProxy 2.2 release where they say there are some changes to the health checks but I cannot figure out what has to be changed.

[dansou901]

@sonix07 something I don't get yet: how do you connect your application to the postgres DB in the cluster? What is the public facing name and how do you configure that?

[sonix07]

you connect to HAProxy on port 5000 (makes sure you always connect to the manager node with read and write access to the DB)
Port 5001 will give you read only access on the replica nodes. but you can also connect to the individual nodes on port 5432,5433 and 5434 as seen in the example above if you prefer.

[dansou901]

So haproxy is the single point of failure in your example, right? Or is that configured in HA?

[sonix07]

of course you would run more than one instance of HAProxy to avoid it being a SPOF... but you could also talk to patroni directly via rest (port 8008 in my example) and have the logic for failover built in to your application.

[Nutties93]

@sonix07
Sorry. I'm a little new to this.

• "/home/tstest/run3:/etc/service/etcd/run"
• "/home/tstest/spilo3/pgdata:/home/postgres/pgdata/"
• "/home/tstest/etcd3:/tmp"

/home/tstest/run3 , /home/tstest/spilo3/pgdata, /home/tstest/etcd3. Can you explain this? These resides in the host directory right? Am I suppose to create a volume in docker and include the run3 file here? What do /spilo3/pgdata and /etcd3 contains?

[jifox]

Hi,

Is there a complete docker stack example including HAProxy with auto failover/-failback somewhere on github available?