如何在副本服务器postgresql-13中启用写入选项?

vc9ivgsu  于 2023-02-08  发布在  PostgreSQL
关注(0)|答案(1)|浏览(120)

我有2个数据库服务器:10.1.1.49- master和10.1.1.68- slave。第一个是生产服务器,第二个是开发服务器。我需要从生产服务器复制数据到开发服务器,我还需要能够写入一些数据到开发服务器。
当我将这些服务器配置为主从复制成功时。但是当我在开发服务器www.example.com中关闭只读选项并启用读写选项时10.1.1.49,复制不再工作。您能帮助解决此问题吗?我的配置如下。
在主10.1.1.49(产品)服务器中:

  • pg_hba.配置文件:
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             10.1.1.0/24             md5
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host    replication     postgres        10.1.1.68/24            md5
  • postgresql.conf:
listen_addresses = '*'
wal_level = hot_standby
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 8
hot_standby = on

在从10.1.1.68服务器(开发人员)中

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             10.1.1.0/24             md5
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                    peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host    replication     postgres        10.1.1.49/24            md5
  • postgresql.conf:
listen_addresses = '*'
wal_level = hot_standby
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 8
hot_standby = on

将数据从主服务器复制到从服务器时执行的命令:在从属服务器中:

rm -rf data; mkdir data; chmod go-rwx data
pg_basebackup -P -R -X stream -c fast -h 10.1.1.49 -U postgres -D ./data
ubbxdtey

ubbxdtey1#

您无法写入流式复制备用服务器。您需要的是logical replication

相关问题