0

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
Jasur Abdunabiyev
  • 1

1 Answers1

1

There is no way you can write to a streaming replication standby server. What you need is logical replication.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263