The Fifth Elephant 2015

We built a postgres cluster using repmgr to serve 2k requests per second, and store 5G of data per day. You’ll learn about postgres’ WAL replication and archival, how repmgr works, how we leveraged it for our needs, hooked it up to our application, and built multiple lines of defence in case something bad happens. And oh, we’ll also compare it with RDS for good measure.

Outline

Because of the nature of our application (experimentation), it was crucial for us to protect the integrity and prevent against loss of data. Hence, we needed a quick failover mechanism in place.

There isn’t any out of the box postgres cluster management solution that fit our need. RDS at that time didn’t have read replica support for postgres. We built this mechanism ourselves using repmgr.

The talk would cover details on multiple axes, but gere are a few things we did around this as a gist:

• Evaluated postgres latencies on the same box and different box.
• Set up a postgres cluster using repmgr on vagrant.
• Multiple lines of defence in case of a failover:
  • Repmgr runs a script to notify applications (all running instances) about failover.
  • Application polls to check if it can write to the master DB. And attemps to find the master from the cluster if not.
• We load tested this, and were able to achieve a maximum downtime of 5s.

Note: This will be a talk by two people: Srihari and Nivedita. That’s okay right? :)

-- Update
Here’s a gist version of the below for more on the talk: https://gist.github.com/ssrihari/6443dbc0d4da4a91ba48

Why

• Do you even need a cluster of postgreses? Why did we choose to create a cluster ourselves?
• Why didn’t RDS work for us? Why it might not work for you.
• What are other RDS like services out there?

Postgres side of things:

• What you need to know within postgres so your data can be streamed
  down to multiple nodes quick, and efficiently.

Hardware

• Can you do without an SSD? Are writing fast enough? How to find out?
• Will performance degrade when you’re writing WALs?
• How fast do you need to ship WALs to another machine?

WAL (Write Ahead Logging)

• What is it? How does it work?
• How to configure it for a cluster - hot_standby, wal_keep_segments, etc.

WAL Archives

• Why do you need it when you have streaming replication?
• How do you set the archive and streaming delays?

How does this impact normal workflows?

• Large deletes or updates can blot WALs
• Read-only standbys, large queries and timeouts

Repmgr side of things:

• What does this magic replication manager do? What I learnt by
  digging into it’s C code.

What does a cluster of postgres machines look like?

• What are ‘master’, ‘standbys’, ‘witnesses’, and ‘failed’ nodes
• Cascading, and Synchronous replication
• priorities, configurations

Into the guts of ‘how’ repmgr works

• The consensus algorithm it uses
• repl_nodes - a table of all the machines in the cluster, their configs, statuses
• repl_status - how far is each machine behind the master?
• repl_events - what’s happening in the cluster along a timeline

The things repmgr does for your cluster

• well, it exists. not a single open tool (save pgpool) out there that’s as good.
• help setup, configure the relationships between postgres boxes
• ‘Clone’, ‘Register’, ‘Follow’, ‘Promote’ commands
• monitor for failure, elect a master, automate failover
• How to tap into the promote and failover scenarios?

The things repmgr doesn’t

• It doesn’t talk to your application. it doesn’t act as a load
  balancer. or a single entry point to your cluster.
• Tell you when a failover happens. You need to rig that up yourself.
• Give you extensive support or documentation. But, you have the source.

Failovers

How things can go wrong

• Multi-master / split brain / STONITH
• No master
• Network disconnect, Disk full

What happens when a failover happens?

• How long does it take? How do you know which is the new master?
• How do we recover a failed DB? How long does it take?
• How do we add a new machine to the cluster?
• How do we remove stale node entries?

When things have gone wrong

• What measures to take to ensure you’re equipped for bad scenarios.
• How do you save data when you know you’re going to lose it?
• Deletes/Truncates cascade. How do you free disk space?
• When is your free disk space critically low?
• How to recover from WAL archives? Logical backups? Filesystem backups?
• Which backup/restore mechanism will work best for you?

Wrapping up the big picture

• What does my application do when there’s a failover?
• How do the db-connections move to the new master?
• How can the application, and repmgr work together for maximum robustness?
• Alerting, Monitoring, Maintenance

Speaker bio

Nid and Srihari are developers at Nilenso, a software cooperative in Bangalore.

Slides

https://gist.github.com/ssrihari/6443dbc0d4da4a91ba48#file-postgres-clusters-nuances-png