Managing High Availability in PostgreSQL – Part I

Managing high availability in your  PostgreSQL hosting
<https://scalegrid.io/postgresql.html>   is very important to ensuring your
clusters maintain exceptional uptime and strong operational performance so
your data is always available to your application. In an earlier blog post,
we introduced you to  configuring high availability for PostgreSQL using
streaming replication
<https://scalegrid.io/blog/getting-started-with-postgresql-streaming-replication/>
, and now we’re going to show you how to best manage PostgreSQL high
availability.

There are multiple tools available for managing the high availability of
your PostgreSQL clusters using streaming replication. These solutions offer
automatic failover capabilities, monitoring, replication, and other useful
administrative tasks. Some of the prominent open source solutions include:

1. PostgreSQL Automatic Failover by  ClusterLabs
<https://www.clusterlabs.org/>
2. Replication Manager for PostgreSQL Clusters by  repmgr
<https://repmgr.org/>   (2ndQuadrant)
3. Patroni by  Zalando <https://github.com/zalando/patroni>

Each of these tools provides their own way of managing the clusters. In our
three-part series of posts on high availability for PostgreSQL, we’ll share
an overview, the prerequisites, and the working and test results for each of
these three tools. Here in Part 1, we’ll deep dive into the PostgreSQL
Automatic Failover (PAF) solution by ClusterLabs.

PostgreSQL Automatic Failover

PostgreSQL Automatic Failover <https://clusterlabs.github.io/PAF/>   (PAF)
is a high availability management solution for PostgreSQL by ClusterLabs.
PAF makes use of the popular, industry-standard  Pacemaker
<https://clusterlabs.org/pacemaker/>   and  Corosync
<https://clusterlabs.org/corosync.html>   stack. With Pacemaker and Corosync
together, you’ll be able to detect failures in the system and act
accordingly.

Pacemaker is capable of managing many resources, and does so with the help
of their  resource agents <https://github.com/ClusterLabs/resource-agents/>
. Resource agents then have the responsibility of handling a specific
resource, how they should behave, and inform Pacemaker of their results.

Your resource agent implementation must comply to the  Open Cluster
Framework
<https://clusterlabs.org/pacemaker/doc/en-US/Pacemaker/1.1/html/Pacemaker_Explained/s-resource-supported.html>
(OCF) specification. This specification defines resource agents’ behavior
and implementation of methods like stop, start, promote, demote and
interaction with Pacemaker.

PAF is an OCF resource agent for PostgreSQL written in Perl. Once your
PostgreSQL cluster is built using internal streaming replication, PAF is
able to expose to Pacemaker the current status of the PostgreSQL instance on
each node: master, slave, stopped, catching up, etc.

How it Works

PAF communicates with Pacemaker regarding the cluster status and monitors
the PostgreSQL functioning. In the event of a failure, it informs Pacemaker,
and if there’s no chance of the current master being recovered, it will
trigger an election between the current standby servers. With the robust
Pacemaker in place, PAF will perform management actions like start, stop,
monitor, and failover on all the PostgreSQL nodes.

Are There Any Setup Requirements?

- PAF supports  PostgreSQL version 9.3
<https://www.postgresql.org/support/versioning/>   and higher.
- PAF is not responsible for PostgreSQL master/standby creation or its setup
- you must create and setup streaming replication before using PAF.
- PAF doesn’t edit any configuration of Postgres. However, it requires users
to follow a few prerequisites like:
- Slave must be configured as hot standby.
- A recovery template file (default:
<postgresql_data_location>/recovery.conf.pcmk) has to be provided with below
parameters:
- standby_mode = on
- recovery_target_timeline = ‘latest’
- primary_conninfo must have the application_name parameter defined and set
to local node name as in Pacemaker.
- PAF exposes multiple parameters related to the management of a PostgreSQL
resource. This can be configured to suit one’s requirement. Below are the
parameters:
- bindir: location of the PostgreSQL binaries (default: /usr/bin)
- pgdata: location of the PGDATA of your instance (default:
/var/lib/pgsql/data)
- datadir: path to the directory set in data_directory from your
postgresql.conf file
- pghost: the socket directory or IP address to use to connect to the local
instance (default: /tmp)
- pgport: the port to connect to the local instance (default: 5432)
recovery_template: the local template that will be copied as the
PGDATA/recovery.conf file. This template file must exists on all node
(default: $PGDATA/recovery.conf.pcmk)
- start_opts: Additional arguments given to the Postgres process on startup.
See “postgres –help” for available options. Useful when the postgresql.conf
file is not in the data directory (PGDATA), eg.: -c
config_file=/etc/postgresql/9.3/main/postgresql.conf
- system_user: the system owner of your instance’s process (default:
postgres)
- maxlag: maximum lag allowed on a standby before we set a negative master
score on it

PAF Pros

- PAF provides the user a free hands-on configuration and setup of
PostgreSQL.
- PAF can handle node failures and trigger elections when the master goes
down.
- Quorum behavior can be enforced in PAF.
- It will provide a complete high availability management solution for the
resource, including start, stop, and monitor, and handle network isolation
scenarios.
- It’s a distributed solution, which enables the management of any node from
another node.

PAF Cons

- PAF doesn’t detect if a standby is misconfigured with an unknown or
non-existent node in recovery configuration. Node will be shown as slave,
even if standby is running without connecting to the master/cascading
standby node.
- Requires an extra port (Default 5405) to be opened for the Pacemaker and
Corosync components’ communication using UDP.
- Does not support NAT-based configuration.
- No pg_rewind support.

High Availability Test Scenarios

We conducted a few tests to determine the capability of the PostgreSQL HA
management using PAF. All of these tests were run while the application was
running and inserting data to the PostgreSQL database. The application was
written using PostgreSQL Java  JDBC Driver <https://jdbc.postgresql.org/>
leveraging the connection failover capability.

Standby Server Tests


1) *Kill the PostgreSQL process:* Pacemaker brought the PostgreSQL process
back to running state. There was no disruption in writer application.
2) *Stop the PostgreSQL process:* Pacemaker brought the PostgreSQL process
back to running state. There was no disruption in writer application.
3) *Reboot the server:* Standby server was marked offline initially. Once
the server came up after reboot, PostgreSQL was started by Pacemaker and the
server was marked as online. If fencing was enabled, the node wouldn’t have
been added automatically to cluster. There was no disruption in writer
application.
4) *Stop the Pacemaker process:* It will stop the PostgreSQL process also,
and the server will be marked offline. There was no disruption in writer
application.

Master/Primary Server Tests


1) *Kill the PostgreSQL process:* Pacemaker brought the PostgreSQL process
back to running state. Primary was recovered within the threshold time and,
hence, election was not triggered. The writer application was down for about
26 seconds.
2) *Stop the PostgreSQL process:* Pacemaker brought the PostgreSQL process
back to running state. Primary was recovered within the threshold time and,
hence, election was not triggered. There was a downtime in writer
application for about 26 seconds.
3) *Reboot the server:* Election was triggered by Pacemaker after the
threshold time for which master was not available. The most eligible standby
server was promoted as the new master. Once the old master came up after
reboot, it was added back to the cluster as a standby. If fencing was
enabled, the node wouldn’t have been added automatically to cluster. The
writer application was down for about 26 seconds.
4) *Stop the Pacemaker process:* It will stop the PostgreSQL process also
and server will be marked offline. Election will be triggered and new master
will be elected. There was downtime in writer application.

Network Isolation Tests


1) *Network isolate the standby server from other servers:* Corosync traffic
was blocked on the standby server. The server was marked offline and
PostgreSQL service was turned off due to quorum policy. There was no
disruption in the writer application.
2) *Network isolate the master server from other servers (split-brain
scenario):* Corosync traffic was blocked on the master server. PostgreSQL
service was turned off and master server was marked offline due to quorum
policy. A new master was elected in the majority partition. There was a
downtime in the writer application.

Miscellaneous Tests


1) *Degrade the cluster by turning off all the standby servers:* When all
the standby servers went down, PostgreSQL service on master was stopped due
to quorum policy. After this test, when all the standby servers was turned
on, a new master was elected. There was a downtime in the writer
application.
2) *Randomly turn off all the servers one after the other, starting with the
master, and bring them all back at same time:* All the servers came up and
joined the cluster. New master was elected. There was a downtime in the
writer application.

Inference

PostgreSQL Automatic Failover provides several advantages in handling
PostgreSQL high availability. PAF uses IP address failover instead of
rebooting the standby to connect to the new master during a failover event.
This proves advantageous in scenarios where the user does not want to
restart the standby nodes. PAF also needs very little manual intervention
and manages the overall health of all the resources. The only case where
manual intervention is a requirement is in the event of a timeline
divergence where the user can elect to use  pg_rewind
<https://www.postgresql.org/docs/9.6/app-pgrewind.html>  .

In  Part 1
<https://scalegrid.io/blog/managing-high-availability-in-postgresql-part-1/>
, we’ve discussed the capabilities and workings of PostgreSQL Automatic
Failover (PAF) by ClusterLabs, and in Part 2, we’ll discuss the same high
availability aspects using the Replication Manager for PostgreSQL clusters
(repmgr) by 2ndQuadrant. Make sure to check back for Part 3, where we’ll
also cover Patroni by Zalando and compare all three open source solutions to
help you determine the best fit for your application.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html