Shulou(Shulou.com)06/01 Report--

Postgresq9.6 master-slave deployment

Experimental environment

Name

IP

System

Master

172.17.10.190

Centos 6.5

Slave

172.17.10.189

Centos 6.5

1.yun installation

Rpm-ivh https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-centos96-9.6-3.noarch.rpmyum install postgresql96.x86_64 postgresql96-server.x86_64-y

two。 Master-slave configuration

2.1 Master database configuration

Start master

/ etc/init.d/postgresql-9.6 initdb/etc/init.d/postgresql-9.6 startsu-postgrespsql

Authorization

Create role repl login replication encrypted password '51idc.com'

Edit the hba file

/ var/lib/pgsql/9.6/data/pg_hba.conf

Add

Host replication repl 172.17.10.0/24 md5host all repl 172.17.10.0/24 trust

Edit configuration file

/ var/lib/pgsql/9.6/data/postgresql.conf

Listen_addresses = 172.17.10.190wal_level = hot_standby # hot backup mode max_wal_senders= 6 # can set up a maximum of several stream replication links, almost a few slaves, set as many as wal_keep_segments = 10240 # important configuration wal_send_timeout = 60s max_connections = 512 # the max_connections of the slave library is greater than the master library archive_mode = on # allow archiving archive_command ='cp% p / url/path%f' # set according to the actual situation

2.2 configure from the database

Su-postgres

If you start to start the database, you can ignore the next step.

Rm-rf / var/lib/pgsql/9.6/data/* # does not start the slave database at the beginning. This step can omit pg_basebackup-h 172.17.10.190-U repl-D / var/lib/pgsql/9.6/data-X stream-Pcp / usr/pgsql-9.6/share/recovery.conf.sample / var/lib/pgsql/9.6/data/recovery.conf

Modify the configuration file recovery.conf

Standby_mode = onprimary_conninfo = 'host=172.17.10.190 port=5432 user=repl password=51idc.com'trigger_file =' / var/lib/pgsql/9.6/data/trigger.kenyon' # trigger file recovery_target_timeline = 'latest' after master-slave switching

Configure the postgresql.conf file

Listen_addresses = 172.17.10.189wal_level = hot_standby max_connections = 1000 # generally, the maximum slave link is larger than the master link. Hot_standby = on # indicates that this machine is not only used for data archiving, but also for querying max_standby_streaming_delay = 30s wal_receiver_status_interval = 10s # how often the slave status is reported to the master. Hot_standby_feedback = on # if there is incorrect data replication, whether to make an example to the master

Detection

Select client_addr,sync_state from pg_stat_replication

View master-slave status

Select * from pg_stat_replication

Script monitoring master-slave

Github address

#! / bin/bash# mail xuel@51idc.comdata= `date +% Y-%M-%d ""% H:% m`netstat-lntup | grep 5432 & & ps-ef | grep postmasterif [$?-eq 0]; thenfor IP in 172.17.10.188 172.17.10.189do/usr/bin/psql-h 172.17.10.190-p 5432-U repl-d postgres-- command "select * from pg_stat_replication" | grep $IPif ["$?"! = "0"]; thenecho "postgresql master-slave status is error! Please login check! "| mail-r" xuel@51idc.com "- s" postgresql master-slave status is error "xuel@51idc.com\ & & echo" $data postgresql postgresql master-slave status is error! "> / var/log/postgresql-error.logfidoneelseecho" postgresql master-slave status is error! Please login check! "| mail-r" xuel@51idc.com "- s" postgresql master-slave status is error "xuel@51idc.com\ & & echo" $data postgresql postgresql master-slave status is error! "> / var/log/postgresql-error.logfi

2.3 Master-Slave switching

The main library viewing process is sender

Prepare the library

Stop the main library

View slave's log

Create trigger file and switch master

Touch trigger.kenyon

Check the log of slave. It has been switched to master before.

Use pg_controldata

Standby status: in archive recovery

The status of the main library is in production

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.