How does pglogical-2 handle logical replication on same table while allowing it to be writeable on both databases?

Question

Based on the above image, there are certain tables I want to be in the Internal Database (right hand side). The other tables I want to be replicated in the external database.

In reality there's only one set of values that SHOULD NOT be replicated across. The rest of the database can be replicated. Basically the actual price columns in the prices table cannot be replicated across. It should stay within the internal database.

Because the vendors are external to the network, they have no access to the internal app.

My plan is to create a replicated version of the same app and allow vendors to submit quotations and picking items.

Let's say the replicated tables are at least quotations and quotation_line_items. These tables should be writeable (in terms of data for INSERTs, UPDATEs, and DELETEs) at both the external database and the internal database. Hence at both databases, the data in the quotations and quotation_line_items table are writeable and should be replicated across in both directions.

The data in the other tables are going to be replicated in a single direction (from internal to external) except for the actual raw prices columns in the prices table.

The quotation_line_items table will have a price_id column. However, the raw price values in the prices table should not appear in the external database.

Ultimately, I want the data to be consistent for the replicated tables on both databases. I am okay with synchronous replication, so a bit of delay (say, a couple of second for the write operations) is fine.

I came across pglogical https://github.com/2ndQuadrant/pglogical/tree/REL2_x_STABLE

and they have the concept of PUBLISHER and SUBSCRIBER.

I cannot tell based on the readme which one would be acting as publisher and subscriber and how to configure it for my situation.

Answer 1

That won't work. With the setup you are dreaming of, you will necessarily end up with replication conflicts.

How do you want to prevent that data are modified in a conflicting fashion in the two databases? If you say that that won't happen, think again.

I believe that you would be much better off using a single database with two users: one that can access the “secret” table and one that cannot.

If you want to restrict access only to certain columns, use a view. Simple views are updateable in PostgreSQL.

Answer 2

It is possible with BDR replication which uses pglogical. On a basic level by allocating ranges of key ids to each node so writes are possible in both locations without conflict. However BDR is now a commercial paid for product.