PostgreSQL-12主从异步流复制

原创

insist_way 2024-01-19 09:51:09 博主文章分类：PostgreSQL ©著作权

文章标签 PostgreSQL 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者insist_way的原创作品，谢绝转载，否则将追究法律责任

Tips：

内容仅供参考

主库(10.100.3.129)修改postgresql.conf配置

cat postgresql.conf

listen_addresses = '*'

wal_level = replica

max_wal_senders = 5

wal_keep_segments = 128

wal_sender_timeout = 60s

max_connections = 200

hot_standby = on

max_standby_streaming_delay = 30s

wal_receiver_status_interval = 10s

hot_standby_feedback = on

wal_log_hints = on

主库(10.100.3.129)修改pg_hba.conf配置

host replication replica 10.100.3.128/32 md5

主库创建流复制用户

create role replica with replication login password '123456';

alter user replica with password '123456';

需要重启主库

从库(10.100.3.128)修改postgresql.conf配置

listen_addresses = '*'

primary_conninfo = '10.100.3.129 port=5432 user=replica password=123456'

recovery_target_timeline = latest

max_connections = 300

hot_standby = on

max_standby_streaming_delay = 30s

wal_receiver_status_interval = 10s

hot_standby_feedback = on

max_wal_senders = 15

从库(10.100.3.128)修改pg_hba.conf配置

host replication replica 10.100.3.129/32 md5

从库基本备份

/usr/pgsql-12/bin/pg_basebackup -h 10.100.3.129 -p 5432 -U replica -Fp -Xs -Pv -R -D /pgsqldata

变更PostgreSQL数据目录权限

chown postgres.postgres /pgsqldata/ -R

从库无法启动(pg_hba.conf配置错误)

2023-10-09 23:40:53.622 EDT [2629] FATAL: no pg_hba.conf entry for host "10.100.3.128", user "replica", database "postgres", SSL off

从库无法启动(postgresql.conf max_connections参数值配置错误)

2023-10-09 23:49:10.982 EDT [2728] FATAL: hot standby is not possible because max_connections = 100 is a lower setting than on the master server (its value was 200)

从库日志报错

2023-10-10 02:13:30.461 EDT [2976] FATAL: could not load library "/usr/pgsql-12/lib/libpqwalreceiver.so": /usr/pgsql-12/lib/libpq.so.5: symbol X509_get_signature_nid, version libcrypto.so.10 not defined in file libcrypto.so.10 with link time reference

openssl version openssl版本太低导致

yum -y install openssl 解决

转载参考：

symbol X509_get_signature_nid, version libcrypto.so.10 not defined_mob60475707634e的技术博客_51CTO博客

从库只有只读权限

postgres=# insert into t01 values(5);

ERROR: cannot execute INSERT in a read-only transaction

其他转载参考：

PostgreSQL 流复制配置环境搭建过程_PostgreSQL_脚本之家 (jb51.net)

Postgresql 流复制_postgresql流复制-CSDN博客

postgresql12主从异步流复制搭建_未既的博客-CSDN博客

postgresql|数据库|【postgresql-12的基于pg_basebackup的主从复制部署】_postgresql12 主备_晚风_END的博客-CSDN博客