Logical Replication and Declarative Partitioning in PostgreSQL 11

Question

I have a regular table transactions with 200 million rows.

I decided to convert this table to a Declarative Partition using Logical Replication.

I created a Publication on node1 like so:

CREATE PUBLICATION transactions_pub FOR TABLE transactions;

When I try to create Subscription on node2 as below:

CREATE SUBSCRIPTION transactions_sub CONNECTION 'host=x.x.x.x port=5432 password=123456 user=replicator dbname=mydbname' PUBLICATION transactions_pub;

Returns this error:

ERROR:  logical replication target relation "public.transactions" is not a table

Is it possible to replicate a regular table to a Declarative Partition Table using Logical Replication?

Off

Answer 1

No, logical replication cannot replicate from and to declarative partitioned tables.

Countable

In this case, how can I reconstruct the declarative partitioned table on the logical replica (subscriber)? Is there a way I can create the main table back? – Total

You can replicate all of your data to subscriber, but it involves quite a bit of manual steps. If you want to replicate a partitioned tables, create publications and subscriptions for each partition manually. I have described an example in a note here: gitlab.com/snippets/1898229 – Countable

Answer 2

For now, logical replication only works for base tables.

See this documentation page:

Replication is only possible from base tables to base tables. That is, the tables on the publication and on the subscription side must be normal tables, not views, materialized views, partition root tables, or foreign tables. In the case of partitions, you can therefore replicate a partition hierarchy one-to-one, but you cannot currently replicate to a differently partitioned setup. Attempts to replicate tables other than base tables will result in an error.

Exactitude