0

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?

  • No, that's the advantage of logical replication: the subscriber does not need to be read-only. If you want a read-only 1:1 copy of the primary server, use streaming replication with a hot-standby – a_horse_with_no_name Jun 26 at 9:00
  • are you referencing physical replication (use streaming replication with a hot-standby )? – Dejan Jun 26 at 10:44
1

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.

  • Thank you. Also on my question there was a comment - "use streaming replication with a hot-standby " is that physical replication? – Dejan Jun 26 at 10:45
  • Yes, that is what was meant. I avoided the term in my answer, because logical replication also streams from the primary. – Laurenz Albe Jun 26 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.