PostgreSQL透過流複製Streaming Replication可輕鬆實現高可用HA環境的搭建.本節簡單介紹了搭建流複製環境的基本步驟.

**Step 1 主庫:建立使用者**
建立複製使用者replicator
```
testdb=# CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replicator';
CREATE ROLE
```

**Step 2 主庫:引數配置**
配置archive_mode等引數
```
archive_mode = ON
wal_level = replica
max_wal_senders = 10
archive_command = '/home/xdb/archive.sh %p %f'
listen_addresses = '*'
```
也可用alter system命令修改
```
ALTER SYSTEM SET wal_level TO 'replica';
ALTER SYSTEM SET archive_mode TO 'ON';
ALTER SYSTEM SET max_wal_senders TO '10';
ALTER SYSTEM SET listen_addresses TO '*';

```
重啟資料庫
```
pg_ctl -D $PGDATA restart -mf

```

**Step 3 主庫:訪問配置**
修改pg_hba.conf檔案
```
host replication replicator 192.168.26.26/32 md5

```
生效配置
```
pg_ctl -D $PGDATA reload

```

**Step 4 從庫:從主庫備份中恢復**
在從庫上使用pg_basebackup建立備庫
192.168.26.25是主庫IP,192.168.26.26是從庫IP
```
pg_basebackup -h 192.168.26.25 -U replicator -p 5432 -D $PGDATA -P -Xs -R

```
配置從庫postgres.conf
```
hot_standby = ON
hot_standby_feedback = ON

ALTER SYSTEM SET hot_standby TO 'ON';
ALTER SYSTEM SET hot_standby_feedback TO 'ON';

```
配置從庫recovery.conf
```
$ cat $PGDATA/recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=192.168.26.25 port=5432 user=replicator password=replicator'
restore_command = 'cp /data/archivelog/%f %p'
archive_cleanup_command = 'pg_archivecleanup /data/archivelog %r'

```

**Step 5 從庫:啟動資料庫**
```
[xdb@localhost testdb]$ pg_ctl -D $PGDATA start
waiting for server to start....2019-03-13 12:13:30.239 CST [1870] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-03-13 12:13:30.239 CST [1870] LOG:  listening on IPv6 address "::", port 5432
2019-03-13 12:13:30.252 CST [1870] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-03-13 12:13:30.379 CST [1870] LOG:  redirecting log output to logging collector process
2019-03-13 12:13:30.379 CST [1870] HINT:  Future log output will appear in directory "pg_log".
 done
server started
```

**Step 6 驗證複製環境**
確認相關程式是否已啟動
```
#主庫
[xdb@localhost testdb]$ ps -ef|grep sender
xdb       1646  1532  0 12:13 ?        00:00:00 postgres: walsender replicator 192.168.26.26(35294) streaming 0/43000140
xdb       1659  1440  0 12:17 pts/1    00:00:00 grep --color=auto sender
[xdb@localhost testdb]$

#從庫
[xdb@localhost testdb]$ ps -ef|grep receiver
xdb       1879  1870  0 12:13 ?        00:00:00 postgres: walreceiver   streaming 0/43000140
xdb       1884  1799  0 12:18 pts/0    00:00:00 grep --color=auto receiver
[xdb@localhost testdb]$ ps -ef|grep startup
xdb       1872  1870  0 12:13 ?        00:00:00 postgres: startup   recovering 000000100000000000000043
xdb       1887  1799  0 12:18 pts/0    00:00:00 grep --color=auto startup
[xdb@localhost testdb]$

```

**Step 7 監控**
查詢pg_stat_replication資料字典表
```

testdb=#

```

**同步複製**
從庫配置引數recovery.conf,在primary_conninfo中新增application_name
```
primary_conninfo = 'user=replicator password=replicator host=192.168.26.25 port=5432 application_name = standby_26'

```

```

**參考資料**
[pgbasebackup](https://www.postgresql.org/docs/current/app-pgbasebackup.html)
[Setting up Streaming Replication in PostgreSQL](https://www.percona.com/blog/2018/09/07/setting-up-streaming-replication-postgresql)

PostgreSQL DBA(31) - Backup&Recovery#4(搭建流複製)

相關文章