PostgreSQL9.6主從配置

来源:http://www.cnblogs.com/netonline/archive/2017/10/13/7660767.html
-Advertisement-
Play Games

參考文檔： 本文涉及postgresql基於非同步方式的主從複製的配置驗證。 一．主從複製簡介 1. 基於文件的日誌傳送 創建一個高可用性（HA）集群配置可採用連續歸檔，集群中主伺服器工作在連續歸檔模式下，備伺服器工作在連續恢復模式下（1台或多台可隨時接管主伺服器），備持續從主伺服器讀取WAL文件。 ...


參考文檔：

  1. 備機日誌傳送：https://www.postgresql.org/docs/9.6/static/warm-standby.html
  2. 英文文檔：https://www.postgresql.org/docs/current/static/index.html
  3. 中文文檔：http://www.postgres.cn/docs/9.6/
  4. pg_basebackup：https://www.postgresql.org/docs/current/static/app-pgbasebackup.html
  5. 參考1：http://blog.csdn.net/wlwlwlwl015/article/details/53287855
  6. 參考2：http://www.cnblogs.com/yjf512/p/4499547.html

 本文涉及postgresql基於非同步方式的主從複製的配置驗證。 

一．主從複製簡介

1. 基於文件的日誌傳送

創建一個高可用性（HA）集群配置可採用連續歸檔，集群中主伺服器工作在連續歸檔模式下，備伺服器工作在連續恢復模式下（1台或多台可隨時接管主伺服器），備持續從主伺服器讀取WAL文件。

連續歸檔不需要對資料庫表做任何改動，可有效降低管理開銷，對主伺服器的性能影響也相對較低。

直接從一個資料庫伺服器移動WAL記錄到另一臺伺服器被稱為日誌傳送，PostgreSQL通過一次一文件（WAL段）的WAL記錄傳輸實現了基於文件的日誌傳送。

  1. 日誌傳送所需的帶寬取根據主伺服器的事務率而變化；
  2. 日誌傳送是非同步的，即WAL記錄是在事務提交後才被傳送，那麼在一個視窗期內如果主伺服器發生災難性的失效則會導致數據丟失，還沒有被傳送的事務將會被丟失；
  3. 數據丟失視窗可以通過使用參數archive_timeout進行限制，可以低至數秒，但同時會增加文件傳送所需的帶寬。

2. 流複製

PostgreSQL在9.x之後引入了主從的流複製機制，所謂流複製，就是備伺服器通過tcp流從主伺服器中同步相應的數據，主伺服器在WAL記錄產生時即將它們以流式傳送給備伺服器，而不必等到WAL文件被填充。

  1. 預設情況下流複製是非同步的，這種情況下主伺服器上提交一個事務與該變化在備伺服器上變得可見之間客觀上存在短暫的延遲，但這種延遲相比基於文件的日誌傳送方式依然要小得多，在備伺服器的能力滿足負載的前提下延遲通常低於一秒；
  2. 在流複製中，備伺服器比使用基於文件的日誌傳送具有更小的數據丟失視窗，不需要採用archive_timeout來縮減數據丟失視窗；
  3. 將一個備伺服器從基於文件日誌傳送轉變成基於流複製的步驟是：把recovery.conf文件中的primary_conninfo設置指向主伺服器；設置主伺服器配置文件的listen_addresses參數與認證文件即可。 

二．驗證環境

1. 操作系統

CentOS-7-x86_64-Everything-1511

2. PostgresSQL版本

PostgreSQL 9.6.3：https://www.postgresql.org/download/linux/redhat/

3. 主機

採用VMware ESXi虛擬出的2台伺服器：

host1：psql_master，10.11.4.186

host2：psql_standby，10.11.4.187 

三．主庫配置

1. 創建複製用戶

#需要一個賬號進行主從同步
postgres=#create role repl login replication encrypted password 'repl@123';

2. 認證文件pg_hba.conf

#配置從庫可以採用repl賬號進行同步
[root@psql_master ~]# vim /var/lib/pgsql/9.6/data/pg_hba.conf 
host replication repl 10.11.4.187/32 md5

3. 主庫配置文件postgresql.conf

[root@psql_master ~]# vim /var/lib/pgsql/9.6/data/postgresql.conf
#監聽埠
listen_addresses = '*'

#主從設置為熱備模式，流複製必選參數
wal_level = hot_standby

#流複製允許的連接進程，一般同standby數量一致
max_wal_senders = 2

#流複製在沒有基於文件的連續歸檔時，主伺服器可能在備機收到WAL日誌前回收這些舊的WAL，此時備機需要重新從一個新的基礎備份初始化；可設置wal_keep_segments為一個足夠高的值來確保舊的WAL段不會被太早重用；1個WAL日誌為16MB，所以在設置wal_keep_segments時，在滿足空間的前提下可以儘量設置大一些
wal_keep_segments = 64

#預設參數，非主從配置相關參數，表示到資料庫的連接數，一般從庫做主要的讀服務時，設置值需要高於主庫
max_connections = 100

4. 重啟服務

#同時註意打開防火牆埠打開
[root@psql_master ~]# systemctl restart postgresql-9.6

四．從庫配置

從庫安裝postgresql後，暫不初始化，如果從庫已初始化，可以清空其data目錄（預設安裝是/ /var/lib/pgsql/9.6/data/目錄）。

1. 基礎備份

[root@psql_standby ~]# pg_basebackup -h 10.11.4.186 -p 5432 -U repl -F p -P -D /var/lib/pgsql/9.6/data/
#-h，主庫主機，-p，主庫服務埠；
#-U，複製用戶；
#-F，p是預設輸出格式，輸出數據目錄和表空間相同的佈局，t表示tar格式輸出；
#-P，同--progress，顯示進度；
#-D，輸出到指定目錄；
#因為主庫採用的是md5認證，這裡需要密碼認證。

2. 備份目錄許可權

#基於root賬號做的基礎備份，需要將相關目錄文件的許可權變更
[root@psql_standby ~]# chown -R postgres:postgres /var/lib/pgsql/9.6/data/

3. 從庫配置文件postgresql.conf

#在基礎備份時，初始化文件是從主庫複製來的，所以配置文件一致，可將wal_level，max_wal_senders與wal_keep_segments等參數註釋，以下是新增或修改的參數
[root@psql_standby ~]# vim /var/lib/pgsql/9.6/data/postgresql.conf
#在備份的同時允許查詢
hot_standby = on

#可選，流複製最大延遲
max_standby_streaming_delay = 30s

#可選，從向主報告狀態的最大間隔時間
wal_receiver_status_interval = 10s

#可選，查詢衝突時向主反饋
hot_standby_feedback = on

#預設參數，非主從配置相關參數，表示到資料庫的連接數，一般從庫做主要的讀服務時，設置值需要高於主庫
max_connections = 1000

4. 恢覆文件recovery.conf

#在做基礎備份時，也可通過-R參數在備份結束後自動生產一個recovery.conf文件
[root@psql_standby ~]# cp /usr/pgsql-9.6/share/recovery.conf.sample /var/lib/pgsql/9.6/data/recovery.conf

[root@psql_standby ~]# chown postgres:postgres /var/lib/pgsql/9.6/data/recovery.conf

[root@psql_standby ~]# vim /var/lib/pgsql/9.6/data/recovery.conf
#指明從庫身份
standby_mode = on

#連接到主庫信息
primary_conninfo = 'host=10.11.4.186 port=5432 user=repl password=repl@123'

#同步到最新數據
recovery_target_timeline = 'latest'

#指定觸發文件，文件存在時，將觸發從庫提升為主庫，前提是必須設置”standby_mode = on”；如果不設置此參數，也可採用”pg_ctl promote“觸發從庫切換成主庫
#trigger_file = ‘/var/lib/pgsql/9.6/data/trigger_activestandby’

5. 重啟服務

[root@psql_standby ~]# systemctl restart postgresql-9.6

五．使用驗證

1. 查看進程 

1）主庫sender進程

[root@psql_master ~]# ps -ef | grep postgres

2）從庫receiver過程 

[root@psql_standby ~]# ps -ef | grep postgres

2. 查看複製狀態（主庫）

postgres=# \x
postgres=# select * from pg_stat_replication;
#pid，sender進程；
#usesysid，複製用戶id；
#usename，複製用戶名；
#application_name，複製進程名；
#client_addr，從庫客戶端地址；
#client_hostname，從庫客戶端名；
#client_port，從庫客戶端port；
#backend_start，主從複製開始時間；
#backend_xmin，當前後端的xmin範圍，由備機提供；
#state，同步狀態，startup：連接中；catchup：同步中；streaming：同步；
#sent_location，主傳送wal的位置；
#write_location，從接收wal的位置；
#flush_location，從刷盤的wal位置；
#replay_location，從同步到資料庫的wal位置；
#sync_priority，同步優先順序，0表示非同步；1～？表示同步，數字越小優先順序越高；
#sync_state， async：非同步；sync：同步；potential；當前是非同步，但可能升級到同步模式；
#另外，”select pg_is_in_recovery();“命令也可以查看主從狀態，false是主，true為從。

3. 表複製測試 

1）建表（主庫）

postgres=# create table postgrestb(id int primary key,name VARCHAR(20),salary real);
postgres=# insert into postgrestb values(10, 'Messi', 10000.00);
postgres=# insert into postgrestb values(6, 'Xavi', 10000.00);
postgres=# select * from postgrestb;

2）查詢（從庫）

[root@psql_standby ~]# su - postgres
-bash-4.2$ psql
postgres=# \d
postgres=# select * from postgrestb;

3）從庫寫測試 

#從庫只讀，不能寫入數據
postgres=# insert into postgrestb values(8, 'Iniesta', 10000.00);

4. 主從切換 

1）切換前狀態

[root@psql_master ~]# pg_controldata /var/lib/pgsql/9.6/data/

[root@psql_standby ~]# pg_controldata /var/lib/pgsql/9.6/data/

2）主庫故障 

#以postgres賬戶停止主庫
[root@psql_master ~]# su - postgres -c "pg_ctl stop -m fast"

[root@psql_master ~]# pg_controldata /var/lib/pgsql/9.6/data/

3）創建用戶查看從庫日誌 

 

#日誌已經明確是不能連接到主庫
[root@psql_standby ~]# tailf /var/lib/pgsql/9.6/data/pg_log/postgresql-Tue.log

 

4）激活從庫

#採用”pg_ctl promote“切換從庫為主庫；
#切換後，從庫的recovery.conf文件名字變成了recovery.done
[root@psql_standby ~]# su - postgres -c "pg_ctl promote"

5）查看從庫日誌，狀態與進程

[root@psql_standby ~]# tailf /var/lib/pgsql/9.6/data/pg_log/postgresql-Tue.log

 [root@psql_standby ~]# tailf /var/lib/pgsql/9.6/data/pg_log/postgresql-Tue.log

[root@psql_standby ~]# ps aux | grep postgres

 6）總結

  1. 配合keepalived可以做postgresql的高可用，需要寫檢測主從狀態腳本，可參考：https://github.com/francs/PostgreSQL-Keepalived-HA；
  2. 檢測trigger_file存在與否也可完成從庫切換主庫；
  3. 從庫切換到主庫，故障的原主庫恢復後，可將其降為備庫，主要是設置recovery.conf文件與postgres.conf文件，最差的情況下可清空此時的備庫（原主庫）的$PGDATA，重新同步數據。  

六．同步流複製（補充）

1. 與非同步流複製的區別

  1. 同步複製必須等待主庫與從庫都寫完wal後才能commit事務，在一定程度上會增加事務的響應時間；
  2. 配置同步複製步驟： 
    1. 在主庫postgresql.conf文件中設置參數synchronous_standby_names為1個字元串或"*"，存在多個從庫時使用逗號分隔；
    2. 在主庫postgresql.conf文件中設置參數synchronous_commit參數設置為"on"，控制是否等待wal日誌buffer刷入磁碟再返回用戶事務狀態信息，同步流複製需要打開；
    3. 從庫的recovery.conf中primary_conninfo參數需要指明"application_name"。 

2. 註意事項

  1. 當只有1個從做同步流複製時，如果從庫故障，則主庫的寫也會掛起（可以看到postgres下會有數據操作的waiting進程），此時的方案建議採用1+1+n的方式，即1 master+1 slave(同步)+n slave(非同步)，做同步的slave故障後，可從n個非同步slave中選舉1個切換成同步模式；
  2. 設置synchronous_commit = off 後，即使同步複製模式的從庫故障，主庫的事務也不會出現等待掛起的現象。

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • MS SQLServer 批量附加資料庫
    /************************************************************ * 標題：MS SQLServer 批量附加資料庫 * 說明：請根據下麵的註釋使用此腳本 * 時間: 2015/7/13 11:16:41 ******************... ...
  • MSSQL查詢數據分頁
    這幾天剛好碰到數據的分頁查詢，覺得不錯，Mark一下，方法有兩種,都是使用select top，效率如何就不在這討論 方法1:利用select top配合not in(或者not exists)，查詢第n頁的時候，過濾掉n-1頁的數據即可，示例假設每頁查詢數量為5，查詢第3頁的數據； Select ...
  • Oracle05、函數+多表查詢+子查詢+集合查詢+分頁+創建表約束
    一、通用函數和條件判斷語句 單引號出現的地方如下：1）字元串，例如：'hello'2）日期型，例如：'17-12月-80'3）to_char/to_date(日期,'YYYY-MM-DD HH24:MI:SS')雙引號出現的地方如下：1）列別名，例如：select ename "姓 名" from ...
  • mysql-覆蓋索引
    什麼叫做覆蓋索引？ 解釋一： 就是select的數據列只用從索引中就能夠取得，不必從數據表中讀取，換句話說查詢列要被所使用的索引覆蓋。 解釋二： 索引是高效找到行的一個方法，當能通過檢索索引就可以讀取想要的數據，那就不需要再到數據表中讀取行了。如果一個索引包含了（或覆蓋了）滿足查詢語句中欄位與條件的 ...
  • Oracle04、基本查詢+條件查詢+排序+單行函數+通用函數介紹
    一、基本查詢 對於ORACLE，最常關註的無非就是關於ORACLE的查詢之類的語句了，而在PL/SQL上面，筆者使用的一般有兩種不同的查詢視窗：CommandWindow和SQLWindow兩種不同的視窗。 1.1現在將一些需要在CommandWindow使用的命令羅列如下： 上述分別是查詢當前用戶 ...
  • 使用mysqlfrm恢復frm表結構
    1、mysqlfrm安裝 由於mysqlfrm是mysql-utilities工具一部分，那麼我們安裝mysql-utilities即可，下載好對應的源碼包，進行編譯安裝。 shell> tar -xvzf mysql-utilities-1.6.4.tar.gz shell> cd mysql-u ...
  • SQL語句的MINUS,INTERSECT和UNION ALL
    SQL語句中的三個關鍵字:MINUS(減去),INTERSECT(交集)和UNION ALL(並集); 關於集合的概念,中學都應該學過,就不多說了.這三個關鍵字主要是對資料庫的查詢結果進行操作,正如其中文含義一樣:兩個查詢,MINUS是從第一個查詢結果減去第二個查詢結果,如果有相交部分就減去相交部分 ...
  • sql server作業實現數據同步
    作業介紹 SQL SERVER的作業是一系列由SQL SERVER代理按順序執行的指定操作。作業可以執行一系列活動，包括運行Transact-SQL腳本、命令行應用程式、Microsoft ActiveX腳本、Integration Services 包、Analysis Services 命令和查 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類