建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
PostgreSQL數(shù)據(jù)庫單機(jī)擴(kuò)展為流復(fù)制
1. 在standby服務(wù)器安裝postgres數(shù)據(jù)庫,不需要初始化.
創(chuàng)新互聯(lián)建站自2013年創(chuàng)立以來，先為光明等服務(wù)建站，光明等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為光明企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問題。
安裝過程詳見:http://www.cnblogs.com/ilifeilong/p/6979288.html
2. 在primary服務(wù)器創(chuàng)建具有REPLICATION權(quán)限的復(fù)制用戶
- postgres=# CREATE ROLE repl WITH REPLICATION PASSWORD ‘repl‘ LOGIN;
3. 允許復(fù)制用戶遠(yuǎn)程連接到primary服務(wù)器
- $ grep "^host" pg_hba.conf
- host all all 127.0.0.1/32 trust
- host replication repl 0.0.0.0/0 md5
- host all all ::1/128 trust
4. 在primary服務(wù)器設(shè)置流復(fù)制相關(guān)的參數(shù)
- $ mkdir /usr/local/pgsql/arch
- $ egrep "archive_mode|max_wal_senders|wal_keep_segments|archive_command|wal_level|hot_standby" postgresql.conf
- al_level = hot_standby # minimal, archive, hot_standby, or logical
- archive_mode = on # enables archiving; off, on, or always
- archive_command = ‘test ! -f /usr/local/pgsql/arch/%f && cp %p /usr/local/pgsql/arch/%f‘
- max_wal_senders = 5 # max number of walsender processes
- wal_keep_segments = 30 # in logfile segments, 16MB each; 0 disables
- hot_standby = on # "on" allows queries during recovery
- #hot_standby_feedback = off # send info from standby to prevent
5. 重新啟動primary服務(wù)器進(jìn)程
- $ pg_ctl stop -m fast
- $ pg_ctl start
6. 對primary服務(wù)器做一個全備并傳輸?shù)絪tandby服務(wù)器
- 在primary服務(wù)器通過pg_(start|stop)_backup函數(shù)進(jìn)行備份
- postgres=# SELECT pg_start_backup(‘label‘, true);
- pg_start_backup
- -----------------
- 7/E6000060
- (1 row)
- $ rsync -az --progress ${PGDATA} postgres@10.189.100.195:/usr/local/pgsql/ --exclude postmaster.pid
- postgres=# SELECT pg_stop_backup();
- NOTICE: pg_stop_backup complete, all required WAL segments have been archived
- pg_stop_backup
- ----------------
- 7/E60005C8
- (1 row)
在standby服務(wù)器通過pg_basebackup命令進(jìn)行備份,要求standby的PGDATA目錄為空
- $ pg_basebackup --host=10.189.102.118 --username=repl --port=5432 --label=backup --verbose --progress --pgdata=/usr/local/pgsql/data --checkpoint=fast --format=p --xlog-method=stream
- Password:
- transaction log start point: 7/EA000028 on timeline 1
- pg_basebackup: starting background WAL receiver
- 65933562/65933562 kB (100%), 1/1 tablespace
- transaction log end point: 7/EA000830
- pg_basebackup: waiting for background process to finish streaming ...
- pg_basebackup: base backup completed
7. 設(shè)置standby數(shù)據(jù)庫復(fù)制相關(guān)參數(shù),使得standby失效轉(zhuǎn)移后可以作為主庫工作
- $ mkdir /usr/local/pgsql/arch
- $ egrep "archive_mode|max_wal_senders|wal_keep_segments|archive_command|wal_level|hot_standby" postgresql.conf
- wal_level = hot_standby # minimal, archive, hot_standby, or logical
- archive_mode = on # enables archiving; off, on, or always
- archive_command = ‘test ! -f /usr/local/pgsql/arch/%f && cp %p /usr/local/pgsql/arch/%f‘
- max_wal_senders = 5 # max number of walsender processes
- wal_keep_segments = 30 # in logfile segments, 16MB each; 0 disables
- hot_standby = on # "on" allows queries during recovery
- #hot_standby_feedback = off # send info from standby to prevent
8. 在standby文件創(chuàng)建恢復(fù)文件
- $ cat recovery.conf
- restore_command = ‘cp /usr/local/pgsql/arch/%f "%p"‘
- standby_mode = ‘on‘
- primary_conninfo = ‘user=repl password=repl host=10.189.102.118 port=5432 sslmode=disable sslcompression=1‘
- archive_cleanup_command = ‘pg_archivecleanup -d /usr/local/pgsql/arch %r >> /usr/local/pgsql/arch/archive_cleanup.log‘
- trigger_file = ‘/usr/local/pgsql/data/trigger_active_standby‘
9. 啟動standby數(shù)據(jù)庫進(jìn)程,自動啟動流復(fù)制
- $ pg_ctl start -w
- waiting for server to start....LOG: could not create IPv6 socket: Address family not supported by protocol
- LOG: redirecting log output to logging collector process
- HINT: Future log output will appear in directory "pg_log".
- done
- server started
10. 檢查primary和standby數(shù)據(jù)庫的延遲
- 通過函數(shù)和系統(tǒng)表查看
- edbstore=# select * from pg_stat_replication; #在primary主庫查看
- -[ RECORD 1 ]----+------------------------------
- pid | 15013
- usesysid | 19206
- usename | repl
- application_name | walreceiver
- client_addr | 10.189.100.195
- client_hostname |
- client_port | 56072
- backend_start | 2017-06-13 08:10:35.400508-07
- backend_xmin |
- state | streaming
- sent_location | 7/EC01A588
- write_location | 7/EC01A588
- flush_location | 7/EC01A588
- replay_location | 7/EC01A588
- sync_priority | 0
- sync_state | async
- edbstore=# SELECT pg_current_xlog_location(); #在primary主庫查看
- pg_current_xlog_location
- --------------------------
- 7/EC01A588
- (1 row)
- postgres=# select pg_last_xlog_receive_location(),pg_last_xlog_replay_location(),pg_last_xact_replay_timestamp(); #在standby備庫查看
- pg_last_xlog_receive_location | pg_last_xlog_replay_location | pg_last_xact_replay_timestamp
- -------------------------------+------------------------------+-------------------------------
- 7/EC01A588 | 7/EC01A588 | 2017-06-13 08:25:20.281568-07
- (1 row)
- 通過進(jìn)程查看
- $ ps -ef | grep sender | grep -v grep #在primary庫查看
- postgres 15013 24883 0 08:10 ? 00:00:00 postgres: wal sender process repl 10.189.100.195(56072) streaming 7/EC01A668
- $ ps -ef | grep receiver | grep -v grep #在standby庫查看
- postgres 12857 12843 0 08:10 ? 00:00:00 postgres: wal receiver process streaming 7/EC01A668
網(wǎng)站欄目：PostgreSQL數(shù)據(jù)庫單機(jī)擴(kuò)展為流復(fù)制
鏈接分享：http://m.5511xx.com/article/coeddse.html
