Understanding and implementing PostgreSQL High Availability with Patroni

Presented by:

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!

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.

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).

Why Guidelines

PostgresConf US 2019 is a non-profit, community driven conference delivering the largest education and advocacy platform for Postgres. In an effort to create a productive and profitable environment for our community we must set a bar of expectation for content and we set the bar high.

The community is comprised of users, developers, core-contributors, sponsors, advocates, and external communities. We work hard to create a professional, valuable, and highly educational environment that produces a net result of, “Wow, that conference was very well done. It had great content with knowledgeable professionals, educators and advocates!”

Types of Presentations We Accept

Any presentation that has a tie-in to Postgres will be considered for acceptance into one of our event programs. This includes, but is not limited to, presentations on Open Source Projects, Postgres forks, extensions, new APIs, and languages. As a conference that makes it a goal to incorporate the entire community we also consider business cases, product talks, and service presentations. Presentations that are not related to the success of Postgres will not be considered.

Laptop connections supported

PostgresConf will provide a HDMI connection. If you do not have a HDMI port, it is the speaker’s responsibility to provide an adapter.

What about X technology?

When considering whether to submit a topic or not, ask yourself, “Is this presentation in some way related to the success of Postgres?” If it is, then it will be considered. This includes limitations of our great database. It also includes forks of Postgres both closed and Open Source. These types projects and products ultimately contribute to the success of Postgres.

How do presentations on forks or closed source versions help the success of Postgres?

Postgres is BSD style licensed. The BSD license is a true freedom license. The only license that maintains a higher level of freedom for Open Source development is the anti-license: Public Domain. That means the community embraces all forms of use for Postgres as long as it abides by the license.

Shouldn’t the community be promoting Open Source solutions over proprietary closed source solutions?

Yes, the end goal is that the deployed Postgres is the Open Source Postgres and preference will always be given to Open Source Postgres presentations. However, we offer presentation opportunities to our sponsors that may or may not advocate Open Source Postgres. Sponsors are welcome to present on any topic as long as it follows the guideline of: Does it promote the success of Postgres?

Types of Presentations

PostgresConf offers several presentation opportunities:

Presentation: 20 minutes

Breakout Sessions: 50 minutes

Keynotes: 10 and 20 minutes

Half and Full Day trainings (NOTE: Paid registrants receive priority seating, and trainings are restricted according to badge level)

If you must cancel for any reason you must notify us in writing by March 4, 2019 to receive a refund minus the $25 cancellation fee. Please contact us via email at organizers@postgresconf.org.

Cancellations within 14 days of the event are non-refundable. You may transfer your registration to another person through the conference website. Confirmed and paid attendees who do not attend or who cancel after the deadline are liable for the entire fee. In the unlikely event of cancellation of the conference, the liability of PostgresConf is limited to the return of paid registration fees.

Introduction

Postgres Conference (PostgresConf) prides itself on the quality of our community and our work, and the technical and professional accomplishments of our community. We expect everyone who participates to conduct themselves in a professional manner, acting with common courtesy and in the common interest, with respect for all of our community.

It is the expectation that PostgresConf community members will adhere to the PostgresConf Code of Conduct (CoC).

Inclusivity and Appropriate Conduct

PostgresConf is open to participation by anyone with an interest in working with Postgres and related technologies, regardless of their level of experience with the software, or with technology in general. We encourage contributions from all individuals, whatever their background may be.

Personal attacks and negative comments on personal characteristics are unacceptable, and will not be permitted. Examples of personal characteristics include, but are not limited to age, race, national origin or ancestry, religion, political affiliation, gender, or sexual orientation.

Additional behaviors which are also violations of this CoC include, but are not limited to, threats of violence against an individual or group, threats of professional, community, and/or project sabotage, unwelcome sexual attention in any form, engaging in behavior that may bring PostgresConf into disrepute, and refusing to cease inappropriate conduct when requested to do so.

Retaliation

It is also expressly forbidden for anyone to retaliate against a person who brings a complaint under this CoC, or who assists in investigating such a complaint. Retaliation may take the form of, among other actions:
further personal attacks (public or private); actions which undermine an individual's professional status and/or status with their employer, coworkers, clients, or community; actions which threaten the individual's privacy, physical person, well-being, home, and/or family. Acts of retaliation will be treated in the same manner as any other violation of this CoC.

Reporting

PostgresConf believes that only objective parties can properly handle Code of Conduct issues. Therefore we contract with Lighthouse an independent and objective party to handle all reports.

Acting in Good Faith

Any allegations that prove not to be substantiated, and which prove to have been made maliciously or knowingly to be false, will be viewed as a serious community offense and a violation of this CoC.

Conclusion

We encourage appropriate and collegial relationships among community members; however, members must be sensitive to conduct that may be considered offensive by fellow members and must refrain from engaging in such conduct.

In all interactions with the community, use your professional judgment, and keep the discussion focused on moving our project and our community forward in a positive direction for all.

This tool is
free software,
released under the
MIT license.
You can run, copy, distribute, study, change and improve it.
The source code and the developers are on
github.