PostgreSQl 12主從流復(fù)制及歸檔配置
微信公眾號(hào):運(yùn)維開(kāi)發(fā)故事,作者:姜總
大家好,我是小姜。一名落魄的低層運(yùn)維工程師。
上一篇文章說(shuō)道PostgreSQL 12 的源碼部署,這里我們說(shuō)一下PostgreSQl 12的主從流復(fù)制和歸檔配置。
主從復(fù)制的兩種形式
1) 基于文件的日志傳送
創(chuàng)建一個(gè)高可用性(HA)集群配置可采用連續(xù)歸檔,集群中主服務(wù)器工作在連續(xù)歸檔模式下,備服務(wù)器工作在連續(xù)恢復(fù)模式下(1臺(tái)或多臺(tái)可隨時(shí)接管主服務(wù)器),備持續(xù)從主服務(wù)器讀取WAL文件。連續(xù)歸檔不需要對(duì)數(shù)據(jù)庫(kù)表做任何改動(dòng),可有效降低管理開(kāi)銷(xiāo),對(duì)主服務(wù)器的性能影響也相對(duì)較低。直接從一個(gè)數(shù)據(jù)庫(kù)服務(wù)器移動(dòng)WAL記錄到另一臺(tái)服務(wù)器被稱(chēng)為日志傳送,PostgreSQL通過(guò)一次一文件(WAL段)的WAL記錄傳輸實(shí)現(xiàn)了基于文件的日志傳送。日志傳送所需的帶寬取根據(jù)主服務(wù)器的事務(wù)率而變化;日志傳送是異步的,即WAL記錄是在事務(wù)提交后才被傳送,那么在一個(gè)窗口期內(nèi)如果主服務(wù)器發(fā)生災(zāi)難性的失效則會(huì)導(dǎo)致數(shù)據(jù)丟失,還沒(méi)有被傳送的事務(wù)將會(huì)被丟失;數(shù)據(jù)丟失窗口可以通過(guò)使用參數(shù)archive_timeout進(jìn)行限制,可以低至數(shù)秒,但同時(shí)會(huì)增加文件傳送所需的帶寬。archive_timeout強(qiáng)制N秒以后進(jìn)行一次歸檔,若設(shè)置太小,很快就會(huì)超過(guò)wal_keep_segments 的值,導(dǎo)致數(shù)據(jù)覆蓋丟失,因此不要盲目設(shè)置。
2)流復(fù)制
PostgreSQL在9.x之后引入了主從的流復(fù)制機(jī)制,所謂流復(fù)制,就是備服務(wù)器通過(guò)tcp流從主服務(wù)器中同步相應(yīng)的數(shù)據(jù),主服務(wù)器在WAL記錄產(chǎn)生時(shí)即將它們以流式傳送給備服務(wù)器,而不必等到WAL文件被填充。默認(rèn)情況下流復(fù)制是異步的,這種情況下主服務(wù)器上提交一個(gè)事務(wù)與該變化在備服務(wù)器上變得可見(jiàn)之間客觀上存在短暫的延遲,但這種延遲相比基于文件的日志傳送方式依然要小得多,在備服務(wù)器的能力滿(mǎn)足負(fù)載的前提下延遲通常低于一秒;在流復(fù)制中,備服務(wù)器比使用基于文件的日志傳送具有更小的數(shù)據(jù)丟失窗口,不需要采用archive_timeout來(lái)縮減數(shù)據(jù)丟失窗口;PostgreSQL 12開(kāi)始,在執(zhí)行通過(guò)流復(fù)制來(lái)配置主備數(shù)據(jù)庫(kù)的時(shí)候,不再需要配置額外配置recovery.conf文件了。取而代之的是在備庫(kù)環(huán)境的$PGDATA路徑下配置一個(gè)standby.signal文件,注意該文件是一個(gè)普通的文本文件,內(nèi)容為空。理解起來(lái)就是,該文件是一個(gè)標(biāo)識(shí)文件。如果備庫(kù)通過(guò)執(zhí)行pg_ctl promote提升為主庫(kù)的話(huà),那么該文件將自動(dòng)消失。
注意:全部操作都以postgres用戶(hù)進(jìn)行。
配置主從流復(fù)制和歸檔
1)兩臺(tái)機(jī)器做免密登錄
我們備份和還原過(guò)程中所用的archive_command和restore_command命令都以postgres用戶(hù)運(yùn)行,因此我們需要針對(duì)postgres用戶(hù)實(shí)現(xiàn)ssh無(wú)密碼登錄。
# 用postgres用戶(hù)登錄到主pgsql服務(wù)器 ssh-keygen -t rsa # 一路回車(chē) scp /home/postgres/.ssh/id_rsa.pub postgres@10.10.22.152:/home/postgres/.ssh/authorized_keys 或者拷貝id_rsa.pub文件到從pgsql上,然后到從上執(zhí)行以下命令 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys # 登錄從pgsql服務(wù)器查看權(quán)限 chmod 700 /home/postgres/.ssh chmod 600 /home/postgres/.ssh/authorized_keys # 測(cè)試登錄 ssh postgres@10.10.22.152 # 從pgsql庫(kù) ssh-keygen -t rsa # 一路回車(chē) scp /home/postgres/.ssh/id_rsa.pub postgres@10.10.22.151:/home/postgres/.ssh/authorized_keys # 登錄從pgsql服務(wù)器查看權(quán)限 chmod 700 /home/postgres/.ssh chmod 600 /home/postgres/.ssh/authorized_keys # 測(cè)試登錄 ssh postgres@10.10.22.152
2)主庫(kù)配置
# 創(chuàng)建用戶(hù) ceate user replica with replication login password 'replication'; alter user replica with password 'replication'; # 修改pg_hba.conf host replication replica 10.10.0.0/16 md5 # 修改配置文件 $ vim /data/postgresql-12/data/postgresql.conf # 監(jiān)聽(tīng)所有IP listen_addresses = "0.0.0.0" # 最大連接數(shù),據(jù)說(shuō)從機(jī)需要大于或等于該值 max_connections = 200 # 設(shè)置主pgsql為生成wal的主機(jī),9.6開(kāi)始沒(méi)有hot_standby(熱備模式) wal_level = replica # 開(kāi)啟連續(xù)歸檔 archive_mode = on #歸檔命令。-o "StrictHostKeyChecking no" 作用是取消第一次連接輸入yes或者no archive_command = 'scp -o "StrictHostKeyChecking no" %p pgslave.ptcloud.t.home:/data/postgresql-12/archive/%f' # archive_command = 'test ! -f /data/postgresql-12/archive/%f && scp %p pgslave.ayunw.cn:/data/postgresql-12/archive/%f' archive_cleanup_command = '/usr/local/postgresql-12/bin/pg_archivecleanup -d /data/postgresql-12/data/pg_wal %r >> /data/postgresql-12/log/archive_cleanup.log 2>&1' # 最多有16個(gè)流復(fù)制連接。 max_wal_senders = 16 # 設(shè)置流服務(wù)保留的最多wal(老版本叫xlog)文件個(gè)數(shù) wal_keep_segments = 256 # 數(shù)據(jù)堆清理的最大進(jìn)程 autovacuum_max_workers = 2 max_worker_processes = 16 max_logical_replication_workers = 10 # 日志設(shè)置 log_destination = 'stderr' logging_collector = on log_directory = '/data/postgresql-12/log' log_filename = 'postgresql-%w.log' log_file_mode = 0600 log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 1GB log_min_messages = error # 執(zhí)行超過(guò)300ms的sql語(yǔ)句會(huì)記錄到pgsql的日志文件,類(lèi)似于慢日志 # 一般設(shè)置300ms就好,慢日志會(huì)打到pgsql日志文件,方便查問(wèn)題 log_min_duration_statement = 300 log_checkpoints = on log_connections = on log_disconnections = on log_error_verbosity = verbose log_hostname = on log_line_prefix = '%m [%p] ' log_lock_waits = on log_statement = 'ddl' # 主庫(kù)設(shè)置完成后,需要root用戶(hù)重啟PG服務(wù)才能使以上配置生效 systemctl daemon-reload systemctl restart postgresql su - postgres psql # 主庫(kù)創(chuàng)建具有流復(fù)制權(quán)限的用戶(hù)replica CREATE user replica login replication encrypted password 'replication'; postgres=# \du; List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} replica | Replication | {} # 主庫(kù)增加主從復(fù)制的信任訪問(wèn)(pg_hba.conf) $ vim /data/postgresql-12/data/pg_hba.conf # replication privilege. host replication replica 10.10.0.0/16 trust # 重啟服務(wù) systemctl daemon-reload systemctl restart postgresql systemctl status postgresql
3)從庫(kù)配置
# 停止從庫(kù),刪除從pgsql數(shù)據(jù)目錄中的數(shù)據(jù) # 備份數(shù)據(jù)目錄 mkdir -p /opt/pgsqldata_backup mv /data/postgresql-12/data/* /opt/pgsqldata_backup pg_ctl -D /data/postgresql-12/data -l logfile stop rm -rf /data/postgresql-12/data/*
4)從庫(kù)做基礎(chǔ)備份
從主服務(wù)器上copy數(shù)據(jù)到從服務(wù)器,這一步叫做“基礎(chǔ)備份”
su - postgres $ pg_basebackup -h 10.10.22.151 -p 5432 -U replica -W -R -Fp -Xs -Pv -D /data/postgresql-12/data/ Password: pg_basebackup: initiating base backup, waiting for checkpoint to complete pg_basebackup: checkpoint completed pg_basebackup: write-ahead log start point: 0/8000028 on timeline 1 pg_basebackup: starting background WAL receiver pg_basebackup: created temporary replication slot "pg_basebackup_13370" 31384/31384 kB (100%), 1/1 tablespace pg_basebackup: write-ahead log end point: 0/8000100 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: syncing data to disk ... pg_basebackup: base backup completed
?
參數(shù)說(shuō)明:
-h 啟動(dòng)的主庫(kù)數(shù)據(jù)庫(kù)地址
-p 主庫(kù)數(shù)據(jù)庫(kù)端口
-U 流復(fù)制用戶(hù)
-W 使用密碼驗(yàn)證,要用replica的密碼
-Fp 備份輸出正常的數(shù)據(jù)庫(kù)目錄
-Xs 使用流復(fù)制的方式進(jìn)行復(fù)制
-Pv 輸出復(fù)制過(guò)程的詳細(xì)信息
-R 為備庫(kù)創(chuàng)建recovery.conf文件。但是pgsql 10以后的新版本的pgsql不需要這個(gè)文件了。
-D 指定創(chuàng)建的備庫(kù)的數(shù)據(jù)庫(kù)目錄
?
5) 配置從庫(kù)的配置文件
注意:這時(shí)候,從庫(kù)數(shù)據(jù)目錄下的postgresql.conf文件是剛才從主的pgsql上同步過(guò)來(lái)的,并不是pgsql的配置文件,你需要將原先老的從庫(kù)上的配置文件拿過(guò)來(lái)用。
$ cd /data/postgresql-12/data/ $ mv postgres.conf postgres.conf_master.bak $ cp /opt/pgsqldata_backup/postgres.conf postgres.conf $ vim /data/postgresql-12/data/postgres.conf # 監(jiān)聽(tīng)所有IP listen_addresses = "0.0.0.0" # 最大連接數(shù),從pgsql需要大于或等于主的值 max_connections = 300 restore_command = 'cp /data/postgresql-12/archive/%f %p' archive_cleanup_command = '/usr/local/postgresql-12/bin/pg_archivecleanup -d /data/postgresql-12/data/pg_wal %r && /usr/local/postgresql-12/bin/pg_archivecleanup -d /data/postgresql-12/archive %r >> /data/postgresql-12/log/archive_cleanup.log 2>&1' # 9.6開(kāi)始沒(méi)有hot_standby(熱備模式) wal_level = replica # 最多有16個(gè)流復(fù)制連接。 max_wal_senders = 16 # 設(shè)置比主庫(kù)大,可以設(shè)置為2倍的數(shù)值 wal_keep_segments = 512 max_logical_replication_workers = 10 autovacuum_max_workers = 2 # 和主的值保持一致即可 max_worker_processes = 16 # 說(shuō)明這臺(tái)機(jī)器不僅用于數(shù)據(jù)歸檔,還可以用于數(shù)據(jù)查詢(xún) hot_standby = on #流備份的最大延遲時(shí)間 max_standby_streaming_delay = 30s # 向主機(jī)匯報(bào)本機(jī)狀態(tài)的間隔時(shí)間 wal_receiver_status_interval = 10s # 出現(xiàn)錯(cuò)誤復(fù)制,向主機(jī)反饋 hot_standby_feedback = on # 日志設(shè)置 log_destination = 'stderr' logging_collector = on log_directory = '/data/postgresql-12/log' log_filename = 'postgresql-%w.log' log_file_mode = 0600 log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 1GB log_min_messages = error # 執(zhí)行超過(guò)300ms的sql語(yǔ)句會(huì)被記錄到pgsql的日志文件中 log_min_duration_statement = 300 log_checkpoints = on log_connections = on log_disconnections = on log_error_verbosity = verbose log_hostname = on log_line_prefix = '%m [%p] ' log_lock_waits = on log_statement = 'ddl'
6) 重啟從庫(kù)
要保證從庫(kù)的數(shù)據(jù)目錄是postgres屬主和屬組,且權(quán)限為0700
su - postgres pg_ctl -D /data/postgresql-12/data restart
驗(yàn)證pgsql主從
# 登錄主庫(kù) su - postgres postgres=# psql postgres=# select client_addr,sync_state from pg_stat_replication; client_addr | sync_state --------------+------------ 10.10.22.152 | async (1 row) select pid, usename, application_name, client_addr, backend_start, client_port, state, sync_state from pg_stat_replication; pid | usename | application_name | client_addr | backend_start | client_port | state | sync_state -------+---------+------------------+-------------+-------------------------------+-------------+-----------+------------ 28356 | repl | walreceiver | 10.10.22.152 | 2021-12-30 17:00:59.357653+08 | 48660 | streaming | async (1 row)
以上說(shuō)明10.10.22.152服務(wù)器是從節(jié)點(diǎn),在接收異步流復(fù)制
到這里,主流復(fù)制和歸檔配置完成。
公眾號(hào):運(yùn)維開(kāi)發(fā)故事
github:https://github.com/orgs/sunsharing-note/dashboard
作者:姜總 運(yùn)維開(kāi)發(fā)故事
歡迎關(guān)注:運(yùn)維開(kāi)發(fā)故事