Scaling PostgreSQL with repmgr

Talk Type: 
45 Minute Talk
Technical Level: 
Creative Commons - No Commercial or Modification

The new features in PostgreSQL 9.0 allows replication to systems whose only communication with the master are files sent to it, and you can then run queries against that copy. repmgr is free software to make building read scalable clusters of PostgreSQL nodes using this feature simpler to get started with, and then manage in production.

Streaming Replication and Hot Standby are features in PostgreSQL 9.0 that introduce built-in replication to the database. An entire copy of the database cluster is sent to some number of standby nodes, each of which is capable of running read-only queries. If utilized properly, this scaling approach is applicable to many situations where it's acceptable for some clients to read data that might lag real-time by a small amount.

Once you have multiple nodes in a cluster like there, there are several common concerns that everyone encounters. How do you monitor lag between the servers? (Hint: the server will tell you, but the unit it uses are internal transactions identification numbers!) How do you gracefully switchover to a new master? And when your master fails, how do you promote one of the standby systems to that role, and then get all of the other standby systems to start talking to it?

The repmgr software is a PostgreSQL add-on that lives outside the database to make these chores easier to do. It automatically computes and reports on lag in human-readable units. It includes a monitoring daemon that tracks node state across the whole cluster. Using a simple command line interface, you can execute complicated operations that touch every node in the cluster all from one place.

This talk will introduce PostgreSQL replication, how to setup a cluster with repmgr, and what sort of tasks you can expect it to help automate. If you're concerned you'll find yourself in a difficult situation where you need to make decisions like "which node has the most recent copy of my data now that the master is failed?" and then shuffle around the configuration of every remaining node you have, you'll have just the right tool needed for that type of problem.

repmgrIntro.pdf407.56 KB