MathJax

MathJax-2

MathJax-3

Google Code Prettify

置頂入手筆記

EnterproseDB Quickstart — 快速入門筆記

由於考慮採用 EnterpriseDB 或是直接用 PostgreSQL 的人,通常需要一些入手的資料。這邊紀錄便提供相關快速上手的簡單筆記 ~ 這篇筆記以 資料庫安裝完畢後的快速使用 為目標,基本紀錄登入使用的範例:

2019年12月1日 星期日

再探 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=# 


大功告成。

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


參考資料


sed 替代第一個遇到的符合內容
sed 刪掉第一行
archive_command 的寫法
restore_command 的寫法
對 lxc exec 執行 heredoc


沒有留言:

張貼留言