跳转至

主备实例

一、安装环境

操作系统：Ubuntu 18.04.5

节点 角色
192.168.1.139 主节点
192.168.1.140 备节点

二、安装PostgreSQL

主备节点安装postgresql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
apt install -y gcc make zlib1g-dev libreadline-dev
mkdir -p /opt/package && cd /opt/package
wget https://ftp.postgresql.org/pub/source/v12.13/postgresql-12.13.tar.gz
tar xf postgresql-12.13.tar.gz
cd postgresql-12.13
mkdir build_dir && cd build_dir
../configure --prefix=/opt/software/postgresql/12.13
make && make install
adduser --disabled-password --gecos "" postgres
mkdir -p /opt/software/postgresql/12.13/data
chown postgres:postgres /opt/software/postgresql/12.13/data
cat >> /home/postgres/.bashrc << "EOF"
export PGHOME=/opt/software/postgresql/12.13
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGHOME/lib
export PGDATA=$PGHOME/data
EOF

主节点初始化数据库

1
su - postgres -c 'initdb -D $PGDATA'

主节点启动数据库

1
su - postgres -c 'pg_ctl start -D $PGDATA -l logfile'

备节点无需初始化和启动

三、创建用户、修改配置

以下操作切换到postgre用户执行

1. 主节点创建流复制用于及配置

1.1 创建流复制需要的用户

1
psql -c "create role repl login replication encrypted password 'repl@123'"

1.2 配置pg_hba.conf，添加repl用户

1
echo "host replication repl 192.168.1.142/32 trust" >> $PGDATA/pg_hba.conf

1.3 配置postgresql.conf，修改以下参数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10  # 流复制允许连接进程，主备设置必须一致
wal_keep_segments = 64

archive_mode = on
archive_command = 'test ! -f /home/postgres/archives/%f && cp %p /home/postgres/archives/%f'
restore_command = 'cp /home/postgres/archives/%f %p'
recovery_target_timeline = 'latest'
# 切换为备库后，主库的信息
primary_conninfo = 'host=192.168.1.141 port=5432 application_name=standby1 user=repl password=repl@123'
full_page_writes = on
wal_log_hints = on

hot_standby = on
ax_standby_archive_delay = 30s
wal_receiver_status_interval = 10s

1.4 重启主库

1
2
pg_ctl stop
pg_ctl start -D $PGDATA -l logfile

2. 备节点同步主节点数据并创建备库所需文件

2.1 在备库上利用pg_basebackup，将主库的数据同步到备库

1
pg_basebackup -h 192.168.1.141 -p 5432 -U repl -F p -P -D $PGDATA

2.2 配置pg_hba.conf，添加repl用户

1
sed -i  '$c\\host replication repl 192.168.1.141/32 trust'  $PGDATA/pg_hba.conf

2.3 修改postgresql.conf文件

1
2
# 连接到主库的信息
primary_conninfo = 'host=192.168.1.141 port=5432 application_name=standby1 user=repl password=repl@123'

2.4 创建备库文件standby.signal

1
touch $PGDATA/standby.signal

2.4 启动备库

1
2
chmod 750 $PGDATA
pg_ctl start -D $PGDATA -l logfile

四、验证

1. 主库验证

查询主节点进程

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
postgres@ubuntu:~$ ps -u postgres -o cmd --forest
CMD
-su
 \_ psql
-su
 \_ ps -u postgres -o cmd --forest
/opt/software/postgresql/12.13/bin/postgres -D /opt/software/postgresql/12.13/data
 \_ postgres: checkpointer   
 \_ postgres: background writer   
 \_ postgres: walwriter   
 \_ postgres: autovacuum launcher   
 \_ postgres: archiver   last was 00000001000000000000000C
 \_ postgres: stats collector   
 \_ postgres: logical replication launcher   
 \_ postgres: postgres postgres [local] idle
 \_ postgres: walsender repl 192.168.1.142(44498) streaming 0/D0132C0

查看同步状态

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
postgres=# select * from pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 13962
usesysid         | 16384
usename          | repl
application_name | standby1
client_addr      | 192.168.1.142
client_hostname  | 
client_port      | 44498
backend_start    | 2023-02-03 17:52:05.543671+00
backend_xmin     | 
state            | streaming
sent_lsn         | 0/D0132C0
write_lsn        | 0/D0132C0
flush_lsn        | 0/D0132C0
replay_lsn       | 0/D0132C0
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2023-02-03 17:58:51.520936+00

2. 备库验证

查看备节点进程

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
postgres@ubuntu:~$ ps -u postgres -o cmd --forest
CMD
-su
 \_ psql
-su
 \_ ps -u postgres -o cmd --forest
/opt/software/postgresql/12.13/bin/postgres -D /opt/software/postgresql/12.13/data
 \_ postgres: startup   recovering 00000001000000000000000D
 \_ postgres: checkpointer   
 \_ postgres: background writer   
 \_ postgres: stats collector   
 \_ postgres: walreceiver   streaming 0/D0132C0
 \_ postgres: postgres postgres [local] idle

查看同步状态

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
postgres=# select * from pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 16451
status                | streaming
receive_start_lsn     | 0/A000000
receive_start_tli     | 1
received_lsn          | 0/D0132C0
received_tli          | 1
last_msg_send_time    | 2023-02-03 17:59:51.949989+00
last_msg_receipt_time | 2023-02-03 17:59:52.008054+00
latest_end_lsn        | 0/D0132C0
latest_end_time       | 2023-02-03 17:58:51.460858+00
slot_name             | 
sender_host           | 192.168.1.141
sender_port           | 5432
conninfo              | user=repl password=******** dbname=replication host=192.168.1.141 port=5432 application_name=standby1 fallback_application_name=walreceiver sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any

五、主备切换

1. 停止主库

1
pg_ctl stop

2. 备库升主

1
pg_ctl promote

3. 主节点创建standby.signal

1
touch $PGDATA/standby.signal

4. 启动主库

1
pg_ctl start -D $PGDATA -l logfile

5. 验证结果

新主节点进程

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
/opt/software/postgresql/12.13/bin/postgres -D /opt/software/postgresql/12.13/data
 \_ postgres: checkpointer   
 \_ postgres: background writer   
 \_ postgres: stats collector   
 \_ postgres: postgres postgres [local] idle
 \_ postgres: walwriter   
 \_ postgres: autovacuum launcher   
 \_ postgres: archiver   last was 00000001000000000000000E.partial
 \_ postgres: logical replication launcher   
 \_ postgres: walsender repl 192.168.1.141(53926) streaming 0/E0132B8

新备节点进程

1
2
3
4
5
6
7
/opt/software/postgresql/12.13/bin/postgres -D /opt/software/postgresql/12.13/data
 \_ postgres: startup   recovering 00000002000000000000000E
 \_ postgres: checkpointer   
 \_ postgres: background writer   
 \_ postgres: stats collector   
 \_ postgres: postgres postgres [local] idle
 \_ postgres: walreceiver