read/write separation for Postgesql cluster
hi all:
I've installed a HA gitlab instance according to our official guide:
https://docs.gitlab.com/13.11/ee/administration/reference_architectures/3k_users.html
3 pgbouncer node, 3 postgresql node(including patroni), 1 internal load balancer
The patroni service takes responsible for postgres' failover, pgbouncer provide available db connection pools, it seems all 3 pgbouncer always points to current leader postgresql, this is pgbouncer's config file(pgbouncer.ini) database section content:
[root@iZj6c539myfk5bafgdow4wZ ~]# cat /var/opt/gitlab/consul/databases.ini
[databases]
gitlabhq_production = host=172.25.94.49 auth_user=pgbouncer
172.25.94.49 is current postgresql leader, once the postgresql leader switches manually, it seems that all pgbouncer points to newly elected postgresql leader node automatically:
1、restart postgre leader(172.25.94.49)patroni service
gitlab-ctl stop patroni
gitlab-ctl start patroni
2、check current postgre cluster leader
the newly elected leader is 172.25.94.50 3、after new leader is elected, all pgbouncers point to new postgresql leader, below is its configure file /var/opt/gitlab/consul/databases.ini content:
The above behavior looks pretty good, but it also puzzled me at the same time: Since sidekiq and rails are configured to visit db through pgbouncer (actually through internal lb), but above test shows that all bgbouncer always points to current postgresql leader node, then it seems there is no read/write seperation! Even a simple read operation will go to leader postgresql.
I don't think pgbouncer be able to distinguish read/write operation and dispatch them to replica node and leader node.
I also got below video screenshot in youtube, which shows the Database Architecutre:
all read operations are directed to replicas nodes while write goes to leader node, this is what I want.
I think above w/r seperation design should based on dns domain name which is provided by consul, but it does not consistence with our official documentation.
And what's more, gitlab handbook also has similar database archeturture:
https://about.gitlab.com/handbook/engineering/infrastructure/production/architecture/#database-architecture
Does anybody be able to explain a little deeper about what's the read/write seperation machenism about gitlab.com and what is our suggested solution?
Thanks!