PostgresSQL 主從搭建步驟

PostgresSQL 主從搭建步驟

由於工作需要，最近開始接觸各種資料庫，並嘗試各種資料庫產品的高可用方案。今天分享的是postgresSQL的主從配置，其實還是蠻簡單的，跟隨本文的步驟，保證能實現PG主從的搭建。

1. 安裝環境

192.168.0.136 主庫
192.168.0.160 從庫
PORT: 5432
USR: postgres

2. 主庫已經運行一段時間，檢查主庫的version，保證主從資料庫的version相同。

# psql --version
psql (PostgreSQL) 9.4.11
 
# rpm -qa|grep postgres
postgresql94-libs-9.4.11-1PGDG.rhel6.x86_64
postgresql94-server-9.4.11-1PGDG.rhel6.x86_64
postgresql94-9.4.11-1PGDG.rhel6.x86_64

3. 在從庫上安裝相同版本的軟體

檢查安裝情況，已經安裝和primary相同的軟體版本

# rpm -qa|grep postgres
postgresql94-libs-9.4.11-1PGDG.rhel6.x86_64
postgresql94-server-9.4.11-1PGDG.rhel6.x86_64
postgresql94-9.4.11-1PGDG.rhel6.x86_64

4. 查詢主庫的資料庫位置

# su - postgres
$  echo $PGDATA
/var/lib/pgsql/9.4/data
$ cd /var/lib/pgsql/9.4/data
$ ls
base    pg_clog      pg_hba.conf    pg_log      pg_multixact
pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION 
postgresql.auto.conf  postmaster.opts global  pg_dynshmem  pg_ident.conf 
 pg_logical  pg_notify    pg_serial    pg_stat      pg_subtrans 
 pg_twophase  pg_xlog    postgresql.conf      postmaster.pid

確認設定檔位置

postgres=# show config_file;
  config_file             
-----------------------------------------
 /var/lib/pgsql/9.4/data/postgresql.conf

查看資料檔案目錄

postgres=# show data_directory;
    data_directory     
-------------------------
 /var/lib/pgsql/9.4/data

5. 準備修改主庫的參數檔案，先查詢一下pg_hba.conf已有的參數內容

$ cat pg_hba.conf|grep -v '^#'
local  all            all                                    peer
host    all            all            0.0.0.0/0            trust
host    all            all            ::1/128            ident

6. 在主庫的pg_hba.conf中添加

$ more pg_hba.conf
host    replication    replica    192.168.0.160                md5

這樣，就設定了replica這個使用者可以從192.168.0.160 對應的網段進行流複製請求。

7. 在主庫給postgres設定密碼，登入和備份許可權。

$psql
postgres# CREATE ROLE replica login replication encrypted password 'replica123'

8. 修改postgresql.conf，注意設定下下面幾個地方：

wal_level = hot_standby      # 這個是設定主為wal的主機
max_wal_senders = 10          # 這個設定了可以最多有幾個流複製串連，差不多有幾個從，就設定幾個
wal_keep_segments = 256        ＃ 設定流複製保留的最多的xlog數目
wal_sender_timeout = 60s      ＃ 設定流複製主機發送資料的逾時時間
max_connections = 100          # 這個設定要注意下，從庫的max_connections必須要大於主庫的
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/9.4/archive/%f'

9. 建立對應archive log存放路徑

mkdir -p /var/lib/pgsql/9.4/archive/

10. 重啟主庫，讓配置生效

# service postgresql-9.4 start
Starting postgresql-9.6 service:                          [  OK  ]

11. 在從庫遠端連線主要資料庫，驗證replica使用者是否可以正常訪問

psql -h IP-address -p 5432 dbname usename 
psql -h 192.168.0.136 -p 5432 postgres replica

12. 然後在主庫做一次基礎備份（後面的Hot-standby主要使用data目錄下檔案）：

postgres=# SELECT pg_start_backup('bak20170905');
$tar czvf /var/lib/pgsql/9.4/backups/backup_data.tar.gz.20170905 /var/lib/pgsql/9.4/data
postgres=# SELECT pg_stop_backup();

13. 將備份檔案sftp到從庫，並解壓，替換原有的data目錄

cd  /var/lib/pgsql/9.4/
mv data data_bk
mv backup_data.tar.gz.20170905 backup_data.tar.gz
tar -xzvf backup_data.tar.gz

14. 刪除一些就的身份資訊，歸檔記錄檔等

rm -rf data/pg_xlog/
mkdir -p data/pg_xlog/archive_status
rm data/postmaster.pid

15. 尋找並拷貝recovery.conf.sample檔案到data目錄下

find / -name recovery.conf.sample
/root/postgresql/postgresql-9.2.20/src/backend/access/transam/recovery.conf.sample
/usr/pgsql-9.4/share/recovery.conf.sample
複製
cp /usr/pgsql-9.4/share/recovery.conf.sample /var/lib/pgsql/9.4/data/recovery.conf

16. 然後編輯recovery.conf:

standby_mode = on
restore_command = 'cp /var/lib/pgsql/9.4/archive/%f %p'  #這個參數，我還需要確認具體含義
primary_conninfo = 'host=192.168.0.136 port=5432 user=replica password=replica123'                      # 主伺服器的資訊以及串連的使用者,這條資訊最重要
recovery_target_timeline = 'latest'

17. 拷貝下面設定檔

cp  /var/lib/pgsql/9.4/data.bk/postgresql.conf  /var/lib/pgsql/9.4/data/postgresql.conf
cp  /var/lib/pgsql/9.4/data.bk/pg_hba.conf  /var/lib/pgsql/9.4/data/pg_hba.conf

18. 然後編輯pstgresql.conf:

hot_standby = on

19. 啟動Hot-Standby：

/usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data --port=5432

20. 驗證是否部署成功

在主節點上執行，驗證已經成功搭建，說明5.160是從伺服器，在接收流，而且是非同步流複製。

postgres=# select client_addr,sync_state from pg_stat_replication;
 client_addr | sync_state
-------------+------------
 192.168.0.160 | async

21. 查詢更多資料同步資訊：

postgres=# select usename,application_name,client_addr,client_port,backend_start,backend_xmin,state,sent_location,write_location,flush_location,replay_location,sync_priority,sync_state  from pg_stat_replication;
 usename | application_name | client_addr | client_port |        backend_start        | backend_xmin |  state  | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
---------+------------------+-------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
 replica | walreceiver      | 192.168.0.160 |      39375 | 2017-09-05 17:49:22.512393+08 |              | streaming |        5/1049488    | 5/1049488        | 5/1049488      |  5/1049488      |            0 |          async

22. 此外，還可以分別在主、從節點上運行 ps aux | grep postgres 來查看進程：

主伺服器上，可以看到有一個 wal sender 進程，還有archiver進程等

# ps aux | grep postgres
postgres  1104  0.0  0.1 324652 15120 ?        S    14:26  0:00 /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data                     
postgres  1111  0.0  0.0 324652  5844 ?        Ss  14:26  0:00 postgres: wal writer process                             
postgres  1113  0.0  0.0 179796  1544 ?        Ss  14:26  0:00 postgres: archiver process  last was 000000010000000500000000.00000060.backup
postgres  8515  0.0  0.0 325448  3108 ?        Ss  17:49  0:00 postgres: wal sender process replica 192.168.0.160(39375) streaming 5/103A1D0

從伺服器上，可以看到 wal receiver 進程，和recovering進程正在恢複archive log

$ ps aux | grep postgres
postgres 11508  0.0  0.1 324684 15128 ?        S    17:49  0:00 /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data
postgres 11510  0.0  0.0 324796  4336 ?        Ss  17:49  0:00 postgres: startup process  recovering 000000010000000500000001
postgres 11513  0.0  0.0 331892  3700 ?        Ss  17:49  0:00 postgres: wal receiver process  streaming 5/103A1D0

23. 可以在下面路徑中，看到從庫接收的archive log檔案

# pwd
/var/lib/pgsql/9.4/data/pg_xlog
# ls
000000010000000500000001  000000010000000500000002  archive_status

至此，PostgreSQL主從流複製安裝部署完成。

在主伺服器上插入資料或刪除資料，在從伺服器上能看到相應的變化。從伺服器上只能查詢，不能插入或刪除資料。

24. 第12、13、14步驟，可以通過另一種拷貝主庫到從庫的方法，pg_basebackup命令拷貝檔案

pg_basebackup -F p --progress -D /var/lib/pgsql/9.4/data2 -h 192.168.0.136 -p 5432 -U replica --password

進入到/var/lib/pgsql/9.4/data2目錄，修改recovery.conf，這個檔案可以從pg的安裝目錄的share檔案夾中擷取，比如

cp /usr/pgsql-9.6/share/recovery.conf.sample /var/lib/pgsql/9.6/data2/recovery.conf

確保檔案夾許可權是700，這個很關鍵,其他許可權，不能正常啟動

$ chmod 0700 /var/lib/pgsql/9.6/data2

使用下面命令啟動standby

$ /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data2

本文永久更新連結地址：https://www.bkjia.com/Linux/2018-03/151186.htm