Thingworx高可用集群部署（三）-PG数据库安装

流复制配置

用户：postgres

创建用户-主

sudo -u postgres psql
postgres=# alter role postgres with password 'Postgres@2024_PG';
postgres=# create role repuser login replication encrypted password 'Repuser@2024_RP';
\q

创建归档目录-all

所有节点

mkdir /data/{pg_arch,pg_scripts}

cat >  /data/pg_scripts/clean_arch.sh <<"EOF"
test ! -f /data/pg_arch/$1 && cp --preserve=timestamps $2 /data/pg_arch/$1 ; find /data/pg_arch -type f -mmin +30 -exec rm -f {} \;
EOF

chown postgres.postgres /data/pg_arch/ -R
chown postgres.postgres /data/pg_scripts/ -R

chmod 755 /data/pg_scripts/clean_arch.sh

修改参数-主

vi /data/pg_data/postgresql.conf

listen_addresses = '*'
port = 5432
max_connections = 2000 

synchronous_commit = on
synchronous_standby_names = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 204800 
wal_sender_timeout = 60s 
unix_socket_directories='/var/run/postgresql, /tmp'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_truncate_on_rotation = on
log_destination = 'csvlog'
archive_mode = on
archive_command = '/data/pg_scripts/clean_arch.sh %f %p'
max_replication_slots = 10
hot_standby = on
wal_log_hints = on
password_encryption = 'md5'

# 优化参数
shared_buffers = 8GB
dynamic_shared_memory_type = mmap
effective_cache_size = 24GB
maintenance_work_mem = 2GB
autovacuum_work_mem = 2GB
vacuum_cost_delay = 0
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 10.0
checkpoint_completion_target = 0.9
wal_buffers = 32MB
wal_writer_delay = 10ms 
commit_delay = 20
commit_siblings = 9
default_statistics_target = 100
random_page_cost = 1.1
#effective_io_concurrency = 200
work_mem = 2101kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 16
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 6s
autovacuum_vacuum_cost_delay = 0

pg_hba 设置

vi /data/pg_data/pg_hba.conf

同时更新以下行

host     all              all             0.0.0.0/0               md5
host     replication    repuser           10.13.3.0/24           md5

重启pg

systemctl restart postgresql-12
systemctl status postgresql-12

所有从库

systemctl stop postgresql-12

rm -rf /data/pg_data/*

su - postgres

pg_basebackup -h 10.10.10.71 -p 5432 -U repuser -Fp -Xs -Pv -R -D /data/pg_data
密码: Repuser@2024_RP

修改从库配置文件

vi /data/pg_data/standby.signal

加入

standby_mode = 'on'

修改postgresql.conf文件

vi /data/pg_data/postgresql.conf
或者
vi /data/pg_data/postgresql.auto.conf

recovery_target_timeline = latest
hot_standby = on
max_standby_streaming_delay = 30s 
wal_receiver_status_interval = 10s 
hot_standby_feedback = on

启动从库

systemctl start postgresql-12

查看状态-主

查看主从状态-主库

sudo -u postgres psql
postgres=# \x
postgres=# select * from pg_stat_replication;

如下图所示即配置完成

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 11393
usesysid         | 16384
usename          | repuser
application_name | walreceiver
client_addr      | 10.10.10.72
client_hostname  |
client_port      | 14970
backend_start    | 2024-01-11 14:39:06.67363+08
backend_xmin     | 488
state            | streaming
sent_lsn         | 0/4000060
write_lsn        | 0/4000060
flush_lsn        | 0/4000060
replay_lsn       | 0/4000060
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 1
sync_state       | sync
reply_time       | 2024-01-11 14:39:36.884945+08
-[ RECORD 2 ]----+------------------------------
pid              | 11400
usesysid         | 16384
usename          | repuser
application_name | walreceiver
client_addr      | 10.10.10.73
client_hostname  |
client_port      | 32489
backend_start    | 2024-01-11 14:39:12.95273+08
backend_xmin     | 488
state            | streaming
sent_lsn         | 0/4000060
write_lsn        | 0/4000060
flush_lsn        | 0/4000060
replay_lsn       | 0/4000060
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 1
sync_state       | potential
reply_time       | 2024-01-11 14:39:33.032133+08