Postgresql實現主從復制，讀寫分離

前言

簡單記錄一下postgresql主從的實現方式之一——基于Standby的異步流復制，這是PostgreSQL9.x版本（2010.9）之后提供的一個很nice的功能，類似的功能在Oracle中是11g之后才提供的active dataguard和SQL Server 2012版本之后才提供的日志傳送，此處再次為pg鼓掌，確實是一個很棒的開源數據庫。廢話不多說，本篇blog就詳細記錄一下在pg9.5中實現Hot Standby異步流復制的完整配置過程和注意事項。

Standby數據庫原理

簡單介紹一些基礎概念與原理，首先我們做主從同步的目的就是實現db服務的高可用性，通常是一臺主數據庫提供讀寫，然后把數據同步到另一臺從庫，然后從庫不斷apply從主庫接收到的數據，從庫不提供寫服務，只提供讀服務。在postgresql中提供讀寫全功能的服務器稱為primary database或master database，在接收主庫同步數據的同時又能提供讀服務的從庫服務器稱為hot standby server。

PostgreSQL在數據目錄下的pg_xlog子目錄中維護了一個WAL日志文件，該文件用于記錄數據庫文件的每次改變，這種日志文件機制提供了一種數據庫熱備份的方案，即：在把數據庫使用文件系統的方式備份出來的同時也把相應的WAL日志進行備份，即使備份出來的數據塊不一致，也可以重放WAL日志把備份的內容推到一致狀態。這也就是基于時間點的備份（Point-in-Time Recovery），簡稱PITR。而把WAL日志傳送到另一臺服務器有兩種方式，分別是：

WAL日志歸檔（base-file）

流復制（streaming replication）

第一種是寫完一個WAL日志后，才把WAL日志文件拷貝到standby數據庫中，簡言之就是通過cp命令實現遠程備份，這樣通常備庫會落后主庫一個WAL日志文件。而第二種流復制是postgresql9.x之后才提供的新的傳遞WAL日志的方法，它的好處是只要master庫一產生日志，就會馬上傳遞到standby庫，同第一種相比有更低的同步延遲，所以我們肯定也會選擇流復制的方式。

在實際操作之前還有一點需要說明就是standby的搭建中最關鍵的一步——在standby中生成master的基礎備份。postgresql9.1之后提供了一個很方便的工具—— pg_basebackup，關于它的詳細介紹和參數說明可以在官網中查看（pg_basebackup tool），下面在搭建過程中再做相關具體說明，關于一些基礎概念和原理先介紹到這里。

詳細配置

下面開始實戰，首先準備兩臺服務器，我這里開了2個虛擬機做測試，分別是：

主庫（master） centos-release-7-3.1511 192.168.42.71 postgresql 9.5

從庫（standby） centos-release-7-3.1511 192.168.42.70 postgresql 9.5

一、主庫（master）安裝 并 配置postgresql

（1）安裝postgresql

1.添加RPM

$ sudo yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm

# 2.安裝PostgreSQL 9.5

$ sudo yum install postgresql95-server postgresql95-contrib

# 3.初始化數據庫（切記：從庫不需要初始化數據庫）

$ sudo /usr/pgsql-9.5/bin/postgresql95-setup initdb

# 4.設置開機自啟動

$ sudo systemctl enable postgresql-9.5.service

#5.啟動服務

$ sudo systemctl start postgresql-9.5.service

#6.停止服務

$sudo systemctl stop postgresql-9.5.service

安裝完成默認會做三件事：

1. 創建 `postgres` 的 Linux 用戶；

2. 創建 `postgres` 不帶密碼的默認數據庫管理員賬戶；

3. 創建 `postgres` 系統數據庫。

(2)修改配置文件

cd? /var/lib/pgsql/9.5/data

vim pg_hba.conf 增加以下配置：（切記：必須要配置為從庫的ip/32）

host all all 0.0.0.0 0.0.0.0 md5

host replication postgres 192.168.42.70/32 md5 ? ?? #這句話的意思允許從數據庫連接主數據庫去拖wal日志數據

vim postgresql.conf

Listen_adresses = '*'

wal_level = hot_standby #主從設置為熱血模式，流復制必選

max_wal_senders=2 #流復制允許連接進程

wal_keep_segments =64

max_connections = 1000 默認參數，非主從配置相關參數，表示到數據庫的連接數

第一個不用說了，wal_level表示啟動搭建Hot Standby，max_wal_senders則需要設置為一個大于0的數，它表示主庫最多可以有多少個并發的standby數據庫，而最后一個wal_keep_segments也應當設置為一個盡量大的值，以防止主庫生成WAL日志太快，日志還沒有來得及傳送到standby就被覆蓋，但是需要考慮磁盤空間允許，一個WAL日志文件的大小是16M：

如上圖，一個WAL日志文件是16M，如果wal_keep_segments設置為64，也就是說將為standby庫保留64個WAL日志文件，那么就會占用16*64=1GB的磁盤空間，所以需要綜合考慮，在磁盤空間允許的情況下設置大一些，就會減少standby重新搭建的風險。接下來還需要在主庫創建一個超級用戶來專門負責讓standby連接去拖WAL日志：

(3)啟動 主 數據庫

$ sudo systemctl start postgresql-9.5.service

二、從庫（standby）安裝 并 配置postgresql

（1）從庫安裝完成后，不初始化，若已經初始化，刪除其/var/lib/pgsql/9.5/data目錄

執行，以下命令將主數據庫的/var/lib/pgsql/9.5/data的目錄同步過來。

pg_basebackup -h 192.168.42.71 -U postgres -F p -x -P -R -D?/var/lib/pgsql/9.5/data/ -l postgresbackup20190129

下面簡單做一下參數說明（可以通過pg_basebackup --help進行查看），-h指定連接的數據庫的主機名或IP地址，這里就是主庫的ip。-U指定連接的用戶名，此處是我們剛才創建的專門負責流復制的repl用戶。-F指定了輸出的格式，支持p（原樣輸出）或者t（tar格式輸出）。-x表示備份開始后，啟動另一個流復制連接從主庫接收WAL日志。-P表示允許在備份過程中實時的打印備份的進度。-R表示會在備份結束后自動生成recovery.conf文件，這樣也就避免了手動創建。-D指定把備份寫到哪個目錄，這里尤其要注意一點就是做基礎備份之前從庫的數據目錄（/usr/local/postgresql/data）目錄需要手動清空。-l表示指定一個備份的標識，運行命令后看到如下進度提示就說明生成基礎備份成功：

如上圖，由于我們在pg_hba.conf中指定的md5認證方式，所以需要輸入主數據庫postgres用戶的密碼postgres。

（2）修改配置文件

vi /postgres/data/postgresql.conf

#在基礎備份時，初始化文件是從主庫復制來的，所以配置文件一致，注釋掉

wal_level，

max_wal_senders

wal_keep_segments等參數

打開如下參數：

hot_standby = on #在備份的同時允許查詢

max_standby_streaming_delay = 30s #可選，流復制最大延遲

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

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

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

(3)創建恢復文件recovery.conf（如果我們在執行pg_basebackup的時候指定了-R 參數，可以忽略此步驟 ）

recovery.conf ? ?#在做基礎備份時，也可通過-R參數在備份結束后自動生產一個recovery.conf文件

standby_mode = on ?#指明從庫身份

primary_conninfo = 'host=10.0.120.150 port=5432 user=repl password=repl1234'?? ? ?#連接到主庫信息

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

#trigger_file = ‘/postgres/data/trigger_activestandby’

指定觸發文件，文件存在時，將觸發從庫提升為主庫，前提是必須設置”standby_mode = on”；如果不設置此參數，也可采用”pg_ctl promote“觸發從庫切換成主庫

（4）啟動從數據庫，可能會報權限不正確的錯誤

[root@scoder19 9.3]# service postgresql-9.3 start

Starting postgresql-9.3 service: [FAILED]

日志文件（/var/lib/pgsql/9.3/pgstartup.log）：報錯如下：

原因是應該是 文件夾 “/var/lib/pgsql/9.3/data”權限錯誤， 權限應該是 rwx (0700)。

解決方法：

切換到 具有root權限的用戶，

先把文件夾 “/var/lib/pgsql/9.5/data” 的用戶所屬組，給postgres 用戶：

進入/var/lib/pgsql/9.5目錄

cd /var/lib/pgsql/9.5

chown -R postgres:postgres data

把data目前的所有文件及子目錄文件權限改成： rwx (0700)

chmod -R 0700 data

重啟PostgreSQL 數據庫，問題解決。

三 、驗證主從是否配置成功

（1）查看主庫sender進程

（2）查看從庫receiver進程

（3）向主庫中創建表，并插入數據，查看從庫是否同步過去

su postgres

psql

select * from pg_stat_replication;

create table test(id int primary key,name vatchar(20),salary real);

insert into test values(10,'i love you',10000.00);

insert into test values(2,'li si',12000.00);

從庫只讀，不可插入數據，修改，刪除數據

到此主從復制建立完成。

四? 、主從切換（未完待續）

（1）通過自帶的函數，是備機則是t

postgres=# select pg_is_in_recovery();

pg_is_in_recovery

——————-

f

PostgreSQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。