PostgreSQL資料庫單機擴充為流複製

標籤：socket   waiting   nts   ring   action   info   man   .com   proc   

1. 在standby伺服器安裝postgres資料庫,不需要初始化.

安裝過程詳見:http://www.cnblogs.com/ilifeilong/p/6979288.html

2. 在primary伺服器建立具有REPLICATION許可權的複製使用者

postgres=# CREATE ROLE repl WITH REPLICATION PASSWORD ‘repl‘ LOGIN;

3. 允許複製使用者遠端連線到primary伺服器

$ grep "^host" pg_hba.confhost    all             all             127.0.0.1/32            trusthost    replication             repl             0.0.0.0/0               md5 host    all             all             ::1/128                 trust

4. 在primary伺服器設定流複製相關的參數

$ mkdir /usr/local/pgsql/arch $ egrep "archive_mode|max_wal_senders|wal_keep_segments|archive_command|wal_level|hot_standby" postgresql.confal_level = hot_standby            # minimal, archive, hot_standby, or logicalarchive_mode = on        # enables archiving; off, on, or alwaysarchive_command = ‘test ! -f /usr/local/pgsql/arch/%f && cp %p /usr/local/pgsql/arch/%f‘        max_wal_senders = 5        # max number of walsender processeswal_keep_segments = 30        # in logfile segments, 16MB each; 0 disableshot_standby = on            # "on" allows queries during recovery#hot_standby_feedback = off        # send info from standby to prevent

5. 重新啟動primary伺服器處理序

$ pg_ctl stop -m fast$ pg_ctl start

6. 對primary伺服器做一個全備並傳輸到standby伺服器

•    在primary伺服器通過pg_(start|stop)_backup函數進行備份

postgres=# SELECT pg_start_backup(‘label‘, true); pg_start_backup ----------------- 7/E6000060(1 row)$ rsync -az --progress ${PGDATA} [email protected]10.189.100.195:/usr/local/pgsql/ --exclude postmaster.pidpostgres=# SELECT pg_stop_backup();NOTICE:  pg_stop_backup complete, all required WAL segments have been archived pg_stop_backup ---------------- 7/E60005C8(1 row)

•  在standby伺服器通過pg_basebackup命令進行備份,要求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=streamPassword: transaction log start point: 7/EA000028 on timeline 1pg_basebackup: starting background WAL receiver65933562/65933562 kB (100%), 1/1 tablespace                                         transaction log end point: 7/EA000830pg_basebackup: waiting for background process to finish streaming ...pg_basebackup: base backup completed

7. 設定standby資料庫複寫相關參數,使得standby失效轉移後可以作為主庫工作

$ mkdir /usr/local/pgsql/arch$ egrep "archive_mode|max_wal_senders|wal_keep_segments|archive_command|wal_level|hot_standby" postgresql.confwal_level = hot_standby                 # minimal, archive, hot_standby, or logicalarchive_mode = on               # enables archiving; off, on, or alwaysarchive_command = ‘test ! -f /usr/local/pgsql/arch/%f && cp %p /usr/local/pgsql/arch/%f‘max_wal_senders = 5             # max number of walsender processeswal_keep_segments = 30          # in logfile segments, 16MB each; 0 disableshot_standby = on                        # "on" allows queries during recovery#hot_standby_feedback = off             # send info from standby to prevent

8. 在standby檔案建立恢複檔案

$ 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資料庫進程,自動啟動流複製

$ pg_ctl start -wwaiting for server to start....LOG:  could not create IPv6 socket: Address family not supported by protocolLOG:  redirecting log output to logging collector processHINT:  Future log output will appear in directory "pg_log". doneserver started

10. 檢查primary和standby資料庫的延遲

• 通過函數和系統資料表查看

edbstore=# select * from pg_stat_replication;           #在primary主庫查看-[ RECORD 1 ]----+------------------------------pid              | 15013usesysid         | 19206usename          | replapplication_name | walreceiverclient_addr      | 10.189.100.195client_hostname  | client_port      | 56072backend_start    | 2017-06-13 08:10:35.400508-07backend_xmin     | state            | streamingsent_location    | 7/EC01A588write_location   | 7/EC01A588flush_location   | 7/EC01A588replay_location  | 7/EC01A588sync_priority    | 0sync_state       | asyncedbstore=# 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)

• 通過進程查看

$ 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

 

PostgreSQL資料庫單機擴充為流複製