pgexperts / patroni

Runners to orchestrate a high-availability PostgreSQL
MIT License
3 stars 0 forks source link

|Build Status| |Coverage Status|

Patroni: A Template for PostgreSQL HA with ZooKeeper or etcd

Patroni was previously known as Governor.

There are many ways to run high availability with PostgreSQL. Here, we present a template for you to create your own customized, high-availability solution using Python and — for maximum accessibility — a distributed configuration store like ZooKeeper or etcd.

Getting Started

To get started, do the following from different terminals:

::

> etcd --data-dir=data/etcd
> ./patroni.py postgres0.yml
> ./patroni.py postgres1.yml

From there, you will see a high-availability cluster start up. Test different settings in the YAML files to see how its behavior changes. Kill some of the components to see how the system behaves.

Add more postgres*.yml files to create an even larger cluster.

We provide a haproxy configuration, which will give your application a single endpoint for connecting to the cluster's leader. To configure, run:

::

> haproxy -f haproxy.cfg

::

> psql --host 127.0.0.1 --port 5000 postgres

How Patroni Works

For a diagram of the high availability decision loop, review this PDF: postgres-ha.pdf <https://github.com/zalando/patroni/blob/master/postgres-ha.pdf>__

YAML Configuration

For an example file, see postgres0.yml. Regarding settings:

Replication Choices

Patroni uses Postgres' streaming replication. By default, this replication is asynchronous. For more information, see the Postgres documentation on streaming replication <http://www.postgresql.org/docs/current/static/warm-standby.html#STREAMING-REPLICATION>__.

Patroni's asynchronous replication configuration allows for maximum_lag_on_failover settings. This setting ensures failover will not occur if a follower is more than a certain number of bytes behind the follower. This setting should be increased or decreased based on business requirements.

When asynchronous replication is not optimal for your use case, investigate how Postgres's synchronous replication <http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION>__ works. Synchronous replication ensures consistency across a cluster by confirming that writes are written to a secondary before returning to the connecting client with a success. The cost of synchronous replication: reduced throughput on writes. This throughput will be entirely based on network performance. In hosted datacenter environments (like AWS, Rackspace, or any network you do not control), synchrous replication significantly increases the variability of write performance. If followers become inaccessible from the leader, the leader effectively becomes readonly.

To enable a simple synchronous replication test, add the follow lines to the parameters section of your YAML configuration files:

.. code:: YAML

    synchronous_commit: "on"
    synchronous_standby_names: "*"

When using synchronous replication, use at least three Postgres data nodes to ensure write availability if one host fails.

Choosing your replication schema is dependent on your business considerations. Investigate both async and sync replication, as well as other HA solutions, to determine which solution is best for you.

Applications Should Not Use Superusers

When connecting from an application, always use a non-superuser. Patroni requires access to the database to function properly. By using a superuser from an application, you can potentially use the entire connection pool, including the connections reserved for superusers with the superuser_reserved_connections setting. If Patroni cannot access the Primary because the connection pool is full, behavior will be undesireable.

Requirements on a Mac

Run the following on a Mac to install requirements:

::

brew install postgresql etcd haproxy libyaml python
pip install psycopg2 pyyaml

Notice

There are many different ways to do HA with PostgreSQL: See the PostgreSQL documentation <https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling>__ for a complete list.

We call Patroni a "template" because it is far from being a one-size-fits-all or plug-and-play replication system. It will have its own caveats. Use wisely.

.. |Build Status| image:: https://travis-ci.org/zalando/patroni.svg?branch=master :target: https://travis-ci.org/zalando/patroni .. |Coverage Status| image:: https://coveralls.io/repos/zalando/patroni/badge.svg?branch=master :target: https://coveralls.io/r/zalando/patroni?branch=master