建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案
PostgreSQL9.6主從部署詳解
實(shí)驗(yàn)環(huán)境
成都創(chuàng)新互聯(lián)主營(yíng)上思網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,成都app開(kāi)發(fā),上思h5成都微信小程序搭建,上思網(wǎng)站營(yíng)銷推廣歡迎上思等地區(qū)企業(yè)咨詢
|
名稱 |
IP |
系統(tǒng) |
|
Master |
172.17.10.190 |
centos 6.5 |
|
Slave |
172.17.10.189 |
CentOS 6.5 |
1.yun安裝
| 1 2 | rpm -ivh https: //download .postgresql.org /pub/repos/yum/9 .6 /RedHat/rhel-6-x86_64/pgdg-centos96-9 .6-3.noarch.rpm yum install postgresql96.x86_64 postgresql96-server.x86_64 -y |
2.主從配置
2.1 主數(shù)據(jù)庫(kù)配置
啟動(dòng)master
| 1 2 3 4 | /etc/init .d /postgresql-9 .6 initdb /etc/init .d /postgresql-9 .6 start su - postgres psql |
授權(quán)
| 1 | create role repl login replication encrypted password '51idc.com' ; |
編輯hba文件
/var/lib/pgsql/9.6/data/pg_hba.conf
新增
| 1 2 | host replication repl 172.17.10.0 /24 md5 host all repl 172.17.10.0 /24 trust |
編輯配置文件
/var/lib/pgsql/9.6/data/postgresql.conf
| 1 2 3 4 5 6 7 8 | listen_addresses = 172.17.10.190 wal_level = hot_standby #熱備模式 max_wal_senders= 6 #可以設(shè)置最多幾個(gè)流復(fù)制鏈接，差不多有幾個(gè)從，就設(shè)置多少 wal_keep_segments = 10240 #重要配置 wal_send_timeout = 60s max_connections = 512 #從庫(kù)的 max_connections要大于主庫(kù) archive_mode = on #允許歸檔 archive_command = 'cp %p /url/path%f' #根據(jù)實(shí)際情況設(shè)置 |
2.2 從數(shù)據(jù)庫(kù)配置
| 1 | su - postgres |
如果開(kāi)始為啟動(dòng)數(shù)據(jù)庫(kù)可忽略下一步
| 1 2 3 | rm -rf /var/lib/pgsql/9 .6 /data/ * #開(kāi)始沒(méi)有啟動(dòng)從數(shù)據(jù)庫(kù)，這一步可以省略 pg_basebackup -h 172.17.10.190 -U repl -D /var/lib/pgsql/9 .6 /data -X stream -P cp /usr/pgsql-9 .6 /share/recovery .conf.sample /var/lib/pgsql/9 .6 /data/recovery .conf |
修改配置文件recovery.conf
| 1 2 3 4 | standby_mode = on primary_conninfo = 'host=172.17.10.190 port=5432 user=repl password=51idc.com' trigger_file = '/var/lib/pgsql/9.6/data/trigger.kenyon' #主從切換時(shí)后的觸發(fā)文件 recovery_target_timeline = 'latest' |
配置postgresql.conf文件
| 1 2 3 4 5 6 7 | listen_addresses = 172.17.10.189 wal_level = hot_standby max_connections = 1000 #一般從的最大鏈接要大于主的。 hot_standby = on #說(shuō)明這臺(tái)機(jī)器不僅僅用于數(shù)據(jù)歸檔，也用于查詢 max_standby_streaming_delay = 30s wal_receiver_status_interval = 10s #多久向主報(bào)告一次從的狀態(tài)。 hot_standby_feedback = on #如果有錯(cuò)誤的數(shù)據(jù)復(fù)制，是否向主進(jìn)行范例 |
檢測(cè)
| 1 | select client_addr,sync_state from pg_stat_replication; |
查看主從狀態(tài)
| 1 | select * from pg_stat_replication; |
腳本監(jiān)控主從
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | #!/bin/bash # mail xuel@51idc.com data=` date +%Y-%M-%d " " %H:%m` netstat -lntup| grep 5432 && ps -ef| grep postmaster if [ $? - eq 0 ]; then for IP in 172.17.10.188 172.17.10.189 do /usr/bin/psql -h 172.17.10.190 -p 5432 -U repl -d postgres -- command "select * from pg_stat_replication" | grep $IP if [ "$?" != "0" ]; then echo "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 .log fi done else echo "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 .log fi |
2.3主從切換
主庫(kù)查看進(jìn)程為sender
備庫(kù)
停止主庫(kù)
查看slave的日志
創(chuàng)建觸發(fā)文件，切換主
| 1 | touch trigger.kenyon |
查看slave的日志，面前已經(jīng)切換為主
使用pg_controldata
備機(jī)狀態(tài)為： in archive recovery
主庫(kù)狀態(tài)為：in production
分享名稱：PostgreSQL9.6主從部署詳解
網(wǎng)站路徑：http://m.5511xx.com/article/djpdhih.html
