Implement PostgreSQL failover in CentOS 6.5

Implement PostgreSQL failover in CentOS 6.5

Pgpool-II is an intermediate component that works between the PostgreSQL multi-server and the PostgreSQL Data Warehouse client.

It provides the following functions:

Connection Pool: pgpool-II is used to save and connect to the PostgreSQL server and reuse new connections with the same attributes (that is, the user name, data library, and Protocol version ), reduces the sales of connections and increases the overall throughput of the system. Replication: pgpool-II can manage multiple PostgreSQL servers. With the copy function, you can back up two or more physical magnetic disks in real time, so you do not need to stop the service even if the hard disk fails.

Load Balancing: if the database is replicated, the same result will be returned if a SELECT query is executed on any server. Pgpool-II adopts a replication function. The advantage is that it reduces the load of SELECT queries allocated between multiple server servers for each PostgreSQL server, improve the overall throughput of the system. The number of PostgreSQL server servers with the best performance improvement ratio. When there are a large number of user queries at the same time, load balancing has the best execution.

Connection exceeded limit: there is a limit on the maximum number of concurrent connections with PostgreSQL. connections that exceed the maximum number of connections are rejected. Sets the maximum number of connections, but the increased resource consumption and impact on system performance. Pgpool-II also has limits on the maximum number of connections, but connections outside the quota will be queued, rather than returning an error immediately.

Parallel query: with the parallel query function, data can be distributed across multiple server servers, so that the query can reduce the total execution time on all server servers. When querying data in parallel, find the best big gauge data.

Before building a pgpool, you must configure stream replication for postgresql. For operation steps, see

I. Installation

Wget http://www.pgpool.net/download.php? F=pgpool-II-3.4.0.tar.gz
Tar-zxvf pgpool-3.4.0.tar.gz
Cd pgpool-II-3.4.0/
./Configure -- prefix =/usr/local/pgpool -- with-pgsql = path -- with-pgsql =/usr/local/pgsql
Make
Make install
Chown postgres. postgres/usr/local/pgpool/-R
Chown postgres. postgres/usr/src/pgpool-II-3-R
Mkdir/var/run/pgpool
Chown postgres. postgres/var/run/pgpool/
# Switch to S to install some functions
Su-postgres

C/usr/src/pgpool-II-3.4.0/src/SQL/
Make
Make install
Cd pgpool-recovery/
Make install
Cd ../pgpool-regclass/
Make install

Ii. Configuration

Cd/usr/local/pgpool/etc
Cp pcp. conf. sample pcp. conf
Pg_md5 S
E8a48653851e28c69d0504178fb27fc5
Echo "ipvs: e8a48653851e28c69d0501168fb27fc5"> pcp. conf
Echo "s: e8a48653851e28c69d050da-8fb27fc5"> pool_passwd
Cp pool_0000.conf.sample pool_0000.conf
Vim pool_cmd.conf
Host all ipvs db2 md5


Listen_addresses = '*' # Allow all hosts to listen
Port = 9999 # Access port
Backend_hostname0 = 'db1' # DBmaster ip
Backend_port0 = 5432 # DBmaster postgresql Port
Backend_weight0 = 1 # weight
Backend_data_directory0 = '/opt/data' # DBmaster database directory
Backend_flag0 = 'Allow _ TO_FAILOVER '# Switch allowed

Backend_hostname0 = 'db2'
Backend_port0 = 5432
Backend_weight0 = 1
Backend_data_directory0 = '/opt/data'
Backend_flag0 = 'Allow _ TO_FAILOVER'

Enable_pool_hba = on # customizable, use pool_cmd.conf to verify the client
Pool_passwd = 'pool _ passwd' # md5 verification file
Sr_check_user = 'ipvs' # user used for failover

Failover_command = '/usr/local/pgsql/bin/failover_command.sh % d % H/tmp/trigger_file'

Failover script
 
Vim/usr/local/pgsql/bin/failover_command.sh

#! /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
# Trigger file so that standby takes over primary node.
#
# Arguments: $1: failed node id. $2: new master hostname. $3: path
# 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;
Chmod + x/usr/local/pgsql/bin/failover_command.sh

Iii. debugging

Start command with log output
 
[S @ db1 etc] $ pgpool-nd>/tmp/pgpool. log 2> & 1 &
[S @ db1 etc] $ netstat-ntlp
(Not all processes cocould be identified, non-owned process info
Will not be shown, you wowould have to be root to see it all .)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
Tcp 0 0 0.0.0.0: 22 0.0.0.0: * LISTEN-
Tcp 0 0 127.0.0.1: 25 0.0.0.0: * LISTEN-
Tcp 0 0 0.0.0.0: 9898 0.0.0.0: * LISTEN 16664/pgpool
Tcp 0 0 0.0.0.0: 9999 0.0.0.0: * LISTEN 16664/pgpool
Tcp 0 0: 22: * LISTEN-
Tcp 0 0: 1: 25: * LISTEN-
Tcp 0 0: 9999: * LISTEN 16664/pgpool

 
Login

[S @ db1 etc] $ psql-U postgres-h db1-p 9999
Psql (9.2.1)
Type "help" for help.
Postgres = # show pool_nodes;
Node_id | hostname | port | status | lb_weight | role
--------- + ---------- + ------ + -------- + ----------- + ---------
0 | db1 | 5432 | 2 | 0.500000 | primary
1 | db2 | 5432 | 2 | 0.500000 | standby
(2 rows)
S = # create database db0;
CREATE DATABASE

2: Start
3: Dead

You can log on to the test and read and write data.

Iv. Failover

First stop DBmaster
[S @ db1 etc] $ pg_ctl-m fast stop

Log on to view

[S @ db1 etc] $ psql-U postgres-h db1-p 9999
Postgres = # show pool_nodes;
Node_id | hostname | port | status | lb_weight | role
--------- + ---------- + ------ + -------- + ----------- + ---------
0 | db1 | 5432 | 3 | 0.500000 | standby
1 | db2 | 5432 | 2 | 0.500000 | primary
(2 rows)

The log information displayed by DBslave

[S @ db2 data] $ FATAL: replication terminated by primary server
LOG: record with zero length at 0/10000FE0
LOG: trigger file found:/tmp/trigger_file
LOG: redo done at 0/Hangzhou f80
LOG: last completed transaction was at log time 11:05:44. 379009 + 08
LOG: selected new timeline ID: 2
LOG: archive recovery complete
LOG: database system is ready to accept connections
LOG: autovacuum launcher started

DBmaster is dead, status switches to standby; DBslave switches to primary; test read/write
Log prompt, trigger_file file found, switch
Switch DBslave to master server
Recover. conf is automatically changed to recover. done.

[S @ db2 data] $ ll/opt/data/recovery. done
Recovery. done

Failover successful

5. Restore DBmaster

1. Synchronize DBmaster to DBslave

Pg_basebackup-D $ PGDATA-Fp-Xs-v-h db1-p 5432-U postgres

2. modify the configuration file

Listen_addresses = '*'
Port = 5432
Hot_standby = on

3. Modify the recover File

Mv recover. done recover. conf
Vim recover. conf
Primary_conninfo = 'host = 172.16.0.20.port = 5432 user = s s'

4. Start DBslave
5. Add a node

Pcp_attach_node-d 5 db1 9898 postgres 0
Pcp_attach_node-d 5 db1 9898 postgres 1

Log on to view

Postgres = # show pool_nodes;
Node_id | hostname | port | status | lb_weight | role
--------- + ---------- + ------ + -------- + ----------- + ---------
0 | db1 | 5432 | 2 | 0.500000 | primary
1 | db2 | 5432 | 2 | 0.500000 | standby
(2 rows)

Restore to normal
Game Over!

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

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: