PostgreSQL stream replication +pgpool for high availability

Pgpool Overview

The Pgpool-ii is a middleware located between the PostgreSQL server and the PostgreSQL database client, which provides the following features: Connection pooling, replication, load balancing, limiting over-limit connections, and parallel queries. Document here.

Four types of modes

O means "available" and X means "not available
(1) Parallel query mode requires both replication and load balancing to be turned on, but replication and load balancing cannot be used for distributed tables in parallel query patterns.
(2) online recovery can be used in conjunction with streaming replication.
The client simply connects to the PostgreSQL server via Pgpool-ii. This mode is used only to limit the number of connections to the server, or to enable failback on multiple machines.

Pgpool in different modes, provide different functions, this article will choose in the main standby mode, the implementation of fault recovery, that is, automatic failover function.

System structure

Configuration file Introduction

The Pgpool has four main configuration files, namely

• Pcp.conf is used to manage and view node information, such as adding new nodes. This file is mainly stored in the form of user name and MD5 password.
• Pgpool.conf is used to set Pgpool mode, information about primary and secondary databases, and so on.
• Pool_hba.conf is used to authenticate user login methods, such as client IP restrictions, etc., similar to the pg_hba.conf file for PostgreSQL.
• POOL_PASSWD is used to save the corresponding client login account name and MD5 password.

Topological structure

Install PostgreSQL streaming replication settings

Reference

Pgpool Installation Setup Setup

sudo apt-get insatll pgpool2

Set pcp.conf

/USR/SBIN/PG_MD5 Password

will get MD5 plus user name in the following way to write to the file/etc/pgpool2/pcp.conf,
Pgpool:5f4dcc3b5aa765d61d8327deb882cf99

Set pgpool.conf

Make sure that the relevant configuration items are set up as follows:

Listen_addresses = ‘*‘
Backend_hostname0 = '10.10.10.104' #主机ip
Backend_port0 = 5432
Backend_weight0 = 1 #loadbalance is not enabled, invalid
Backend_data_directory0 = ‘/var/lib/postgresql/9.5/main‘
Backend_flag0 = ‘ALLOW_TO_FAILOVER’

Backend_hostname1 = ‘10.10.10.102‘ #备机ip
Backend_port1 = 5432
Backend_weight1 = 1
Backend_data_directory1 = ‘/var/lib/postgresql/9.5/main‘
Backend_flag1 = ‘ALLOW_TO_FAILOVER’

Enable_pool_hba = on
Pool_passwd = ‘pool_passwd‘

Master_slave_mode = on
Master_slave_sub_mode = ‘stream’
Sr_check_user = ‘replication’ #Stream copy account
Sr_check_password = ‘password‘

Failover_command = ‘/var/lib/postgresql/failover.sh %h %H /var/lib/postgresql/state/recovery_trigger‘ After the host fails, the touch file changes the standby from read-only to read-write.

Set pool_hba.conf

Settings can refer to the HBA settings for PostgreSQL, such as

# IPv4 local connections:
host    all               all           127.0.0.1/32    md5
host    all               all           0.0.0.0/0       md5
host    replication     replication     0.0.0.0/0       md5

Set POOL_PASSWD

Premise: The corresponding instance of PostgreSQL has already established the corresponding account. The purpose of setting up the POOL_PASSWD is to allow the database account to log in to the database via Pgpool.
Method One: Set up a system account with the same name as the database account, then call PG_MD5 directly, the command will automatically generate a good pool_passwd file. For example I have SYSTEM account cloud, password for cloud

/USR/SBIN/PG_MD5-M-U Cloud Cloud

Method Two: Direct access to the database, the results in the following way into the file/etc/pgpool2/pool_passwd

Cloud:md5313e20fe4ca8bf6751ffd3c5b963a9ad

Querying the database:

Select usename,passwd from Pg_shadow;

failover.sh

When the host is down, Pgpool will move the link to standby, so the standby must be modified from read-only to read-write, either to create a trigger file or to invoke the promote command.

#! /bin/sh
# Failover command for streaming replication.
# This script assumes that DB node 0 is primary, and 1 is standby.
# 
# If standby goes down, do nothing. If primary goes down, create a
# trigger file so that standby takes over primary node.
#
# Arguments: $1: failed node id. $2: new master hostname. $3: path to
# trigger file.

failed_node=$1
new_master=$2
trigger_file=$3

# Do nothing if standby goes down.
if [ $failed_node = 1 ]; then
	exit 0;
fi

# Create the trigger file.
/usr/bin/ssh -T $new_master /bin/touch $trigger_file

exit 0;

Configure trust between hosts

The main purpose is to make Pgpool host can login PostgreSQL host, create trigger file.
Reference command:

Ssh-keygenssh-copy-id [email protected]

Test connection

psql -h10.10.10.105 -p9999 -Ucloud

cloud=> show pool_nodes;
 node_id |   hostname   | port | status | lb_weight |  role   
---------+--------------+------+--------+-----------+---------
 0       | 10.10.10.102 | 5432 | 2      | 0.500000  | primary
 1       | 10.10.10.104 | 5432 | 2      | 0.500000  | standby
(2 rows)

Analog host Downtime

Shut down the host's service. Connect again to view.

server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
cloud=> show pool_nodes;
 node_id |   hostname   | port | status | lb_weight |  role   
---------+--------------+------+--------+-----------+---------
 0       | 10.10.10.102 | 5432 | 3      | 0.500000  | standby
 1       | 10.10.10.104 | 5432 | 2      | 0.500000  | primary
(2 rows)

Can find standby has been upgraded to the main, the original host status became 3. The meaning of the status corresponds to:

Status is represented by the number [0 - 3].
0 - This state is only used for initialization, and PCP never displays it.
1 - The node has been started and there is no connection yet.
2 - The node has been started and the connection is buffered.
3 - The node is down. 

Repair and re-join

The original host after repair, re-provide services, can be re-standby as a new host to join Pgpool. The steps are as follows:

• Update ppgpool.conf, HOST0 changed to new host, HOST1 changed to original standby. Pgpool requires a reboot to take effect on the modification of this option, so the modification here only prevents Pgpool from restarting and leads to the wrong host, and the actual repair does not require a restart of the Pgpool.
• Re-establish standby, which is stream replication.
• Use the PCP command to add the repaired standby to Pgpool.

/usr/sbin/pcp_attach_node 10 localhost 9898 pgpool cloud 0

psql -h10.10.10.105 -p9999 -Ucloud

cloud=> show pool_nodes;
 node_id |   hostname   | port | status | lb_weight |  role   
---------+--------------+------+--------+-----------+---------
 0       | 10.10.10.102 | 5432 | 2      | 0.500000  | standby
 1       | 10.10.10.104 | 5432 | 2      | 0.500000  | primary
(2 rows)

This completes the repair work in the case of non-stop machine.

PostgreSQL stream replication +pgpool for high availability