I have database and table collection_city
on one server. It has 21 rows. I have database and table collection_city
on a second server. It also has 21 rows.
They both have this row:
tinker=# table collection_city;
id | name | alias | postal_code | region_id
----+---------------+--------------+-------------+-----------
2 | Obrenovac | obrenovac | | 1
The id
column is the primary key.
I created a publication on the first server:
CREATE PUBLICATION tinkerpub FOR ALL TABLES;
tinker=# \dRp[+]
Publication tinkerpub
Owner | All tables | Inserts | Updates | Deletes | Truncates
----------+------------+---------+---------+---------+-----------
postgres | t | t | t | t | t
(1 row)
I created a subscription on the second server:
CREATE SUBSCRIPTION tinkersub CONNECTION 'dbname=tinker host=192.168.150.5 user=postgres password=test port=5432' PUBLICATION tinkerpub WITH (copy_data = false);
My publication-subscription process works correctly, but I read somewhere that the slave server in that case should be read only for manual updates.
But if I run an INSERT
command for the table collection_city
only on the subscription server, I can see that a new row is being added in the table on the subscription server, although of course it does not exist on the publication server.
Is this the correct behavior? Shouldn't the subscription server be read-only for all modifications except from the publication server?
I am of course aware that the publication server should be able to perform insert
, update
and delete
statements on the subscription server, but is it also possible to do manual INSERT
statements on the subscription server? Shouldn't it be disabled in some way?
Best Answer
With PostgreSQL logical replication, the table on the standby server (where the subscription is) is available for data modifications. This is different from physical replication, where the standby is a physical copy of the primary and cannot be modified directly. Perhaps your confusion originates there.
While you can modify the table on the standby, it is not necessarily a good idea to do that, because it can lead to replication conflicts. But it is in your own responsibility to not perform any such operations.