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）.下面在搭建過程中再做相關具體說明，關于一些基礎概念和原理先介紹到這裡。

pg_basebackup tool官網介紹：

https://www.postgresql.org/docs/current/static/p-pgbasebackup.html

詳細配置環境：

下面開始實戰，首先準備兩台伺服器，我這裡開了2個虛機做測試，分别是：

主庫（master）CentOS release 6.5 (Final) 10.0.0.100 postgresql 9.5.9

從庫（standby）CentOS release 6.7 (Final) 10.0.0.110 postgresql 9.5.9

從主庫配置開始。

首先要提前在master機器10.0.0.100安裝好postgresql,采用的是二進制安裝包，具體參考本博文的postgresql二進制安裝過程。

主庫配置:

注意此處的操作都是在主庫（10.0.0.100）上進行的，首先打開資料目錄下的postgresql.conf檔案然後做以下修改：

1.listen_address = ‘*’（預設localhost）

2.port = 10280 （預設是5432）

3.wal_level = hot_standby（預設是minimal）

4.max_wal_senders=2（預設是0）

5.wal_keep_segments=64（預設是0）

下面對上述參數稍作說明

第一個是監聽任何主機，wal_level表示啟動搭建Hot Standby，max_wal_senders則需要設定為一個大于0的數，它表示主庫最多可以有多少個并發的standby資料庫，而最後一個wal_keep_segments也應當設定為一個盡量大的值，以防止主庫生成WAL日志太快，日志還沒有來得及傳送到standby就被覆寫，但是需要考慮磁盤空間允許，一個WAL日志檔案的大小是16M：

[postgres@localhost data]$ cd /data/pgsql100/data/pg_xlog/

[postgres@localhost pg_xlog]$ ls

000000010000000000000001 000000010000000000000002 000000010000000000000003 000000010000000000000004 000000010000000000000005 archive_status

[postgres@localhost pg_xlog]$ du -sh *

16M 000000010000000000000001

16M 000000010000000000000002

16M 000000010000000000000003

16M 000000010000000000000004

16M 000000010000000000000005

4.0K archive_status

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

CREATE ROLE replica login replication encrypted password 'replica';

接下來打開資料目錄下的pg_hba.conf檔案然後做以下修改：

[postgres@localhost pg_xlog]$ tail -2 /data/pgsql100/data/pg_hba.conf

#host replication postgres ::1/128 trust

host replication replica 10.0.0.110/32 md5

如上，這行配置的意思是允許使用者replica從10.0.0.110/32網絡上發起到本資料庫的流複制連接配接，簡言之即允許從庫伺服器連接配接主庫去拖WAL日志資料。主庫配置很簡單，到此就算結束了，啟動主庫并繼續配置從庫

pg_ctl -D /data/pgsql100/data -l /data/pgsql100/log/postgres.log stop

pg_ctl -D /data/pgsql100/data -l /data/pgsql100/log/postgres.log start

從庫配置:

首先要說明的是從庫上一開始也是需要安裝postgresql資料庫服務的，應為需要pg_basebackup指令工具才能在從庫上生成的master主庫的基礎備份。但是還要強調一點的是：從庫上初始化資料庫時指定的資料目錄/data/psql110/data需要清空，才可以在從庫上使用pg_basebackup指令工具來生成master主庫的基礎備份資料。

從此處開始配置從庫（10.0.0.110），首先要通過pg_basebackup指令行工具在從庫上生成基礎備份：

[postgres@localhost data]$ pg_basebackup -h 10.0.0.100 -U replica -p 10280 -F p -x -P -R -D /data/psql110/data/ -l replbackup

Password: 密碼（replica）

46256/46256 kB (100%), 1/1 tablespace

[postgres@localhost data]$

簡單做一下參數說明（可以通過pg_basebackup --help進行檢視），

-h指定連接配接的資料庫的主機名或IP位址，這裡就是主庫的ip。

-U指定連接配接的使用者名，此處是我們剛才建立的專門負責流複制的repl使用者。

-F指定了輸出的格式，支援p（原樣輸出）或者t（tar格式輸出）。

-x表示備份開始後，啟動另一個流複制連接配接從主庫接收WAL日志。

-P表示允許在備份過程中實時的列印備份的進度。

-R表示會在備份結束後自動生成recovery.conf檔案，這樣也就避免了手動建立。

-D指定把備份寫到哪個目錄，這裡尤其要注意一點就是做基礎備份之前從庫的資料目錄（/data/psql110/data/）目錄需要手動清空。

-l表示指定一個備份的辨別。

[postgres@localhost data]$ cat /data/psql110/data/recovery.conf

standby_mode = 'on'

primary_conninfo = 'user=replica password=replica host=10.0.0.100 port=10280 sslmode=prefer sslcompression=1 krbsrvname=postgres'

運作指令後看到如下進度提示就說明生成基礎備份成功：

如上由于我們在pg_hba.conf中指定的md5認證方式，是以需要輸入密碼。最後還需要修改一下從庫資料目錄下的postgresql.conf檔案，将hot_standby改為啟用狀态，即hot_standby=on。到此為止就算配置結束了，我們現在可以啟動從庫，

[postgres@localhost data]$ egrep -v '^#|^$' /data/psql110/data/postgresql.conf|grep "hot_standby"

wal_level = hot_standby # minimal, archive, hot_standby, or logical

hot_standby = on # "on" allows queries during recovery

[postgres@localhost data]$ pg_ctl -D /data/psql110/data -l /data/psql110/log/postgres.log start

server starting

從庫上檢視到流複制程序：

[postgres@localhost data]$ ss -lntup|grep postgres

tcp LISTEN 0 128 :::10280 :::* users:(("postgres",23161,4))

tcp LISTEN 0 128 *:10280 *:* users:(("postgres",23161,3))

[postgres@localhost data]$ ps -ef|grep postgres

root 5663 4716 0 18:12 pts/0 00:00:00 su - postgres

postgres 5664 5663 0 18:12 pts/0 00:00:00 -bash

postgres 5855 5664 0 18:13 pts/0 00:00:00 /bin/bash /usr/local/pgsql/bin/psql

postgres 5857 5855 0 18:13 pts/0 00:00:00 /usr/local/pgsql/bin/psql.bin

root 12406 7244 0 18:34 pts/1 00:00:00 su - postgres

postgres 12407 12406 0 18:34 pts/1 00:00:00 -bash

root 13861 13810 0 18:47 pts/3 00:00:00 su - postgres

postgres 13862 13861 0 18:47 pts/3 00:00:00 -bash

root 21768 21736 0 19:54 pts/2 00:00:00 su - postgres

postgres 21769 21768 0 19:54 pts/2 00:00:00 -bash

postgres 23161 1 0 20:05 pts/2 00:00:00 /usr/local/pgsql/bin/postgres -D /data/psql110/data

postgres 23164 23161 0 20:05 ? 00:00:00 postgres: startup process recovering 000000010000000000000007

postgres 23165 23161 0 20:05 ? 00:00:00 postgres: checkpointer process

postgres 23166 23161 0 20:05 ? 00:00:00 postgres: writer process

postgres 23167 23161 0 20:05 ? 00:00:00 postgres: stats collector process

postgres 23168 23161 0 20:05 ? 00:00:00 postgres: wal receiver process streaming 0/7000140

postgres 23240 21769 0 20:06 pts/2 00:00:00 ps -ef

postgres 23241 21769 0 20:06 pts/2 00:00:00 grep postgres

主庫上檢視到流複制程序：

[postgres@localhost pg_xlog]$ ps -ef|grep postgres

root 2904 2642 0 00:40 pts/0 00:00:00 su - postgres

postgres 2905 2904 0 00:40 pts/0 00:00:00 -bash

postgres 2939 1 0 00:42 pts/0 00:00:00 /usr/local/pgsql/bin/postgres -D /data/pgsql100/data

postgres 2941 2939 0 00:42 ? 00:00:00 postgres: checkpointer process

postgres 2942 2939 0 00:42 ? 00:00:00 postgres: writer process

postgres 2943 2939 0 00:42 ? 00:00:00 postgres: wal writer process

postgres 2944 2939 0 00:42 ? 00:00:00 postgres: autovacuum launcher process

postgres 2945 2939 0 00:42 ? 00:00:00 postgres: stats collector process

root 3109 3064 0 00:58 pts/2 00:00:00 su - postgres

postgres 3110 3109 0 00:58 pts/2 00:00:00 -bash

postgres 3151 3110 0 00:59 pts/2 00:00:00 /bin/bash /usr/local/pgsql/bin/psql -p10280

postgres 3153 3151 0 00:59 pts/2 00:00:00 /usr/local/pgsql/bin/psql.bin -p10280

root 3189 3087 0 01:07 pts/3 00:00:00 su - postgres

postgres 3190 3189 0 01:07 pts/3 00:00:00 -bash

postgres 3272 2939 0 01:25 ? 00:00:00 postgres: postgres testdb01 [local] idle

postgres 3415 2939 0 02:16 ? 00:00:00 postgres: wal sender process replica 10.0.0.110(34021) streaming 0/7000140

postgres 3422 3190 0 02:17 pts/3 00:00:00 ps -ef

postgres 3423 3190 0 02:17 pts/3 00:00:00 grep postgres

此時從庫上可以看到流複制的程序，同樣的主庫也能看到該程序。表明主從流複制配置成功。

同步測試示範：

建立庫和建表做測試，在master伺服器（10.0.0.100）中的建立testdb02庫并且建一張表并添加幾條資料：

master上操作：

postgres=# create database testdb02;

CREATE DATABASE

檢查：

[postgres@localhost pg_xlog]$ psql -p10280 -c '\list'|grep testdb02

testdb01=# \c testdb02

You are now connected to database "testdb02" as user "postgres".

testdb02=# \d

No relations found.

建立表：

CREATE TABLE weather ( city varchar(80), temp_lo int, temp_hi int, prcp real,date date);

List of relations

Schema | Name | Type | Owner

--------+---------+-------+----------

public | weather | table | postgres

(1 row)

testdb02=# \d weather

Table "public.weather"

Column | Type | Modifiers

---------+-----------------------+-----------

city | character varying(80) |

temp_lo | integer |

temp_hi | integer |

prcp | real |

date | date |

testdb02=#

testdb02=# INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('China05', '47', '59', '1.0', '1994-12-15');

INSERT 0 1

testdb02=# INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('China04', '46', '58', '2.0', '1994-12-14');\

testdb02=# select * from weather;

city | temp_lo | temp_hi | prcp | date

---------+---------+---------+------+------------

China05 | 47 | 59 | 1 | 1994-12-15

China04 | 46 | 58 | 2 | 1994-12-14

(2 rows)

從庫上檢查：

[postgres@localhost data]$ psql -p10280 -c '\list'|grep testdb02

postgres=# \c testdb02;

testdb02=# \d weather;

testdb02=#

可以看到完美同步，那麼從庫是否能删除呢？測試一下：

從庫上測試删除資料庫testdb02;

postgres=# drop database testdb02;

ERROR: cannot execute DROP DATABASE in a read-only transaction

postgres=# drop database testdb01;

standby的資料無法删除，正如我們之前說的，standby隻提供隻讀服務，而隻有master才能進行讀寫操作，是以master才有權限删除資料。master删除的同時standby中的資料也将同步删除，

主庫中執行

關于異步流複制的内容到這裡.

參考博文：

http://blog.csdn.net/wzyzzu/article/details/53331206

本文轉自 wjw555 51CTO部落格，原文連結:http://blog.51cto.com/wujianwei/1977210

postgresql主從實作之異步流複制檢視複制狀态

繼續閱讀

Ubuntu Linux下Apache的配置檔案

ACS基本配置-權限等級管理

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

samba伺服器的功能

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

【Linux】UDP廣播封包接收速率問題

SequoiaDB巨杉資料庫C++驅動概述

Linux裝置模型（中）之上層容器

Oracle 批量查詢傳入List 傳回List

PowerPC平台 Linux移植三