PostgreSQL HA_postgresql-ha

两种主从复制方式log-shipping和streaming replication,以下是Streaming Replication的搭建步骤：

Replication

Master配置

Step 1 .更改用户postgres和repl用户的密码

[root@bogon ~]# su - postgres
-bash-4.2$  psql -U postgres
psql (11.6)
Type "help" for help.

postgres=# ALTER USER postgres with encrypted password 'One.00000';
ALTER ROLE
postgres=# create role repl login replication encrypted password 'One.00000';
CREATE ROLE
1
2
3
4
5
6
7
8
9

Step 2. 编辑pg_hba.conf

vim /var/lib/pgsql/11/data/pg_hba.conf

host    replication     repl            192.168.0.0/24         md5
host    all             repl            192.168.0.0/24         trust

1
2
3
4
5

Step 3. 编辑默认的postgresql配置文件postgresql.conf

vim /var/lib/pgsql/11/data/postgresql.conf 

# - Connection Settings -
listen_addresses = '*'    # what IP address(es) to listen on;
port = 5432 

# - Settings -
wal_level = replica                     # minimal, replica, or logical

# - Sending Servers -
# Set these on the master and on any standby that will send replication data.
max_wal_senders = 10        # max number of walsender processes                              
wal_keep_segments = 1024        # in logfile segments; 0 disables
wal_sender_timeout = 60s        # in milliseconds; 0 disables
1
2
3
4
5
6
7
8
9
10
11
12
13
14

Step 4. 重启服务

systemctl restart postgresql-11
1

Slave配置

Step 1. 从远程读取主节点备份

从数据库不进行初始化，只进行安装步骤，并下载主节点备份

[root@bogon ~]# pg_basebackup -h 192.168.0.169 -U repl -D /var/lib/pgsql/11/data/ -X stream -P
Password: 
24599/24599 kB (100%), 1/1 tablespace
1
2
3

Step 2. 编辑从节点postgresql.conf

编辑从节点的postgresql配置文件postgresql.conf

# - Standby Servers -

# These settings are ignored on a master server.

hot_standby = on                        # "off" disallows queries during recovery
                                        # (change requires restart)
max_standby_archive_delay = 30s # max delay before canceling queries
                                        # when reading WAL from archive;
                                        # -1 allows indefinite delay
max_standby_streaming_delay = 30s       # max delay before canceling queries
                                        # when reading streaming WAL;
                                        # -1 allows indefinite delay
wal_receiver_status_interval = 10s      # send replies at least this often
                                        # 0 disables
hot_standby_feedback = on               # send info from standby to prevent
                                        # query conflicts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Step 3. 编辑recovery.conf

$> vim /var/lib/pgsql/11/data/recovery.conf

primary_conninfo = 'host=192.168.0.169 port=5432 user=repl password=One.00000' 
recovery_target_timeline = 'latest'
standby_mode = on

1
2
3
4
5
6

Step 4. 重启服务

chown -R postgres:postgres /var/lib/pgsql/11/data
systemctl start postgresql-11
1
2

验证

1. 验证从服务器是否连接

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

1
2
3
4
5

2. 验证数据是否同步
   PostgreSQL是遵循SQL规范的，因此你可以像MySQL那样建立普通表并插入样例数据。

基于文件的日志传送

file-based log-shipping
主服务器以异步方式将WAL(write-ahead-log)发送的从服务器。WAL文件在事务提交之后才被传送，因此主从之间会有一定的数据丢失风险。

流式复制

The step that turns a file-based log-shipping standby into streaming replication standby is setting the primary_conninfo setting to point to the primary server. Set listen_addresses and authentication options (see pg_hba.conf) on the primary so that the standby server can connect to the replication pseudo-database on the primary server

Reference List

1. https://www.postgresql.org/download/linux/redhat/
2. https://www.postgresql.org/docs/11/warm-standby.html

配置优化

Reference List

1. PostgreSQL配置优化
2. PostgreSQL Documentation: Server Configuration