1. 目標
掌握Postgresql資料庫主從部署搭建配置
2. 脈絡
部署規劃
PostgreSQL單節點安裝
PostgreSQL主從部署配置
主從同步驗證
3. 知行
3.1 簡介
PostgreSQL是一個比較高效能的資料庫， 結合PostGIS外掛程式， 使PostgreSQL成為了一個空間資料庫，能夠進行空間數據管理、數量測量與幾何拓撲分析。PostgreSQL從9.3開始支持JSON數據型別， 9.4開始支持JSONB， 具備NoSQL資料庫功能， 在效能上甚至超過MongoDB。
3.2 部署規劃
主節點： 10.10.20.26
從節點： 10.10.20.27
3.3 單節點安裝
在主從部署安裝之前， 先要在兩台節點上分別都安裝Postgresql， 這裏以安裝Postgresql10版本為例。
1、安裝yum源
yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
檢視安裝包
[root@localhost local]# yum list | grep postgresql10Repository mariadb is listed more than once in the configurationpostgresql10.x86_64 10.10-1PGDG.rhel7 @pgdg10 postgresql10-contrib.x86_64 10.10-1PGDG.rhel7 @pgdg10 postgresql10-devel.x86_64 10.10-1PGDG.rhel7 @pgdg10 postgresql10-libs.x86_64 10.10-1PGDG.rhel7 @pgdg10 postgresql10-server.x86_64 10.10-1PGDG.rhel7 @pgdg10 postgresql10-debuginfo.x86_64 10.10-1PGDG.rhel7 pgdg10 postgresql10-docs.x86_64 10.10-1PGDG.rhel7 pgdg10 postgresql10-odbc.x86_64 11.01.0000-1PGDG.rhel7 pgdg10 postgresql10-plperl.x86_64 10.10-1PGDG.rhel7 pgdg10 postgresql10-plpython.x86_64 10.10-1PGDG.rhel7 pgdg10 postgresql10-pltcl.x86_64 10.10-1PGDG.rhel7 pgdg10 postgresql10-tcl.x86_64 2.4.0-1.rhel7 pgdg10 postgresql10-tcl-debuginfo.x86_64 2.3.1-1.rhel7 pgdg10 postgresql10-test.x86_64 10.10-1PGDG.rhel7 pgdg10
2、執行安裝
需要安裝postgresql10-contrib.x86_64 、postgresql10-devel.x86_64 和postgresql10-server.x86_64。
yum -y install postgresql10-client postgresql10-server postgresql10-contrib postgresql10-devel
註意， 與單機節點安裝不同， 要加上contrib與devel元件。
3、初始化資料庫資訊
/usr/pgsql-10/bin/postgresql-10-setup initdb
預設生成的資料庫資訊存放路徑是在/var/lib/pgsql/版本號/data下面。
4、啟動資料庫
systemctl enable postgresql-10.service
設定為開機啟動
systemctl start postgresql-10
5、設定使用者資訊
設定管理使用者postgres
su - postgrespsql
進入postgresql控制台
[root@localhost local]-bash-4.2$ psqlpsql (10.10)Type "help" for help.postgres=
修改使用者密碼：
postgres=# alter user postgres with password '654321';ALTER ROLE
其他命令：
結束: \q列出所有庫 \l列出所有使用者 \du列出庫下所有表 \d
6、設定遠端連線許可權
預設是不能透過客戶端遠端連入， 需要做些配置：
修改繫結地址
vi /var/lib/pgsql/10/data/postgresql.conf
繫結所有IP， 以「*」星號替代：
listen_addresses = '*'
修改存取許可權
vi /var/lib/pgsql/10/data/pg_hba.conf
註釋最後三行， 並增加一行設定, 允許所有遠端主機連線：
#local replication all peer#host replication all 127.0.0.1/32 ident#host replication all ::1/128 identhost all all 0.0.0.0/0 md5
7、登陸驗證
填寫IP與使用者連線資訊，點選「連線測試」出現連線成功提示。
3.4 主從部署配置
3.4.1 簡介
PostgreSQL在9.X版本之後提供了基於Standby的異步流復制， 所謂流復制，就是從伺服器透過tcp流從主伺服器中同步相應的數據。與基於檔日誌傳送相比，流復制允許保持從伺服器更新。從伺服器連線主伺服器，其產生的流WAL記錄到從伺服器， 而不需要等待主伺服器寫完WAL檔。
PostgreSQL在數據目錄下的pg_xlog子目錄中維護了一個WAL日誌檔，該檔用於記錄資料庫檔的每次改變，這種日誌檔機制提供了一種資料庫熱備份的方案，即：在把資料庫使用檔案系統的方式備份出來的同時也把相應的WAL日誌進行備份，即使備份出來的數據塊不一致，也可以重放WAL日誌把備份的內容推到一致狀態。這也就是基於時間點的備份（Point-in-Time Recovery），簡稱PITR。而把WAL日誌傳送到另一台伺服器有兩種方式，分別是：
WAL日誌歸檔（base-file）
寫完一個WAL日誌後，才把WAL日誌檔拷貝到standby資料庫中，簡言之就是透過cp命令實作遠端備份，這樣通常備庫會落後主庫一個WAL日誌檔。
流復制（streaming replication）
流復制是postgresql9.x之後才提供的新的傳遞WAL日誌的方法，它的好處是只要master庫一產生日誌，就會馬上傳遞到standby庫，同第一種相比有更低的同步延遲，從即時性角度優先采用流復制方案。
接下來采用hot_standby 流復制方式來實作主從同步。
3.4.2 主庫配置
1、建立主從同步使用者
進入終端：
su - postgrespsql
建立用於主從同步的使用者， 使用者名稱replica, 密碼replica：
CREATE ROLE replica login replication encrypted password 'replica';
2、修改連線許可權
vi /var/lib/pgsql/10/data/pg_hba.conf
增加一行配置， 允許10.10.20.27從節點連入：
#local replication all peer#host replication all 127.0.0.1/32 ident#host replication all ::1/128 identhost all all 0.0.0.0/0 md5host replication replica 10.10.20.27/32 md5
修改資料庫配置：
vi /var/lib/pgsql/10/data/postgresql.conf
修改以下內容：
# 繫結監聽所有IPlisten_addresses = '*' # 允許歸檔archive_mode = on # 透過命令指定歸檔路徑/archive_command = 'cp %p /opt/pgsql/pg_archive/%f' # 寫入WAL的級別(minimal:不能透過基礎備份和wal日誌恢復資料庫; replica: 支持wal歸檔和復制; logical: 在replica級別添加了邏輯解碼所需的資訊)wal_level = logical # 允許最多的流復制連線發送數量， 根據從節點數量來設定max_wal_senders = 32 # 設定流復制保留的最多的xlog數目wal_keep_segments = 256 # 設定流復制發送數據的超時時間wal_sender_timeout = 60s # 最大連線數量，根據從節點與客戶端連線數來設定max_connections = 1000
3、重新開機生效
systemctl restart postgresql-10
若生產環境不能隨意重新開機， 也可采用重新載入命令：
systemctl reload postgresql-10
3.4.3 從庫配置
1、清空從節點數據
su - postgrescd /var/lib/pgsql/10/datarm -rf *
2、將主庫的基礎數據復制到從庫
pg_basebackup -D $PGDATA -Fp -Xstream -R -c fast -v -P -h 10.10.20.26 -U replica -W
接下來會要求輸入上面建立的replica使用者密碼：
Password: pg_basebackup: initiating base backup, waiting for checkpoint to completepg_basebackup: checkpoint completedpg_basebackup: write-ahead log start point: 0/6000028 on timeline 1pg_basebackup: starting background WAL receiverpg_basebackup: created temporary replication slot "pg_basebackup_27275"pg_basebackup: write-ahead log end point: 0/60000F8pg_basebackup: waiting for background process to finish streaming ...pg_basebackup: base backup completed
3、修改從庫配置
vi /var/lib/pgsql/10/data/postgresql.conf
修改配置內容：
# 寫入WAL的級別(minimal:不能透過基礎備份和wal日誌恢復資料庫; replica: 支持wal歸檔和復制; logical: 在replica級別添加了邏輯解碼所需的資訊)wal_level = logical# 根據實際套用情況， 設定最大連線數max_connections = 1000 # 從機不僅用於數據歸檔，也可用於數據查詢hot_standby = on # 數據流備份的最大延遲時間max_standby_streaming_delay = 30s# 多久向主報告一次從的狀態，當然從每次數據復制都會向主報告狀態，這裏只是設定最長的間隔時間wal_receiver_status_interval = 10s # 如果有錯誤的數據復制，是否向主進行反饋hot_standby_feedback = on
修改同步恢復配置：
vi /var/lib/pgsql/10/data/recovery.conf
修改配置內容：
standby_mode = 'on'primary_conninfo = 'user=replica password=replica host=10.10.20.26 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'recovery_target_timeline = 'latest'
4、重新開機從庫服務
systemctl restart postgresql-10
3.5 主從同步驗證
1、檢視同步狀態資訊
進入主節點：
su - postgrespsql
執行查詢：
postgres=# \xExpanded display is on.postgres=# select * from pg_stat_replication;-[ RECORD 1 ]----+------------------------------pid | 8790usesysid | 16384usename | replicaapplication_name | walreceiverclient_addr | 10.10.20.27client_hostname | client_port | 54460backend_start | 2020-04-26 19:22:38.334961+08backend_xmin | state | streamingsent_lsn | 0/726DC08write_lsn | 0/726DC08flush_lsn | 0/726DC08replay_lsn | 0/726DC08write_lag | flush_lag | replay_lag | sync_priority | 0sync_state | async
可以看到從節點10.10.20.27的同步資訊。
2、建立使用者
在主節點， 建立一個業務資料庫的使用者office:
create user office password '654321';
賦予使用者角色許可權， 根據實際情況選擇：
alter user office superuser createrole createdb replication;
3、新建資料庫
透過客戶端工具， 建立資料庫連線：
在主節點， 新建mirson資料庫:
在從節點，重新整理一下， 可以看到新建立的資料庫：
4、匯出原資料庫：
選中資料庫【mirson】-【office】, 右鍵【轉儲SQL檔】-【結構和數據】， 匯出資料庫。
如果工具匯出有問題， 可以采用命令列方式匯出：
pg_dump -h 127.0.0.1 -p 5432 -U office mirson > /usr/local/mirson.sql
5、匯入資料庫
在主節點上，建立模式 【office】:
選擇對應模式-【office】, 點選【執行SQL檔】, 選擇上面匯出的資料庫檔。
如果采用的是命令列方式匯出， 也要用命令列方式匯入：
psql -h 127.0.0.1 -p 5432 -U office mirson < /usr/local/mirson.sql
匯入報錯：
psql: FATAL: Ident authentication failed for user
要檢查配置是否加入：
vi /var/lib/pgsql/10/data/pg_hba.conf
配置檢查是否添加trust:
hostallall 127.0.0.1/32 trust
6、檢視主從節點數據
10.10.20.26主節點
10.10.20.27從節點
可以看到， 兩台節點的數據一致， 主從同步功能正常。
4、合一
透過PostgreSQL主從同步部署， 能夠有效保障主節點數據的安全， 即便主庫歸檔日誌損壞， 也可以透過從節點恢復獲取數據，主從部署還可以有效減少主節點的負載壓力， 將集中讀取的數據透過從節點處理，減少主節點的IO瓶頸和CPU負載， 如果一台從節點不夠， 也可以參照以上部署方式，擴充套件多個從節點， 從而提升整體資料庫吞吐效能。
雖然PostgreSQL原生對內送流量備援容錯機制集群模式沒有較好的支持，對於復雜的海量數據的業務， 我們可以從架構設計上做改進， 將復雜的業務進行拆分， 設為多個微服務， 搭建多個PostgreSQL主從服務群， 分散負載， 消除瓶頸，從而有效支撐海量數據的業務服務。
如喜歡本文，請點選右上角，把文章分享到朋友圈
如有想了解學習的技術點，請留言給若飛安排分享
因公眾號更改推播規則，請點「在看」並加「星標」 第一時間獲取精彩技術分享
·END·
相關閱讀：
作者：麥神-mirso
來源：https://blog.csdn.net/hxx688/article/details/105778256
版權申明：內容來源網路，僅供學習研究，版權歸原創者所有。如有侵權煩請告知，我們會立即刪除並表示歉意。謝謝!
架構師
我們都是架構師！
關註 架構師(JiaGouX)，添加「星標」
獲取每天技術幹貨，一起成為牛逼架構師
技術群請 加若飛： 1321113940 進架構師群
投稿、合作、版權等信箱： [email protected]
