英文:
How to enable writing option in replica server postgresql-13?
问题
在主数据库服务器（10.1.1.49 - prod）中，你的pg_hba.conf文件如下：
# 类型 数据库 用户 地址 认证方法
# "local" 仅用于Unix域套接字连接
local all all peer
# IPv4本地连接：
host all all 127.0.0.1/32 scram-sha-256
host all all 10.1.1.0/24 md5
# IPv6本地连接：
host all all ::1/128 scram-sha-256
# 允许来自localhost的复制连接，由具有复制权限的用户。
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
主数据库服务器（10.1.1.49 - prod）的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 - dev）中，你的pg_hba.conf文件如下：
# 类型 数据库 用户 地址 认证方法
# "local" 仅用于Unix域套接字连接
local all all peer
# IPv4本地连接：
host all all 127.0.0.1/32 scram-sha-256
host all all 10.1.1.0/24 md5
# IPv6本地连接：
host all all ::1/128 scram-sha-256
# 允许来自localhost的复制连接，由具有复制权限的用户。
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
从数据库服务器（10.1.1.68 - dev）的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
如果你在从服务器上禁用读取选项并启用读写选项后，复制不再工作，请检查以下方面：
-
确保从服务器（10.1.1.68 - dev）上的
pg_hba.conf和postgresql.conf配置与提供的配置匹配。 -
确保主服务器（10.1.1.49 - prod）和从服务器（10.1.1.68 - dev）之间的网络连接正常，没有任何防火墙或网络配置阻止数据流量。
-
检查从服务器的日志文件，以查看是否有与复制问题相关的错误消息。可以使用
pg_stat_replication来监视复制状态。
如果你仍然遇到问题，可以提供更多关于错误消息或日志文件的信息，以便我提供更具体的帮助。
英文:
I have 2 DB servers: 10.1.1.49 - master and 10.1.1.68 - slave. First is prod and the second one is dev server respectively. I need to replicate data from prod server to dev server, also I need to able to write some data to dev server as well.
When I configure these server as master-slave replication is successful. But when I am switching off only read option and enabling read-write option in dev server 10.1.1.49, replication doesn't work anymore. Can you help with this issue? My configurations are below.
In master 10.1.1.49 (prod) server:
- pg_hba.conf:
# 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
In slave 10.1.1.68 server (dev)
# 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
executed commands when copying data from master to slave server:
in slave server:
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
答案1
得分: 1
你可以写入流复制的备用服务器。你需要的是逻辑复制。
英文:
There is no way you can write to a streaming replication standby server. What you need is logical replication.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论