Use pgpool to achieve high availability of PostgreSQL

Use pgpool to achieve high availability of PostgreSQL

Pgpool-ii is used to achieve high availability of PG. Based on the stream replication method, two nodes are automatically switched:

1. Single pgpool

A. Environment:

Pgpool: 192.168.238.129
Data1: 192.168.238.130
Data2: 192.168.238.131

B. Legend

C. Configure mutual trust

Ssh-copy-id ha @ node1
Ssh-copy-id ha @ node2
 
D. For database node configuration, see use pg_basebackup to build a PostgreSQL stream replication environment.

E. pgpool Configuration:

Listen_addresses = '*'
Backend_hostname0 = 'node1'
Backend_port0 = 5432
Backend_weight0 = 1
Backend_data_directory0 = '/home/ha/pgdb/data'
Backend_flag0 = 'Allow _ TO_FAILOVER'
 
Backend_hostname1 = 'node2'
Backend_port1= 5432
Backend_weight1 = 1
Backend_data_directory1 = '/home/ha/pgdb/data'
Backend_flag1 = 'Allow _ TO_FAILOVER'
 
Enable_pool_hba = on
Pool_passwd = 'pool _ passwd'
 
Pid_file_name = '/home/ha/pgpool. Pi'
Logdir = '/home/ha/pgpool/Log'
 
Health_check_period = 1
Health_check_user = 'ha'
Health_check_password = 'ha'
 
Failover_command = '/home/ha/pgdb/fail. sh % H'
 
Recovery_user = 'ha'
Recovery_password = 'ha'
 
F. fail. 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
# Trigger file so that standby takes over primary node.
#
# Arguments: $1: failed node id. $2: new master hostname. $3: path
# Trigger file.
 
New_master = $1
Trigger_command = "/home/ha/pgdb/bin/pg_ctl-D/home/ha/pgdb/data promote-m fast"
 
# Do nothing if standby goes down.
If [$ failed_node = 1]; then
Exit 0;
Fi
 
# Create the trigger file.
/Usr/bin/ssh-T $ new_master $ trigger_command
 
Exit 0;
 

G. Create pool_passwd

Pg_md5-m-p-u postgres pool_passwd
 
PS: trigger_file has been used before 9.1. We recommend that you use promote-m fast because
"Pg_ctl promote-m fast will skip the checkpoint at end of recovery so that we can achieve very fast failover when the apply delay is low. write new WAL record XLOG_END_OF_RECOVERY to allow us to switch timeline correctly for downstream log readers. if we skip synchronous end of recovery checkpoint we request a normal spread checkpoint so that the window of re-recovery is low. simon Riggs and Kyotaro Horiguchi, with input from Fujitsu Masao. review by Heikki Linnakangas"

H. Test
Pgpool Node

[Ha @ node0 pgdb] $ pgpool-n-d>/tmp/pgpool. log 2> & 1 &
[1] 22928
[Ha @ node0 pgdb] $ psql-h 192.168.238.129-p 9999-d postgres-U ha
Password for user ha:
Psql (9.4.5)
Type "help" for help.
 
S = # insert into test values (8 );
INSERT 0 1
S = # select * from test;
Id
----
1
2
3
4
6
8
(6 rows)
 
Node1 node:

[Ha @ localhost pgdb] $ ps-ef | grep post
Root 2124 1 0 Dec26? 00:00:00/usr/libexec/postfix/master
Postfix 2147 2124 0 Dec26? 00:00:00 qmgr-l-t fifo-u
Postfix 13295 2124 0? 00:00:00 pickup-l-t fifo-u
Ha 13395 1 0 06:06 pts/3 00:00:00/home/ha/pgdb/bin/postgres
Ha 13397 13395 0? 00:00:00 ipvs: checkpointer process
Ha 13398 13395 0? 00:00:00 ipvs: writer process
Ha 13399 13395 0? 00:00:00 ipvs: wal writer process
Ha 13400 13395 0? 00:00:00 postgres: autovacuum launcher process
Ha 13401 13395 0? 00:00:00 postgres: stats collector process
Ha 13404 13395 0? 00:00:00 ipvs: wal sender process rep 192.168.238.131 (59415) streaming 0/21000060
Ha 13418 4087 0 00:00:00 pts/3 grep post
[Ha @ localhost pgdb] $ kill-9 13395
 
Pgpool node:

S = # insert into test values (8 );
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.
S = # insert into test values (8 );
INSERT 0 1
S = # insert into test values (8 );
INSERT 0 1
S = # select * from test;
Id
----
1
2
3
4
6
8
8
8
(8 rows)

2. Two pgpool nodes

A. Environment

Pgpool: 192.168.238.129 <span> </span> pgpool: 192.168.238.131
Node1: 192.168.238.130
Node2: 192.168.238.131

B. Legend

C. Configure mutual trust. Same as above.
D. Database node configuration, same as above.
E. pgpool Configuration
Node1

F. Configure pgpool (master)

Listen_addresses = '*'
Backend_hostname0 = 'node1'
Backend_port0 = 5432
Backend_weight0 = 1
Backend_data_directory0 = '/home/ha/pgdb/data /'
Backend_flag0 = 'Allow _ TO_FAILOVER'
Backend_hostname1 = 'node2'
Backend_port1= 5432
Backend_weight1 = 1
Backend_data_directory1 = '/home/ha/pgdb/data /'
Backend_flag1 = 'Allow _ TO_FAILOVER'
Enable_pool_hba = on
Authentication
Pool_passwd = 'pool _ passwd'
Pid_file_name = '/home/ha/pgpool. Pi'
Logdir = '/tmp/Log'
Master_slave_mode = on
Master_slave_sub_mode = 'stream'
Sr_check_period = 2
Sr_check_user = 'Ha'
Sr_check_password = 'Ha'
Health_check_period = 1
Health_check_timeout = 20
Health_check_user = 'ha'
Health_check_password = 'ha'
Failover_command = '/home/ha/pgpool/fail. sh % H'
Recovery_user = 'ha'
Recovery_password = 'ha'
Use_watchdog = on
Wd_hostname = 'node1' # local end
Delegate_IP = '192. 168.238.151'
# Use ifconfig to view the NIC
If_up_cmd = 'ifconfig eth1: 0 inet $ _ IP _ $ netmask 255.255.255.0'
If_down_cmd = 'ifconfig eth1: 0 low'
Heartbeat_destination0 = 'node2' # peer
Heartbeat_device0 = 'eth0'
Other_pgpool_hostname0 = 'node2' # peer
Oss_pgpool_port0 = 9999
Other_wid_port0 = 9000

G. Configure pgpool (slave)
 
Listen_addresses = '*'
Backend_hostname0 = 'node1'
Backend_port0 = 5432
Backend_weight0 = 1
Backend_data_directory0 = '/home/ha/pgdb/data /'
Backend_flag0 = 'Allow _ TO_FAILOVER'
Backend_hostname1 = 'node2'
Backend_port1= 5432
Backend_weight1 = 1
Backend_data_directory1 = '/home/ha/pgdb/data /'
Backend_flag1 = 'Allow _ TO_FAILOVER'
Enable_pool_hba = on
Authentication
Pool_passwd = 'pool _ passwd'
Pid_file_name = '/home/ha/pgpool. Pi'
Logdir = '/tmp/Log'
Master_slave_mode = on
Master_slave_sub_mode = 'stream'
Sr_check_period = 2
Sr_check_user = 'Ha'
Sr_check_password = 'Ha'
Health_check_period = 1
Health_check_timeout = 20
Health_check_user = 'ha'
Health_check_password = 'ha'
Failover_command = '/home/ha/pgpool/fail. sh % H'
Recovery_user = 'ha'
Recovery_password = 'ha'
Use_watchdog = on
Wd_hostname = 'node2' # local end
Delegate_IP = '192. 168.238.151'
# Use ifconfig to view the NIC
If_up_cmd = 'ifconfig eth1: 0 inet $ _ IP _ $ netmask 255.255.255.0'
If_down_cmd = 'ifconfig eth1: 0 low'
Heartbeat_destination0 = 'node1' # peer
Heartbeat_device0 = 'eth1'
Other_pgpool_hostname0 = 'node1' # peer
Oss_pgpool_port0 = 9999
Other_wid_port0 = 9000

H. fail. 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
# Trigger file so that standby takes over primary node.
#
# Arguments: $1: failed node id. $2: new master hostname. $3: path
# Trigger file.
 
New_master = $1
Trigger_command = "/home/ha/pgdb/bin/pg_ctl-D/home/ha/data start"
 
# Do nothing if standby goes down.
If [$ failed_node = 1]; then
Exit 0;
Fi
 
# Create the trigger file.
/Usr/bin/ssh-T $ new_master $ trigger_command
 
Exit 0;
 
I. Create pool_passwd

Pg_md5-m-p-u postgres pool_passwd

J. Test
 
# Database and pgpool startup
[Ha @ node0 pgdb] $ psql-h 192.168.238.151-p 9999-d postgres-U ha
Password for user ha:
Psql (9.4.5)
Type "help" for help.
 
S = # insert into test values (9 );
INSERT 0 1
S = # insert into test values (9 );
INSERT 0 1
Postgres = #
-- Kill the database process of node1
S = # insert into test values (9 );
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.
S = # insert into test values (9 );
INSERT 0 1
S = # insert into test values (9 );
INSERT 0 1
S = # insert into test values (9 );
INSERT 0 1
S = # insert into test values (9 );
INSERT 0 1
S = # insert into test values (9 );
INSERT 0 1
S = # insert into test values (9 );
INSERT 0 1
S = # insert into test values (9 );
INSERT 0 1
S = # insert into test values (9 );
INSERT 0 1
-- Kill the pgpool process of node1
S = # insert into test values (9 );
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.
S = # insert into test values (9 );
INSERT 0 1
S = # insert into test values (9 );
INSERT 0 1
Postgres = #

------------------------------------ Lili split line ------------------------------------

Install the PostgreSQL 9.3.5 database in Ubuntu Server 14.04

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: