Understanding and implementing PostgreSQL High Availability with Patroni
Alexey started with PostgreSQL at 2003 as a C programmer. He learned how to debug the backend code before learning SQL, but caught up on the latter pretty quickly. He is now using both of those skills and years of experience as a database consultant to help his employer manage a growing number of PostgreSQL database clusters and deal with terabytes of analytical data. Oleksii is based in Berlin and works as a PostgreSQL Engineer at Adjust GbmH. Over the years, he contributed code and documentation to the PostgreSQL project, as well as promoted Postgres by starting PostgreSQL User Group Berlin in 2015.
During his professional career, Alexander touched PostgreSQL from all possible sides: as Web Developer, as System Administrator, and as a Database Engineer now. About four years ago he started working on Patroni project and achieved quite a big success with it. Periodically he reports bugs and contributes patches to PostgreSQL and some other open source projects (usually postgres related). He is a regular speaker at different postgres events.
No video of the event yet, sorry!
You can get slides at https://github.com/patroni-training/2019/raw/master/slides.pdf
Postgres has a rock-solid single master physical replication, but lacks built-in failover. A number of open-source and proprietary tools aim to fill this niche. Patroni stands apart because it doesn’t try to solve all hard problems by itself. Being a successor of Compose Governor, it relies on an external consistency layer supported by battle-proven solutions like Etcd, Consul, Zookeeper or the Kubernetes API to avoid split-brains and unnecessary failovers.
Patroni is customizable and extensible, recognizing the multitude of tools, such as those for creating new replicas, connection poolers, load balancers, monitoring solutions or watchdogs that it needs to interact with during or after the failover and therefore it can be adapted to a wide variety of database environments, from bare-metal data centers to cloud based computing units and docker-based environments like Kubernetes, without changing the core failover logic. It provides a tunable balance between availability and consistency, allowing the operator to choose how long to wait before recognizing a suddenly unresponsive primary as dead, or use synchronous replication to exclude the possibility of data loss altogether. Patroni also offers customizable retries to deal with network issues. It makes most of PostgreSQL physical replication by offering support for replication slots, pg_rewind, cascading and synchronous replication.
Patroni is trusted by various small and large companies, among them are Zalando, IBM Compose, TimescaleDB and, recently, Gitlab. It is open-source and developed in the open by a group of contributors from various companies and locations around the world.
Based on our experience developing and running Patroni at Zalando, as well as providing support to the global community over GitHub issues and other channels, we will teach you:
- Common pitfalls of PostgreSQL automatic failover solutions and how Patroni avoids them.
- Patroni architecture and various options to tune it to your environment.
- How to manage PostgreSQL configuration with Patroni and keep your replicas configuration in-sync with the primary.
- Various REST API endpoints to help monitor and manage Patroni.
- How to use patronictl tool as a more human-friendly alternative to the REST API.
- How to make clients connect to the primary (or standby) PostgreSQL.
- Features, allowing to extend Patroni, supporting your favorite tool to create new replicas or even clone the whole cluster.
- Many useful features, such as scheduled switchovers and restarts, maintenance mode, standby clusters etc.
- How to read Patroni logs and troubleshoot common issues.
For hands-on session you will need a laptop (preferably Linux or Mac, although Windows should work as well) with docker and docker-compose installed (using your distro package manager, in which case you typically need to install separate packages for both docker and docker-compose, or the installers from https://www.docker.com/products/docker-desktop that have both docker and docker-compose in one package).
Once docker and docker-compose are installed, download https://raw.githubusercontent.com/patroni-training/2019/master/docker-compose.yml and run
docker-compose up -d in the directory where you've downloaded it.
For further information, please refer to https://github.com/patroni-training/2019
- 2019 March 19 13:00
- 3 h
- Sugar Hill
- Postgres Conference
- Ops and Administration