How to enable writing option in replica server postgresql-13?

Question

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

Laurenz Albe · Accepted Answer · 2023-02-06 07:50:51Z

1

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

answered Feb 6, 2023 at 7:50

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jasur Abdunabiyev Over a year ago

is it possible when using logical replication to prevent overwriting data in slave server?

Laurenz Albe Over a year ago

You can use privileges to control that. If you don't allow anybody to modify the data, nobody can overwrite them.

Collectives™ on Stack Overflow

How to enable writing option in replica server postgresql-13?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related