docker部暑postgres高可用集群(没完成)_docker 基于repmgr构建postgresql高可用集群

参考：
https://blog.csdn.net/weixin_39724266/article/details/111291023

一、部署架构

二、 部暑

• master节点（pg-0）

mkdir /data/postgres/data -p
chown 1001.root /data/postgres/data

cat > /data/postgres/start.sh << 'EOF'
docker run -d \
--restart=always \
--name pg-0 \
--network host \
--hostname pg-0 \
-e REPMGR_PARTNER_NODES=pg-0,pg-1,pg-2 \
-e REPMGR_NODE_NAME=pg-0 \
-e REPMGR_NODE_NETWORK_NAME=pg-0 \
-e REPMGR_PRIMARY_HOST=pg-0 \
-e REPMGR_PASSWORD=Repmgrpass2O21 \
-e POSTGRESQL_PASSWORD=Postgres2O21 \
-v /data/postgres/data:/bitnami/postgresql \
-v /etc/localtime:/etc/localtime \
--add-host=pg-0:192.168.11.100 \
--add-host=pg-1:192.168.11.101 \
--add-host=pg-2:192.168.11.102 \
bitnami/postgresql-repmgr:11.13.0

EOF

bash /data/postgres/start.sh
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

• slave节点（pg-1）

mkdir /data/postgres/data -p
chown 1001.root /data/postgres/data

cat > /data/postgres/start.sh << 'EOF'
docker run -d \
--restart=always \
--name pg-1 \
--network host \
--hostname pg-1 \
-e REPMGR_PARTNER_NODES=pg-0,pg-1,pg-2 \
-e REPMGR_NODE_NAME=pg-1 \
-e REPMGR_NODE_NETWORK_NAME=pg-1 \
-e REPMGR_PRIMARY_HOST=pg-0 \
-e REPMGR_PASSWORD=Repmgrpass2O21 \
-e POSTGRESQL_PASSWORD=Postgres2O21 \
-v /data/postgres/data:/bitnami/postgresql \
-v /etc/localtime:/etc/localtime \
--add-host=pg-0:192.168.11.100 \
--add-host=pg-1:192.168.11.101 \
--add-host=pg-2:192.168.11.102 \
bitnami/postgresql-repmgr:11.13.0

EOF

bash /data/postgres/start.sh
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

• slave节点（pg-1）

mkdir /data/postgres/data -p
chown 1001.root /data/postgres/data

cat > /data/postgres/start.sh << 'EOF'
docker run -d \
--restart=always \
--name pg-2 \
--network host \
--hostname pg-2 \
-e REPMGR_PARTNER_NODES=pg-0,pg-1,pg-2 \
-e REPMGR_NODE_NAME=pg-2 \
-e REPMGR_NODE_NETWORK_NAME=pg-2 \
-e REPMGR_PRIMARY_HOST=pg-0 \
-e REPMGR_PASSWORD=Repmgrpass2O21 \
-e POSTGRESQL_PASSWORD=Postgres2O21 \
-v /data/postgres/data:/bitnami/postgresql \
-v /etc/localtime:/etc/localtime \
--add-host=pg-0:192.168.11.100 \
--add-host=pg-1:192.168.11.101 \
--add-host=pg-2:192.168.11.102 \
bitnami/postgresql-repmgr:11.13.0

EOF

bash /data/postgres/start.sh
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

三、验证

docker exec -it  -u root pg-1 bash
gosu gnats bash
cp /opt/bitnami/repmgr/conf/repmgr.conf /tmp 

/opt/bitnami/repmgr/bin/repmgr -f /opt/bitnami/repmgr/conf/repmgr.conf cluster show

 ID   | Name | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                                      
------+------+---------+-----------+----------+----------+----------+----------+-----------------------------------------------------------------------------------------
 1000 | pg-0 | primary | * running |          | default  | 100      | 1        | user=repmgr password=Repmgrpass2O21 host=pg-0 dbname=repmgr port=5432 connect_timeout=5
 1001 | pg-1 | standby |   running | pg-0     | default  | 100      | 1        | user=repmgr password=Repmgrpass2O21 host=pg-1 dbname=repmgr port=5432 connect_timeout=5
1
2
3
4
5
6
7
8
9
10

主库

docker exec -it  -u root pg-0 bash
gosu 1001 bash
psql -U postgres

select * from pg_stat_replication;

 pid | usesysid | usename | application_name |  client_addr   | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state 
-----+----------+---------+------------------+----------------+-----------------+-------------+------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------
 794 |    16384 | repmgr  | pg-0             | 192.168.11.192 |                 |       51368 | 2021-08-19 08:01:45.569731+00 |              | streaming | 0/9001508 | 0/9001508 | 0/9001508 | 0/9001508  |           |           |            |             0 | async
(1 row)
1
2
3
4
5
6
7
8
9
10

从库

docker exec -it  -u root pg-1 bash
gosu 1001 bash
psql -U postgres

select * from pg_stat_wal_receiver;

 pid |  status   | receive_start_lsn | receive_start_tli | received_lsn | received_tli |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_end_time        |    slot_name     | sender_host | sender_port |                                                                                                            conninfo                                                                                                             
-----+-----------+-------------------+-------------------+--------------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------+------------------+-------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 248 | streaming | 0/63000000        |                 3 | 0/63002458   |            3 | 2021-08-19 08:27:49.444632+00 | 2021-08-19 08:27:49.058483+00 | 0/63002458     | 2021-08-19 08:23:49.242758+00 | repmgr_slot_1001 | pg-0        |        5432 | user=repmgr password=******** connect_timeout=5 dbname=replication host=pg-0 port=5432 application_name=pg-1 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any
(1 row)
1
2
3
4
5
6
7
8
9
10

四、主从切换（末成功）

docker exec -it  -u root pg-1 bash
useradd postgres -u 1001
gosu 1001 bash
repmgr standby switchover -f /tmp/repmgr.conf  --siblings-follow
1
2
3
4

/opt/bitnami/repmgr/bin/repmgr -f /tmp/repmgr.conf cluster show
/opt/bitnami/repmgr/bin/repmgr -f /tmp/repmgr.conf cluster crosscheck
/opt/bitnami/repmgr/bin/repmgr -f /tmp/repmgr.conf cluster event
/opt/bitnami/repmgr/bin/repmgr -f /tmp/repmgr.conf cluster event --event=repmgrd_start
/opt/bitnami/repmgr/bin/repmgr -f /tmp/repmgr.conf standby promote --verbose
1
2
3
4
5

查看服务状态

/opt/bitnami/repmgr/bin/repmgr -f /tmp/repmgr.conf  service status

 ID | Name | Role    | Status    | Upstream | repmgrd | PID | Paused? | Upstream last seen
----+------+---------+-----------+----------+---------+-----+---------+--------------------
 1000 | pg-0 | primary | * running |          | running | 1   | no      | n/a                
 1001 | pg-1 | standby |   running | pg-0     | running | 1   | no      | 0 second(s) ago  
1
2
3
4
5
6

4、postgres备份与恢复

mkdir -p /data/postgres_backup/auto_backup

cat > /data/postgres_backup/backup.sh << 'EOF'
#!/bin/bash
#########mysql backup####################
db_user="postgres" 
db_passwd="Postgres2O21" 
db_host="192.168.11.192" 
db_port="5432"
PG_DUMP="docker exec -i -e PGPASSWORD=$db_passwd pg-0 pg_dump " 
PG_DUMPALL="docker exec -i -e PGPASSWORD=$db_passwd pg-0 pg_dumpall " 

##########code & res backup####################
backup_dir="/data/postgres_backup/auto_backup"
time="$(date +"%d-%m-%Y")" 
MKDIR="/bin/mkdir" 
RM="/bin/rm" 
MV="/bin/mv" 
GZIP="/bin/gzip" 

# the directory for story the newest backup 
test ! -d "$backup_dir/backup.0/" && $MKDIR -p "$backup_dir/backup.0/" 

# check the directory for store backup is writeable 
test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0 

backup()
{
# get all databases 
databases=(repmgr)

for db in ${databases[*]}
  do
    #备份指定库
    $PG_DUMP  -U $db_user -h $db_host -p $db_port -c -C  $db | $GZIP -9 > "$backup_dir/backup.0/$time.$db.gz"   
done     
}

backup_all()
{
 $PG_DUMPALL  -U $db_user -h $db_host -p $db_port  | $GZIP -9 > "$backup_dir/backup.0/postgres_$time.gz"   
}

#备份所有数据库
backup_all
#backup
####滚动清理历史备份
test -d "$backup_dir/backup.5/" && $RM -rf "$backup_dir/backup.5" 
for int in 4 3 2 1 0 
do 
if(test -d "$backup_dir"/backup."$int") 
then 
next_int=`expr $int + 1` 
$MV "$backup_dir"/backup."$int" "$backup_dir"/backup."$next_int" 
fi 
done 

exit 0;
EOF
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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

• 恢复

#!/bin/bash
#########postgres backup####################
db_user="postgres" 
db_passwd="Postgres2O21" 
db_host="192.168.11.192" 
db_port="5432"
PSQL="docker exec -i -e PGPASSWORD=$db_passwd pg-0 psql" 

##########code & res backup####################
backup_dir="/data/postgres_backup/auto_backup"
time="$(date +"%d-%m-%Y")" 
MKDIR="/bin/mkdir" 
RM="/bin/rm" 
MV="/bin/mv" 
GZIP="/bin/gzip" 

restore_all()
{
 zcat $backup_dir/backup.1/postgres*.gz|$PSQL  -U $db_user -h $db_host  -p $db_port
}

restore_all

exit 0;
EOF
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