1

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?

Improve this question
4
  • 1
    I think you will need to add that column to the source tables
    – user1822
    Commented Feb 4, 2023 at 19:21
  • @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.
    – Stavros Koureas
    Commented Feb 6, 2023 at 7:43
  • 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.
    – Laurenz Albe
    Commented Feb 6, 2023 at 14:37
  • @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.
    – Stavros Koureas
    Commented Feb 6, 2023 at 17:30

1 Answer 1

Reset to default
0

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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