2

I've been reading about logical replication in PostgreSQL, which seems to be a very good solution for sharing a small number of tables among several databases. My case is even simpler, as my subscribers will only use source tables in a read-only fashion.

I know that I can add extra columns to a subscribed table in the subscribing node, but what if I only want to import a subset of the whole set of columns of a source table? Is it possible or will it throw an error?

For example, my source table product, has a lot of columns, many of them irrelevant to my subscriber databases. Would it be feasible to create replicas of product with only the really needed columns at each subscriber?

coterobarros
  • 941
  • 1
  • 16
  • 25
  • 1
    Selective column replication is supported from Postgres v15 (currently in beta) while using logical replication. But how you added additional columns while subscribing? Probably you mean after creation of publisher, subscriber? Because as far as i know custom expressions are not yet supported. – Stavros Koureas Jun 06 '22 at 13:10
  • Yeah a case as `CREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);` is what I was looking for. https://www.postgresql.org/docs/15/sql-createpublication.html – coterobarros Jun 06 '22 at 13:42
  • 1
    Correct, this can be done now, but the question is, is the following supported? CREATE PUBLICATION users_filtered FOR TABLE users (1 as tenand_id, user_id, firstname); you can check further here https://stackoverflow.com/questions/72518395/postgresql-logical-replication-custom-column-expression – Stavros Koureas Jun 06 '22 at 13:44
  • With regard to your first question (how you added additional columns while subscribing?) the answer is: when creating the subscribing table at the subscriber database. You MUST include all columns from the publicated table (until v15) but you can add extra columns to the subscribing table. – coterobarros Jun 06 '22 at 13:46

2 Answers2

3

The built in publication/subscription method does not support this. But the logical replication framework also supports any other decoding plugin you can write (or get someone else to write) and install, so you could make this happen that way. It looks like pglogical already supports this ("Selective replication of table columns at publisher side", but I have never tried to use this feature myself).

jjanes
  • 37,812
  • 5
  • 27
  • 34
2

As of v15, PostgreSQL supports publishing a table partially, indicating which columns must be replicated out of the whole list of columns.

A case like this can be done now:

CREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);

See https://www.postgresql.org/docs/15/sql-createpublication.html

coterobarros
  • 941
  • 1
  • 16
  • 25