渡鴉之丘: 再探 Streaming Replication — PGSQL 12 上面的設定筆記

在 PGSQL 12 開始，recovery.conf 檔案正式與 postgresql.conf / postgresql.auto.conf 合併，由同一份檔案設定所有資料庫參數了。

因此這邊紀錄新的 Streaming Replication 設置方式。

這邊的目標，除了紀錄 PGSQL 12 設置上不同的地方，同時也紀錄 WAL 備份的方式
進行演練的環境，為 LXD 上面的 CentOS7 Container。如果沒有太特殊的指令輸出內容，就忽略掉～
關於本處測試環境的設置，請參考這篇～

先產生環境

lab@lxdlab:~$ lxc launch images:centos/7/amd64 pg12-1
lab@lxdlab:~$ lxc launch images:centos/7/amd64 pg12-2

查看這些環境資訊

lab@lxdlab:~$ lxc list --columns=ns4tSP pg12-1
+--------+---------+---------------------+------------+-----------+----------+
|  NAME  |  STATE  |        IPV4         |    TYPE    | SNAPSHOTS | PROFILES |
+--------+---------+---------------------+------------+-----------+----------+
| pg12-1 | RUNNING | 10.207.69.54 (eth0) | PERSISTENT | 0         | default  |
+--------+---------+---------------------+------------+-----------+----------+
lab@lxdlab:~$ 
lab@lxdlab:~$ lxc list --columns=ns4tSP pg12-2
+--------+---------+----------------------+------------+-----------+----------+
|  NAME  |  STATE  |         IPV4         |    TYPE    | SNAPSHOTS | PROFILES |
+--------+---------+----------------------+------------+-----------+----------+
| pg12-2 | RUNNING | 10.207.69.233 (eth0) | PERSISTENT | 0         | default  |
+--------+---------+----------------------+------------+-----------+----------+
lab@lxdlab:~$

查看一下兩個環境的 Hostname，並依照 Hostname 把 IP 對應資訊填入兩個環境的 /etc/hosts 檔案裡

lab@lxdlab:~$ lxc exec pg12-1 -- cat /etc/hosts
127.0.1.1       pg12-1
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
lab@lxdlab:~$ lxc exec pg12-1 -- sed '1d' -i /etc/hosts
lab@lxdlab:~$ lxc exec pg12-2 -- sed '1d' -i /etc/hosts
lab@lxdlab:~$ lxc exec pg12-1 -- bash <<'EOF'
cat >> /etc/hosts
10.207.69.54   pg12-1
10.207.69.233  pg12-2
EOF
lab@lxdlab:~$ lxc exec pg12-2 -- bash <<'EOF'
cat >> /etc/hosts
10.207.69.54   pg12-1
10.207.69.233  pg12-2
EOF
lab@lxdlab:~$

因為這個測試環境沒有 SSH 服務，因此開始前要先安裝 SSH Server 服務。這邊偷懶，用 LXD Container 的指令來進行。
另外，因為 LXD Container 預設沒有安裝完整的 PAM 功能，因此需要把 SSHd 的 PAM 功能停掉。

lab@lxdlab:~$ lxc exec pg12-1 -- yum install -y openssh-server
lab@lxdlab:~$ lxc exec pg12-1 -- sed -e "s/^UsePAM yes/UsePAM no/g" -i /etc/ssh/sshd_config
lab@lxdlab:~$ lxc exec pg12-1 -- chkconfig sshd on
lab@lxdlab:~$ lxc exec pg12-1 -- service sshd start
lab@lxdlab:~$ 
lab@lxdlab:~$ lxc exec pg12-2 -- yum install -y openssh-server
lab@lxdlab:~$ lxc exec pg12-2 -- sed -e "s/^UsePAM yes/UsePAM no/g" -i /etc/ssh/sshd_config
lab@lxdlab:~$ lxc exec pg12-2 -- chkconfig sshd on
lab@lxdlab:~$ lxc exec pg12-2 -- service sshd start
lab@lxdlab:~$

接著在兩個環境安裝 EPEL 跟 PGDG Repository，並且僅啟用 PGDB 第一個項目（在這邊就會是 PostgreSQL 12），然後安裝資料庫軟體。這邊偷懶，用 LXD Container 的指令來進行

lab@lxdlab:~$ lxc exec pg12-1 -- yum install -y epel-release https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
lab@lxdlab:~$ lxc exec pg12-1 -- sed -e 's/enabled=1/enabled=0/g' -i /etc/yum.repos.d/pgdg-redhat-all.repo
lab@lxdlab:~$ lxc exec pg12-1 -- sed -e '0,/enabled=0/s/enabled=0/enabled=1/' -i /etc/yum.repos.d/pgdg-redhat-all.repo
lab@lxdlab:~$ lxc exec pg12-1 -- yum group install -y "PostgreSQL Database Server 12 PGDG"
lab@lxdlab:~$ 
lab@lxdlab:~$ lxc exec pg12-2 -- yum install -y epel-release https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
lab@lxdlab:~$ lxc exec pg12-2 -- sed -e 's/enabled=1/enabled=0/g' -i /etc/yum.repos.d/pgdg-redhat-all.repo
lab@lxdlab:~$ lxc exec pg12-2 -- sed -e '0,/enabled=0/s/enabled=0/enabled=1/' -i /etc/yum.repos.d/pgdg-redhat-all.repo
lab@lxdlab:~$ lxc exec pg12-2 -- yum group install -y "PostgreSQL Database Server 12 PGDG"
lab@lxdlab:~$

安裝完 PGSQL 12 的軟體後，OS 帳號 postgres 伴隨著家目錄 /var/lib/pgsql/ 便建立完畢。這邊方便起見，放幾個環境變數的 Script。
一樣用偷懶的：這邊先建立 Script 檔案，再以 LXD 丟檔案到定位，並變更檔案權限

lab@lxdlab:~$ cat << "EOF" >> ./pgsql_profile
export PATH=/usr/pgsql-12/bin:$PATH
export PGHOME=/var/lib/pgsql/
export PGDATA=/var/lib/pgsql/12/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432
export PGLOCALEDIR=/usr/pgsql-12/share/locale
EOF
lab@lxdlab:~$ cat << EOF >> ./psqlrc
\timing on
\pset null '(NULL)'
EOF
lab@lxdlab:~$ 
lab@lxdlab:~$ lxc file push ./pgsql_profile pg12-1/var/lib/pgsql/.pgsql_profile
lab@lxdlab:~$ lxc file push ./psqlrc pg12-1/var/lib/pgsql/.psqlrc
lab@lxdlab:~$ lxc exec pg12-1 -- chown -R postgres. /var/lib/pgsql/
lab@lxdlab:~$ 
lab@lxdlab:~$ lxc file push ./pgsql_profile pg12-2/var/lib/pgsql/.pgsql_profile
lab@lxdlab:~$ lxc file push ./psqlrc pg12-2/var/lib/pgsql/.psqlrc
lab@lxdlab:~$ lxc exec pg12-2 -- chown -R postgres. /var/lib/pgsql/
lab@lxdlab:~$

為了後續的 WAL 備份設置，這邊需要讓 pg12-1 跟 pg12-2 的 postgres 帳號之間可以透過 SSH 金鑰認證的方式，免密碼相互登入。這邊會暫時給 postgres 帳號一個臨時密碼，打通之間的 SSH 登入後，再拔掉密碼。有需要的話在這步驟就自行留著～
這邊沒辦法用 LXD 偷懶了。。。只能乖乖的登入作設置

lab@lxdlab:~$ lxc exec pg12-1 -- passwd postgres
Changing password for user postgres.
New password: pg
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: pg
passwd: all authentication tokens updated successfully.
lab@lxdlab:~$ lxc exec pg12-2 -- passwd postgres
Changing password for user postgres.
New password: pg
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: pg
passwd: all authentication tokens updated successfully.
lab@lxdlab:~$

lab@lxdlab:~$ lxc shell pg12-1
[root@pg12-1 ~]# su - postgres
-bash-4.2$ ssh-keygen
-bash-4.2$ ssh-copy-id pg12-1
-bash-4.2$ ssh-copy-id pg12-2
-bash-4.2$ exit
[root@pg12-1 ~]# exit
lab@lxdlab:~$

lab@lxdlab:~$ lxc shell pg12-2
[root@pg12-2 ~]# su - postgres
-bash-4.2$ ssh-keygen
-bash-4.2$ ssh-copy-id pg12-1
-bash-4.2$ ssh-copy-id pg12-2
-bash-4.2$ exit
[root@pg12-2 ~]# exit
lab@lxdlab:~$

lab@lxdlab:~$ lxc exec pg12-1 -- passwd -d postgres
lab@lxdlab:~$ lxc exec pg12-2 -- passwd -d postgres

接著正式開始：進入 Master DB 環境 pg12-1 進行操作。
先初始化 pg12-1 上面的 Master DB

lab@lxdlab:~$ lxc shell pg12-1
[root@pg12-1 ~]# export LANG=en_US.UTF8
[root@pg12-1 ~]# export PGSETUP_INITDB_OPTIONS="-E UTF-8 --wal-segsize=128"
[root@pg12-1 ~]# /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... OK

[root@pg12-1 ~]# service postgresql-12 start
[root@pg12-1 ~]#

登入 Master DB 資料庫、並設定以下資料庫參數：本處參數調整後需要重新啟動。

[root@pg12-1 ~]# su - postgres
-bash-4.2$ psql
Timing is on.
Null display is "(NULL)".
psql (12.1)
Type "help" for help.

postgres=# ALTER SYSTEM SET listen_addresses = '*';
ALTER SYSTEM
Time: 10.094 ms
postgres=# ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM
Time: 8.134 ms
postgres=# ALTER SYSTEM SET archive_mode = on;
ALTER SYSTEM
Time: 7.431 ms
postgres=# ALTER SYSTEM SET archive_command = '$PGDATA/walarchive.sh %p %f';
ALTER SYSTEM
Time: 7.045 ms
postgres=# ALTER SYSTEM SET full_page_writes = on;
ALTER SYSTEM
Time: 18.366 ms
postgres=# ALTER SYSTEM SET wal_log_hints = on;
ALTER SYSTEM
Time: 7.733 ms
postgres=# ALTER SYSTEM SET hot_standby = on;
ALTER SYSTEM
Time: 17.955 ms
postgres=# ALTER SYSTEM SET logging_collector = on; 
ALTER SYSTEM
Time: 8.100 ms
postgres=# 
postgres=# -- 原本的 $PGDATA/recovery.conf 內容
postgres=# -- primary_conninfo 可以由 pg_basebackup 來產生
postgres=# -- standby_mode 參數取消了
postgres=# -- trigger_file 改名了，叫做 promote_trigger_file
postgres=# 
postgres=# ALTER SYSTEM SET restore_command = '$PGDATA/pitrscript.sh %f %p';
ALTER SYSTEM
Time: 29.797 ms
postgres=# ALTER SYSTEM SET recovery_target_timeline = 'latest';
ALTER SYSTEM
Time: 33.407 ms
postgres=# ALTER SYSTEM SET promote_trigger_file = '$HOME/12/promote_trigger';
ALTER SYSTEM
Time: 25.276 ms
postgres=#

這邊紀錄一下特別的參數異動：
- 在 PGSQL 12 開始，之前版本的 $PGDATA/recovery.conf 檔案的設定，完全整併到 $PGDATA/postgresql.conf 裡面了。
- 原本透過 recovery.conf 或是 recovery.done 來判定資料庫是否處在 Archive Recovery 模式（Streaming Replication 或是 Point-in-Time Recovery），在 PGSQL 12 之後，會以 $PGDATA/standby.signal 以及 $PGDATA/recovery.signal 來標記
- 變化上，standby_mode 參數已經拿掉了；而 trigger_file 調整名稱為 promote_trigger_file
- 這邊用一點稍微設計過的 archive_command 跟 restore_command，會使用到一點點 Script

以下是差異示意表

	R/W Mode Streaming Replication Master	R/O Mode Streaming Replication Standby	During Recovery Point-in-Time Recovery / Crash Recovery
PGSQL 9.0 ~ PGSQL 11	預設或是不存在 $PGDATA/recovery.conf	$PGDATA/recovery.conf 以及 standby_mode = on	$PGDATA/recovery.conf 以及 restore_command
PGSQL 12 之後	預設	$PGDATA/standby.signal	$PGDATA/recovery.signal

接續，新增下面兩個 Script：
以下是 archive_command 使用的 Script，要存為 $PGDATA/walarchive.sh 檔案。這邊會將 remote-IP 填為 pg12-2，也就是放到 Standby DB 主機上。位置為 $HOME/12/backups/wal_archives/（這位置可以依需要變更）。
不過可以的話，會建議將 remote-IP 替換為第三處的存放位置，或是直接以 NFS 目錄存放 WAL 檔。

#!/bin/bash
##Filename : walarchive.sh
##Place this file inside $PGDATA
##This is a script for archive_command
##Compress the file, then pipe to the remote archive location
##Be sure to exchange SSH key for passwordless login
##Limitation: Can not handle file/dir name containing space
## $1 corresponds to %p placeholder in archive_command
## $2 corresponds to %f placeholder in archive_command

ARC_DEST='remote-IP'
ARCHIVEDIR='/var/lib/pgsql/12/backups/wal_archives/'

gzip -c $1 | ssh ${ARC_DEST} "cat - > ${ARCHIVEDIR}/$2.gz"

exit 0

以下是 restore_command 使用的 Script，存成 $PGDATA/pitrscript.sh 檔案。這邊對應上面的 Script，這邊會將 remote-IP 填為 pg12-2，以取得 WAL 檔案。
這邊保留以 ssh 的方式處理 WAL 檔案的取得，方便將 remote-IP 替換為第三處的用途

#!/bin/bash
##Filename : pitrscript.sh
##This is a script for restore_command
##Place this file inside $PGDATA
##Check and decompress remote file
##Be sure to exchange SSH key for passwordless login
##Limitation: Can not handle file/dir name containing space
## $1 corresponds to %f placeholder in restore_command
## $2 corresponds to %p placeholder in restore_command

ARC_DEST='remote-IP'
ARCHIVEDIR='/var/lib/pgsql/12/backups/wal_archives/'

if ssh ${ARC_DEST} [[ -f ${ARCHIVEDIR}/$1.gz ]]
then
  ssh ${ARC_DEST} "cat ${ARCHIVEDIR}/$1.gz" | gunzip - > $2
  exit 0

else
  echo "Requested file is not found anywhere"
  exit 1
fi

exit 0

記得將上面兩個檔案的權限增加執行的權限

-bash-4.2$ chmod +x $PGDATA/walarchive.sh 
-bash-4.2$ chmod +x $PGDATA/pitrscript.sh

接著新增一個帳號，專門給資料庫作同步使用，並增加一個密碼檔。偷懶的話也可以用 Superuser 帳號 postgres 作同步功能

-bash-4.2$ createuser --replication --pwprompt repuser
Enter password for new role: rep
Enter it again: rep
-bash-4.2$ 
-bash-4.2$ cat << EOF >> ~/.pgpass
pg12-1:5432:*:repuser:rep
pg12-2:5432:*:repuser:rep
EOF
-bash-4.2$ chmod 600 ~/.pgpass

再增加登入存取設定。這個設定通常只要重新載入就能生效了

-bash-4.2$ cat << EOF >> $PGDATA/pg_hba.conf

## Replication Setting
host   replication   repuser   pg12-1      md5
host   replication   repuser   pg12-2      md5
EOF
-bash-4.2$

最後記得要重啟，讓上面的參數生效。

[root@pg12-1 ~]# service postgresql-12 restart

以上 Master DB 就準備完畢了，接著進入 Standby DB 的環境。

登入之後，先增加一個密碼檔

lab@lxdlab:~$ lxc shell pg12-2
[root@pg12-2 ~]# su - postgres
-bash-4.2$ cat << EOF >> ~/.pgpass
pg12-1:5432:*:repuser:rep
pg12-2:5432:*:repuser:rep
EOF
-bash-4.2$ chmod 600 ~/.pgpass 
-bash-4.2$

使用 pg_basebackup --write-recovery-conf 幫忙產生 primary_conninfo；以及 --wal-method=stream 避免複製時間太久造成最後一步失敗。（這邊資料庫是空的，不會發生）

-bash-4.2$ pg_basebackup -h pg12-1 -p 5432 -U repuser -Fp -D $PGDATA --write-recovery-conf --wal-method=stream -P -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/20000060 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_953"
25317/25317 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/20000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed
-bash-4.2$

完成後可以查看一下設定檔內容，可以看到 primary_conninfo 被補上了

-bash-4.2$ cat $PGDATA/postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'
wal_level = 'replica'
archive_mode = 'on'
archive_command = '$PGDATA/walarchive.sh %p %f'
full_page_writes = 'on'
wal_log_hints = 'on'
hot_standby = 'on'
logging_collector = 'on'
restore_command = '$PGDATA/pitrscript.sh %f %p'
recovery_target_timeline = 'latest'
promote_trigger_file = '$HOME/12/promote_trigger'
primary_conninfo = 'user=repuser passfile=''/var/lib/pgsql/.pgpass'' host=''pg12-1'' port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
-bash-4.2$

除了這些之外，還需要幾個跟同步有關的參數，再增加一個 standby.signal 檔案

-bash-4.2$ cat << EOF >> $PGDATA/postgresql.auto.conf
restore_command = 'ssh pg12-primary "cat $HOME/12/backups/wal_archives/%f.gz" | gunzip - > %p'
recovery_target_timeline = 'latest'
promote_trigger_file = '$HOME/12/promote_trigger'
EOF
-bash-4.2$ touch $PGDATA/standby.signal

現在就可以啟動了

[root@pg12-2 ~]# service postgresql-12 start

設置完畢之後，可以用 SHOW 指令查看這些參數了：在 PGSQL 12 之前，recovery.conf 的參數是無法用 SQL 指令查到的。
而且，在 PGSQL 12 也可以用 ALTER SYSTEM SET 的方式調整同步相關的設定，並且可以透過重新載入生效。

[root@pg12-2 ~]# su - postgres
-bash-4.2$ psql
Timing is on.
Null display is "(NULL)".
psql (12.1)
Type "help" for help.

postgres=# SHOW restore_command;
                                   restore_command                                   
-------------------------------------------------------------------------------------
 ssh pg12-primary "cat /var/lib/pgsql/12/backups/wal_archives/%f.gz" | gunzip - > %p
(1 row)

Time: 120.309 ms
postgres=# SHOW primary_conninfo;
                                                                           primary_conninfo                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 user=repuser passfile='/var/lib/pgsql/.pgpass' host='pg12-1' port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
(1 row)

Time: 0.287 ms
postgres=#

最後，在 Master DB 裡面查看一下同步檢查表

[root@pg12-1 ~]# su - postgres
-bash-4.2$ psql
Timing is on.
Null display is "(NULL)".
psql (12.1)
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 988
usesysid         | 16384
usename          | repuser
application_name | walreceiver
client_addr      | 10.207.69.233
client_hostname  | pg12-2
client_port      | 41202
backend_start    | 2019-11-30 08:11:09.658517+00
backend_xmin     | (NULL)
state            | streaming
sent_lsn         | 0/28000148
write_lsn        | 0/28000148
flush_lsn        | 0/28000148
replay_lsn       | 0/28000148
write_lag        | (NULL)
flush_lag        | (NULL)
replay_lag       | (NULL)
sync_priority    | 0
sync_state       | async
reply_time       | 2019-11-30 08:15:20.25755+00

Time: 511.419 ms
postgres=#

大功告成。

基本上，這些步驟跟以前的設定順序是大同小異的：只是隨著版本的演進，一些設定的操作變得比較簡化，更容易做成腳本處理了～

參考資料

我的第一篇筆記

Waiting for PostgreSQL 12 – Integrate recovery.conf into postgresql.conf – select * from depesz;

PostgreSQL 12 preview - recovery.conf disappears | pgstef’s blog

Postgres 12 highlight - pg_promote | Michael Paquier - PostgreSQL committer

PostgreSQL recovery.conf 配置文件整合到 postgresql.conf-云栖社区-阿里云

PostgreSQL 12 新特性汇总-云栖社区-阿里云

PostgreSQL 流复制的主备切换-云栖社区-阿里云

https://www.postgresql.org/docs/12/release-12.html

PostgreSQL 12 beta 4 up and running in less than six minutes – Luca Ferrari – Open Source advocate, human being

Postgres Rocks! Powered by EDB - Where is my recovery.conf file in PostgreSQL v12? - Postgres Rocks - 4360