postgreSQL主從復制

一、簡介

postgres在9.0之后引入了主從的流復制機制，所謂流復制，就是從服務器通過tcp流從主服務器中同步相應的數據。這樣當主服務器數據丟失時從服務器中仍有備份。
與基于文件日志傳送相比，流復制允許保持從服務器更新。 從服務器連接主服務器，其產生的流WAL記錄到從服務器， 而不需要等待主服務器寫完WAL文件。
PostgreSQL流復制默認是異步的。在主服務器上提交事務和從服務器上變化可見之間有一個小的延遲，這個延遲遠小于基于文件日志傳送，通常1秒能完成。如果主服務器突然崩潰，可能會有少量數據丟失。
同步復制必須等主服務器和從服務器都寫完WAL后才能提交事務。這樣在一定程度上會增加事務的響應時間。
注意：本次實驗是基于docker完成的

二、postgresql安裝

docker pull postgresql:9.4
docker images
REPOSITORY                                               TAG                 IMAGE ID            CREATED             SIZE
docker.io/postgres                                       9.4                 36726735dc3c        2 weeks ago         206 MB

docker run -it --name postgresql postgres:9.4 bash
su postgres
cd /usr/lib/postgresql/9.4/bin

存在/var/lib/postgresql/data目錄，初始化數據庫

./initdb -D /var/lib/postgresql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Etc/UTC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /var/lib/postgresql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

        ./postgres -D /var/lib/postgresql/data
or
        ./pg_ctl -D /var/lib/postgresql/data -l logfile start

到這里pg數據庫就安裝完成了
開啟數據庫，如果不需要日志可以不用加logfile

./pg_ctl start -D /var/lib/postgresql/data

同樣啟動一個備數據庫

docker run -it --name postgresql2 postgres:9.4 bash

步驟與上面相同

三、主服務器的操作

主服務器為172.18.0.4
先創建一個新目錄，用來歸檔日志，我這里其實沒有歸檔日志，按需所求。

mkdir /opt/pgsql/pg_archive

1.首先需要創建一個數據庫用戶進行主從同步。創建用戶replica，并賦予登錄和復制的權限。

postgres# CREATE ROLE replica login replication encrypted password 'replica'

2.修改pg_hba.conf，允許replica用戶來同步。
在pg_hba.conf里增加兩行：

host all all 172.18.0.5/32 trust #允許0.5連接到主服務器
host replication replica 172.18.0.5/32 md5 #允許0.5使用replica用戶來復制

這樣，就設置了replica這個用戶可以從172.18.0.4進行流復制請求。
*注：第二個字段必須要填replication
4.修改postgresql.conf

listen_addresses = '*' # 監聽所有IP
archive_mode = on # 允許歸檔
archive_command = 'cp %p /opt/pgsql/pg_archive/%f' # 用該命令來歸檔logfile segment，按需所求。
wal_level = hot_standby
max_wal_senders = 32 # 這個設置了可以最多有幾個流復制連接，差不多有幾個從，就設置幾個，設置稍大些較好。
wal_keep_segments = 256 ＃ 設置流復制保留的最多的xlog數目，一個段為16MB，盡量設置大的值，防止主庫生成日志太快還沒來得及發送給hot_standy就被覆蓋。
wal_sender_timeout = 60s ＃ 設置流復制主機發送數據的超時時間
max_connections = 100 # 這個設置要注意下，從庫的max_connections必須要大于主庫的

配置完兩個文件后重啟服務器。

pg_ctl stop -D /var/lib/postgresql/data
pg_ctl start -D /var/lib/postgresql/data

3.測試0.5能否連接0.4數據庫。在0.5上運行如下命令：

psql -h 172.18.0.4 -U postgres

看看是否能進入數據庫。若可以，則正常。

四、從服務器的操作

1.從主節點拷貝數據到從節點

su - postgres
rm -rf /opt/pgsql/data/* #先將data目錄下的數據都清空
pg_basebackup -h 172.18.0.4 -U replica -D /var/lib/postgresql/data -X stream -P # 從0.4拷貝數據到0.5（基礎備份）
mkdir /opt/pgsql/pg_archive

2.配置recovery.conf
復制/usr/share/postgresql/9.4/recovery.conf.sample 到 /var/lib/postgresql/data/recovery.conf

cp /usr/share/postgresql/9.4/recovery.conf.sample /var/lib/postgresql/data/recovery.conf

修改recovery.conf

standby_mode = on # 說明該節點是從服務器
primary_conninfo = 'host=172.18.0.4 port=5432 user=replica password=replica' # 主服務器的信息以及連接的用戶
recovery_target_timeline = 'latest'

3.配置postgresql.conf

wal_level = hot_standby
max_connections = 1000 ＃ 一般查多于寫的應用從庫的最大連接數要比較大
hot_standby = on ＃ 說明這臺機器不僅僅是用于數據歸檔，也用于數據查詢
max_standby_streaming_delay = 30s # 數據流備份的最大延遲時間
wal_receiver_status_interval = 10s # 多久向主報告一次從的狀態，當然從每次數據復制都會向主報告狀態，這里只是設置最長的間隔時間
hot_standby_feedback = on # 如果有錯誤的數據復制，是否向主進行反饋

配置完后重啟從服務器

pg_ctl stop -D /var/lib/postgresql/data
pg_ctl start -D /var/lib/postgresql/data

五、驗證是否部署成功

在主節點數據庫里執行：

select client_addr,sync_state from pg_stat_replication;

結果如下：

postgres=# select client_addr,sync_state from pg_stat_replication;
 client_addr | sync_state 
-------------+------------
 172.18.0.5  | async
(1 row)

postgres=# 

說明0.5是從服務器，在接收流，而且是異步流復制。
此外，還可以分別在主、從節點上運行 ps aux | grep postgres 來查看進程：
主服務器（0.4）上：

ps aux | grep postgres
root 210 0.0 0.0 48508 1548 ? S 06:34 0:00 su postgres
postgres 211 0.0 0.1 19864 2256 ? S 06:34 0:00 bash
postgres 250 0.0 0.9 273940 17632 ? S 06:41 0:00 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/data
postgres 252 0.0 0.2 274044 3800 ? Ss 06:41 0:00 postgres: checkpointer process
postgres 253 0.0 0.1 274072 3216 ? Ss 06:41 0:00 postgres: writer process
postgres 254 0.0 0.3 273940 6108 ? Ss 06:41 0:00 postgres: wal writer process
postgres 255 0.0 0.1 274348 2656 ? Ss 06:41 0:00 postgres: autovacuum launcher process
postgres 256 0.0 0.0 129220 1836 ? Ss 06:41 0:00 postgres: stats collector process
postgres 276 0.0 0.1 274480 3164 ? Ss 06:57 0:00 postgres: wal sender process replica 172.18.0.5(42834) streaming 0/3019C90
postgres 391 0.0 0.0 38296 1752 ? R+ 07:36 0:00 ps aux
postgres 392 0.0 0.0 12772 692 ? S+ 07:36 0:00 grep postgres

可以看到有一個 wal sender 進程。

從服務器（94）上：

ps aux | grep postgres
root 394 0.0 0.0 48508 1548 ? S 06:42 0:00 su postgres
postgres 395 0.0 0.1 19884 2320 ? S 06:42 0:00 bash
postgres 488 0.0 2.3 314268 45052 ? S 06:57 0:00 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/data
postgres 489 0.0 0.2 314452 4904 ? Ss 06:57 0:00 postgres: startup process recovering 000000010000000000000003
postgres 490 0.0 0.1 314388 3524 ? Ss 06:57 0:00 postgres: checkpointer process
postgres 491 0.0 0.1 314268 2956 ? Ss 06:57 0:00 postgres: writer process
postgres 492 0.0 0.0 129220 1848 ? Ss 06:57 0:00 postgres: stats collector process
postgres 493 0.0 0.2 319036 4384 ? Ss 06:57 0:01 postgres: wal receiver process streaming 0/3019C90
postgres 508 0.0 0.0 38296 1756 ? R+ 07:37 0:00 ps aux
postgres 509 0.0 0.0 12772 700 ? S+ 07:37 0:00 grep postgres
可以看到有一個 wal receiver 進程。
至此，PostgreSQL主從流復制安裝部署完成。
在主服務器上插入數據或刪除數據，在從服務器上能看到相應的變化。從服務器上只能查詢，不能插入或刪除。

主上：

postgres=# \c test;
You are now connected to database "test" as user "postgres".
test=# create table company(
test(# id int primary KEY NOT NULL,
test(# name TEXT NOT NULL,
test(# age INT NOT NULL,
test(# address CHAR(50),
test(# salary REAL,
test(# join_date DATE
test(# );
CREATE TABLE
test=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');
INSERT 0 1
test=#
test=#
test=# select * from company;
id | name | age | address | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
1 | Paul | 32 | California | 20000 | 2001-07-13
(1 row)

從上：

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
(4 rows)

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from company
test-# ;
 id | name | age |                      address                       | salary | join_date  
----+------+-----+----------------------------------------------------+--------+------------
  1 | Paul |  32 | California                                         |  20000 | 2001-07-13
(1 row)
s

完成！