PG HA failover automation

/cc @marin @jtevnan

@twk3 Marin mentioned you might be considering Etcd (or some sort of key value store) for something you are working on. If we can compare notes on this front, then it might make it easier to justify its presence in the omnibus package.

mentioned in issue #1807 (closed)

@ibaum it was considered, and then dropped for the licensing stuff because it wasn't worth adding something just for the licensing. (Then we dropped the licensing changes as well).

And it will probably come up again when looking at more HA config. But I haven't looked into anything at the moment.

Updated the wrong ticket yesterday. I'll copy the comments from #2570 (closed) to keep the discussion in the right place.

Updated my docker-compose.yml to work with repmgr. After bringing this online, there are still a couple of manual steps:

To get the cluster working run:

$ docker-compose exec db_one gitlab-ctl repmgr standby setup db_one -w
$ docker-compose exec db_two gitlab-ctl repmgr standby setup db_one -w

To get the application working run:

$ docker-compose exec db_one gitlab-psql -d gitlabhq_production -c 'create extension pg_trgm'
$ docker-compose exec app gitlab-rake db:migrate

To fix:

pg_trgm extension should be fixed by #2501
reconfigure tries to recreate pgbouncer nodes if postgresql['pgbouncer_user'] is set. This fails since its a standby node.
Manual migration shouldn't be necessary

Testing out https://github.com/2ndQuadrant/repmgr/blob/master/docs/repmgrd-node-fencing.md

Create ssh keys on the db nodes
Running in docker node, so needed to add gitlab-psql user to AllowedUsers in /assets/sshd_config
Create home directory for gitlab-psql on app node
Authorize db node ssh keys on app node
Modify pgbouncer.ini to include another ini file containing only the [databases] section.
Add promote.sh to the image
- TL/DR: The script promotes the new master, then notifies the pgbouncer nodes of the new master by generating a new database stanza for the pgbouncer nodes, then sends a reload command to the pgbouncer node.
Update promote_command in repmgr.conf to use the script
Need to debug the script a little better(mostly it's use of psql), but it is executed by the node that was chosen as the new master. Manually executing the failed steps, somewhat corrected, was successful.

Pros of this approach:

This is all pretty straightforward to automate
No new additions to the package

Cons of this approach:

Each db node needs to know about each pgbouncer node. So adding/removing pgbouncer nodes will need to propagate to the database nodes.
the new master database node needs to execute a command on each of the pgbouncer nodes when it's promoted. We'll need to account for the possibility that a pgbouncer node is unreachable during failover.

I'll start investigating using service discovery. Top three candidates in this category seem to be etcd, consul, and zookeeper. I'm going to rule out zookeeper since it's written in Java.

Consul and etcd are both written in Go, and have compatible licenses. I'll put together a list of pros and cons of both to see which one we should evaluate more in depth.

First pass:

Etcd

Pros

Simpler/boring solution
Better performance for key/value store.
- Not necessarily a concern for PG HA only. But if we utilize k/v in other areas, it could be.
Agentless clients

Cons

Key value store only. We would need to write some tooling around it to do what we want
- repmgr promote command should write new master to etcd
- an etcd watch command on pgbouncer nodes should update ini with new master and reload
Etcd docs recommend using other products for service discovery

Consul

Pros

Built in service discovery
DNS interface

Cons

Every node needs to run agent
More complicated product
Performance issues with large amounts of data
- Two caveats on this:
  - The results are from coreos, makers of etcd.
  - This dropoff in performance appears after 500,000 keys are in the db.

Both

Pros

Multi master clustering
Go binaries, should be simple to include in omnibus
http api
Support ACLs

References

I don't think there's a clear winner here yet. I'll try and put together a POC install for both today and see what looks better.

Etcd

This assumes etcdctl is installed on all nodes in the users PATH. I did this by downloading the binary from the etcd releases page, I'm using version 3.2.2

Etcd server

Spin up a node and run etcd. Currently one node only, with no security in place
Preseed etcd with current master: etcdctl set pg_master $CURRENT_MASTER

Pgbouncer server

Modify pgbouncer.ini
- Move the [databases] section to a separate file called pgbouncer-database.ini
- Add a line %include /var/opt/gitlab/pgbouncer/pgbouncer-database.ini to the end of the file
pkill -1 pgbouncer

Create a template pgbouncer-database.ini.erb

[databases]
gitlabhq_production = host=<%= host %> port=5432 dbname=gitlabhq_production auth_user=pgbouncer

Create a script setdb:

#!/bin/bash

directory=$(dirname ${0})
ini_file="${directory}/pgbouncer-database.ini"
erb "host=${ETCD_WATCH_VALUE}" ${ini_file}.erb > ${ini_file}
pkill -1 pgbouncer

Leave running in a window: etcdctl --endpoints http://etcd:2379 exec-watch pg_master -- /var/opt/gitlab/pgbouncer/setdb

Database nodes

Create a new promote script:

#!/bin/bash

gitlab-ctl repmgr standby promote

etcdctl --endpoints http://etcd:2379 set pg_master $(hostname)

Update promote_command in /var/opt/gitlab/postgresql/repmgr.conf with the location of the new script
gitlab-ctl restart repmgrd

To test, I shutdown postgresql on the master node and everything works as expected.

repmgrd on the standby nodes noticed the master was down, and waited for their timeout
After the timeoue, one of the nodes was selected as the new master and promoted.
The promotion updated the etcd key
The pgbouncer server caught the changed key through its exec-watch command, updated the pgbouncer-database.ini file and sent a HUP to the pgbouncer process
The pgbouncer process reread it's configuration, and sent database traffic to the new master server.

Consul

Same node layout as etcd.

1 consul server node
3 database nodes
1 application/pgbouncer node

Consul is installed on the consul server, and the database nodes.

Consul server

Run consul agent -server -bootstrap-expect=1 -data-dir=/tmp/consul -config-dir=/etc/consul.d -bind=172.21.0.6 -ui -client=172.21.0.6 -dns-port=53 -recursor=127.0.0.11

Note: Like the Etcd test, this completely ignores clustering, and security

Database nodes

Create a check script for postgresql. I was unable to find if consul had a concept for passive/active services, so the script will mark a service as failed on a node if it isn't the master, so consul wouldn't send traffic to it.
```
#!/bin/bash

set -e

cluster=$(gitlab-ctl repmgr cluster show || exit 2)
echo "${cluster}" | awk -v host=$(hostname) '
  $2 ~ /master/ {
  if($4 == host)
     exit 0;
  else
     exit 3;
}
'
```
Note: If the script exits 1, consul considers this a warning state and leaves it as available in the cluster.

Create a service definition for postgresql:

{
  "service": {
    "name": "postgresql",
    "tags": ["gitlab"],
    "port": 5432,
    "check": {
      "script": "/root/check_postgresql",
      "interval": "10s"
    }
  }
}

Start consul with consul agent -data-dir=/tmp/consul -bind=172.21.0.3 -config-dir=/etc/consul.d
Join the consul cluster consul join $SERVER_NODE
Ensure curl http://consul:8500/v1/health/service/postgresql has only one passing node for postgresql

Or go to http://CONSUL_SERVER:8500/ui/#/dc1/services/postgresql in a web browser

Pgbouncer node

Update /etc/resolv.conf to use the consul server as its primary nameserver
Update /var/opt/gitlab/pgbouncer/pgbouncer.ini to use postgresql.service.consul for the database host
- We may want to drop the dns_max_ttl in pgbouncer.ini for this, it's currently 15 seconds.
pkill -1 pgbouncer

So both tests were successful.

I really like consul's DNS interface. But, in order to take advantage of that GitLab will require a change to the system (resolv.conf) outside of Omnibus. So we should be really sure it's something we want to do before we offer that.

If we aren't going to do that, the approach would be similar to etcd. We'd run a consul agent on the pgbouncer node, and react to postgresql failure events from that.

So consul itself is a little more complicated to setup, but the workflow is a bit cleaner with the DNS interface. If that's something we would want to use, I would advocate for consul. Its other features may be handy for future use, but I'm not aware of anything in the pipeline that will immediately use it.

If we want to keep as much as possible within omnibus, etcd might be the way to go. It is a bit simpler than consul.

I'm definitely open to feedback, and a call is probably on order for Monday/Tuesday to discuss this.

/cc @marin @jtevnan

But, in order to take advantage of that GitLab will require a change to the system (resolv.conf) outside of Omnibus.

This is a bit of a deal breaker for me, I wouldn't want a package to go in and change my resolv.conf. Currently leaning more towards etcd but lets meet up and make a decision.

@ibaum we could use consul-template to update the /var/opt/gitlab/pgbouncer/pgbouncer.ini similar to etcdctl.

I did some testing of consul without using the DNS service discovery.

For the database nodes, the setup was exactly the same as my testing the other day

On the app node, I created a watch file /etc/consul.d/pgbouncer.json:

{
  "watches": [
  {
    "type": "service",
    "service": "postgresql",
    "handler": "/tmp/checkdb"
  }
  ]
}

/tmp/checkdb

#!/usr/bin/env ruby

require 'json'

data = $stdin.gets

struct = JSON.parse(data)

struct.each do |entry|
  entry['Checks'].each do |check|
    if check['CheckID'].eql?('service:postgresql') && check['Status'].eql?('passing')
      new_master = entry['Node']['Node']
    end
  end
end

ini_file="/var/opt/gitlab/pgboucner/pgbouncer-database.ini"
`erb host=#{new_master} #{ini_file}.erb > #{ini_file}`
`pkill -1 pgbouncer`

Note: There is a bug in my check script for the database cluster, if there is no master, then all database report as healthy.

Otherwise, testing was successful.

After more experience, an update to the pros and cons:

Etcd

Pros

Simpler/boring solution
Better performance for key/value store.
- Not necessarily a concern for PG HA only. But if we utilize k/v in other areas, it could be.
Agentless clients
- We could do this with Consul as well, but that isn't the way it is intended to be used
Mature software

Cons

Key value store only. We would need to write some tooling around it to do what we want
- repmgr promote command should write new master to etcd
- an etcd watch command on pgbouncer nodes should update ini with new master and reload
Etcd docs recommend using other products for service discovery

Consul

Pros

Built in service discovery
DNS/HTTP interface

Cons

Every node is expected to run an agent
More complicated product
Key value performance issues with large amounts of data
- caveats on this:
  - The results are from coreos, makers of etcd.
  - This dropoff in performance appears after 500,000 keys are in the db.
Has not hit v1.0 yet.
- Breaking API changes are possible
- Missing features (for example, querying for a list of watches on an agent)

Both

Pros

Multi master clustering
Go binaries, should be simple to include in omnibus
http api
Support ACLs

References

@jtevnan Provided a list of issues where consul is being considered internally to help:

Service discovery:

https://gitlab.com/gitlab-com/infrastructure/issues/1861 - Prometheus + service discovery for monitoring = node registers as a X server and prometheus builds configuration
- It seems this one is more infrastructure specific, and not geared towards services that are part of the Omnibus Package
https://gitlab.com/gitlab-org/gitlab-ee/issues/2042 - for Postgres (either via service discovery or DNS)
- Either Etcd/Consul is suitable for this.
https://gitlab.com/gitlab-com/infrastructure/issues/1863 - for haproxy configuration
- This is specific to our infrastructure, and not our package. Regardless, the point is to update a template and reload a service when a state changes. This is something that could be handled by either.
https://gitlab.com/gitlab-com/infrastructure/issues/1639 - CI (its a prerequisite for production readyness of CI - https://gitlab.com/gitlab-com/infrastructure/issues/1666)
- This appears to initially be infrastructure specific. It could be done with Etcd as well, but it consul is probably more appropriate.

DNS resolver:

https://gitlab.com/gitlab-com/infrastructure/issues/1723 - for gitlab.com as a whole
- This is infrastructure specific, it would not need to make its way to omnibus
https://gitlab.com/gitlab-org/gitaly/issues/74 - for gitaly
- Based on https://gitlab.com/gitlab-org/gitaly/issues/74#note_23763428 this should not be consul specific
https://gitlab.com/gitlab-org/gitlab-ee/issues/2042 - for Postgres (interestingly)
- See previous response

Secrets management:

https://gitlab.com/gitlab-com/infrastructure/issues/1865 - using vault and consul to role out secrets in real time
- Not relevant to omnibus

Our great scheme for a better gitlab.com - Meta issue with quite a few `fillers`

https://gitlab.com/gitlab-com/infrastructure/issues/1504

@ibaum Thank you very much for this in depth investigation!

From the comment in https://gitlab.com/gitlab-org/omnibus-gitlab/issues/2571#note_35319301 the thing that sticks out to me is that all the issues are very much infrastructure specific. I am not really sure how us shipping consul would make a difference for the Infra team. There is something to be said about using the same tool though, so that could be the difference.

@stanhu we need a tie breaker here, do you have any opinion about this?

mentioned in merge request !1769 (merged)

I see a number of really significant problems with Consul:

Requiring to tweak resolv.conf: this opens up all sorts of security, network connectivity, reliability issues for our customers
Number of open ports needed to be opened for firewalls: https://www.consul.io/docs/agent/options.html#ports
Custom agent on each machine
Immaturity of the solution

Did you discuss the DNS SRV approach?

Requiring to tweak resolv.conf: this opens up all sorts of security, network connectivity, reliability issues for our customers

I tested a different approach yesterday that didn't require a change to the nodes DNS resolver. It worked similar to the Etcd approach, where the pgbouncer node watches for a change in the consul service state for postgresql, and executes a command when it does. This happens using the consul HTTP API

Number of open ports needed to be opened for firewalls: https://www.consul.io/docs/agent/options.html#ports

We can remove two of those ports from the requirements:

8400 -- We will be using a version > 0.8 where the cli communicates over the HTTP API port
8600 -- We will not be using the DNS interface by default

That does still leave four ports that need to be open.

For etcd we would need 2 new ports

Custom agent on each machine

Immaturity of the solution

I share these concerns

Did you discuss the DNS SRV approach?

I've been trying to stick to solutions which rely on components which we can keep internal to the omnibus package. So tying into DNS is problematic.

@stanhu can you explain Immaturity of the solution --> do you mean consul as an immature piece of software?

@jtevnan Yes. Issues such as this concerns me about running an agent on every node that we deploy: https://github.com/hashicorp/consul/issues/1718

I believe etcd is being used internally by Kubernetes and CoreOS, which seems to suggest to me that it's a bit more mature.

I know this isn't going to change decisions already made but I just want to add my 2 cents.

We've been running consul-agent for a year at Gitter and we never had any issues with it. It helps us running a Nomad cluster and create and configure a canary environment with a single git push.

I wouldn't think that a Hashicorp product is immature simply by looking at the version < 1.0. They set an extremely high bar to call a product stable and to date there is only one that met the requirements (see "A HashiCorp 1.0" in https://www.hashicorp.com/blog/packer-1-0/).

Incidentally, we're also relying on Terraform 0.9 for our infrastructure at GitLab.com and it helped us go from 2 days to provision a single server to a maximum of 30 minutes.

Anyone can release a 1.x version: it's just a number after all. It's how a product reaches the milestone that makes the difference.

Per a conversation with @marin and @joshlambert, I did some more digging around Etcd and Consul so we could have some more data points. We wanted to compare the upgrade process, the backup process, as well as any resource usage.

Upgrade

Etcd
Consul

Both support rolling/zero downtime upgrades. The process is the same on both:

Stop the process
Replace the binary
Start the process

This is done one node at a time.

While neither yet has introduced a breaking change that required a different process, Consul has documented the process they expect that would entail if necessary.

Backup

Etcd
Consul

Both have a snapshot process that needs to be run on one node. The resulting snapshot can be used as a backup and restored later.

Resource usage

Documented requirements

Etcd
Consul

Consul has a lower per node requirement than Etcd, but does require more nodes. Still, per the consul docs, for a reliable "but relatively slow" consul cluster, they recommend the equivalent of an AWS t2.micro instance, or 1 cpu and 1 G of ram per node.

For a small cluster, etcd recommends 2 cpus and 8 G of ram per node.

For both products, memory usage is based on the working set of data. That should be small for what we need to do. If users want to use the bundled software for other uses, it would be up to them to ensure the node is properly sized.

Hello! Do you know Postgres Automatic Failover? It is based on Pacemaker and Corosync.

It is specialized for High Availbility thanks to Pacemaker. You can also manage pgbouncer thanks to Systemd ressource agent : http://clusterlabs.org/doc/en-US/Pacemaker/1.1/html/Pacemaker_Explained/_systemd.html

Checkout this presentation (english) : http://www.dalibo.org/paf_another_brick_in_the_ha_wall

Regards,

Hi @anayrat,

yes we took a look at that as well, however pacemaker/corosync are very (very very) hard to package, making them a problematic solution for gitlab (the package).

Also, while i love corosync, it relies heavily on network stability, so it doesnt play well with our cloud based infrastructure.

Check out https://gitlab.com/gitlab-org/omnibus-gitlab/issues/1807 for further information on the history of how we got here. and thank you for your input!

I understand, pacemaker can keep you against split brain.

PG HA key/value store

Use case

On failover, Repmgrd would update the required key in a key/value store. This change would be picked up by Pgbouncer which would then propagate the change to the rest of Pgbouncer processes. That would then allow the traffic to be sent to the new primary.

Comparison

	Complexity	Performance	Configuration	Features	Resources	Size	License
Etcd	Only for key-value store	Highly performant as key/value store	Requires 2 ports to be opened (Client requests and peer comms)	Only used as key/value store	For our use case, memory req. should be minimal	9ish MB	Apache 2.0
Consul	Key/value store is one of the features. Product with more features	Key/value store wise, less performant	Requires 4 ports (API, client, peer internal, peer external comms). For additional features, more ports	Health checks, Service discovery	For our use case, memory req. should be minimal	9ish MB	MPL 2.0

Pros vs Cons

For our current use case, PG HA failover key/value store: I would choose etcd. The fact that it requires 2 ports and that it is simpler to configure are the important factors. High performance does not matter for our use case, we don't expect a lot of traffic.

Future proofing: I would choose Consul. It has way more interesting features that could ultimately simplify required configuration. For example, it could unblock https://gitlab.com/gitlab-org/gitlab-ee/issues/2042 and allow us to build an EE feature which would allow the application to also read from read-only secondaries. We could also think about how to expend it further to use for secrets management between nodes.

Maintainability: I would choose Consul *. Production team already has this in the infrastructure and there is or will be knowledge in the company whose expertise we could re-use. * Here comes a but: The fact that production team is using Consul in the infra. does not bring value to the PG HA solution because production is using this independently and will use this independently from the package. However, there is something to be said about in house expertise.

Maturity: I would choose Etcd. Not that I think Consul is not stable piece of software, but for what it does Etcd has more kilometers behind it.

From all the data at our disposal at this point of discussion, I think we should go with Consul. The maintainability and additional options it opens for future gives more value and for our use case, it will do the same etcd can do at the moment.

@stanhu @ibaum Let me know what you think about this.

@marin Thanks for this, I think this sums up the situation perfectly, and I do agree with your conclusion.

Thanks, everyone, for your input. Please go ahead with Consul.

mentioned in issue gitlab-com/infrastructure#2173 (closed)

mentioned in merge request !1800 (merged)

In order to get consul up and running I need to start by automating the following

Deploying consul cluster
- I'll start by working similar to repmgrd. After install, a user would need to run a join command to get the nodes to cluster together. They just need to know the name of one node in the cluster. I think I prefer this to embedding node names in gitlab.rb. It should be safe to assume nodes will be added and removed over time. So forcing users to maintain the attribute list seems the wrong way.
Deploying consul agent
- Initially it needs to deploy to PostgreSQL and Pgbouncer nodes
- Add postgresql service, with status check to PostgreSQL nodes
- Add service to watch and react to postgresql service updates on pgbouncer nodes

Current known unknowns:

I don't think we need a gitlab-ctl consul subcommand. The command line application is smart enough to talk to the local agent, and get its configuration from there. So we don't need to have any GitLab specific command line options. For workflow consistency, we might consider adding the subcommand anyway, and just blindly passing the arguments on to the consul command.

Conversation with @northrup this morning about their Consul rollout.

They used the following for the rollout

I'll see what I can't leverage from those in order to save some time.

changed milestone to %9.5

added Scheduled label

mentioned in issue gitlab-com/infrastructure#1485 (closed)

mentioned in issue #2694

mentioned in issue #2695 (closed)

mentioned in issue #2696 (closed)

mentioned in issue #2697

marked this issue as related to #2714 (closed)

marked this issue as related to #2713 (closed)

marked this issue as related to #2712 (closed)

marked this issue as related to #2711 (closed)

marked this issue as related to #2705

marked this issue as related to #2703 (closed)

marked this issue as related to #2697

marked this issue as related to #2696 (closed)

marked this issue as related to #2695 (closed)

marked this issue as related to #2694

marked this issue as related to #2692

marked this issue as related to #2690

marked this issue as related to #2651

marked this issue as related to #2649

marked this issue as related to #2572

marked this issue as related to #2522

marked this issue as related to #2628 (closed)

marked this issue as related to #2721 (closed)

Initial release is out, and should be considered beta. I've added several issues as related issues to this ticket. These are all bug fixes or enhancements.

marked this issue as related to #2751 (closed)

mentioned in issue gitlab-com/infrastructure#2762 (closed)

mentioned in issue gitlab-com/infrastructure#2765

mentioned in issue gitlab-com/infrastructure#2547

mentioned in issue #2831

Admin message

Admin message

PG HA failover automation

Relates to

Activity

Etcd

Pros

Cons

Consul

Pros

Cons

Both

Pros

References

Etcd

Etcd server

Pgbouncer server

Database nodes

Consul

Consul server

Database nodes

Pgbouncer node

Etcd

Pros

Cons

Consul

Pros

Cons

Both

Pros

References

Service discovery:

DNS resolver:

Secrets management:

Our great scheme for a better gitlab.com - Meta issue with quite a few fillers

Upgrade

Backup

Resource usage

Documented requirements

PG HA key/value store

Use case

Comparison

Pros vs Cons

Our great scheme for a better gitlab.com - Meta issue with quite a few `fillers`