PostgreSQL Logical Replication Trigger to add destination key column

Question

I am using PostgreSQL Logical Replication to replicate data from multiple source databases into single one for analytical purposes.

Data from the source database have the same schema and therefore primary keys can overlap.

Is there any way to add an additional column with predefined value per subscription participating into primary key of the destination in order to be able to dinstinct between the records of each source?

Is this possible using triggers, and how?

• user1822: I think you will need to add that column to the source tables
• Stavros Koureas: @a_horse_with_no_name, I can not add this column to source tables because I cannot touch ERP production tables, in addition even I could, there are thousands of tables and hundreds of databases which may be participate into replication (not all together). At last I cannot request this as a change in blueprint as this is against database normilization.
• Laurenz Albe: I thought about it, and I don't think there is a way to do that except with an additional column in the source table.
• Stavros Koureas: @LaurenzAlbe, i also thought about this much time, and even with triggers you can not know the subscription in order to know the tenant/database identification number, or even worse on updates I am not sure how we can change the where predicate to base the where statement based on old where statement plus this additional column.

Answer 1

After a lot investigation seems that there is no way (at least in this year) that could make an additional column exist into destination and participating into primary keys without being into source table. Same applies for Native Logical Replication and PGLogical Extension.

But I have opened this idea on both for including source origin is sent to:

1. PostgreSQL mailing list
2. PGLogical Extension on Github