PG13 partitioned table logical replication

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

PG13 partitioned table logical replication

Nicolas Sornin

Dear community,

 

I just made some test of upcoming version of porstgresql, especially the ability to publish partitioned table via root partition.

I tried to setup PG13 to PG12 replication with different partitioning schemes.

 

My first attempt was to replicate time partitioned table in PG13 cluster to a regular table in PG12 cluster, but after looking at the logs I saw the following error :

 

2020-09-09 12:21:33.964 CEST [22976] LOG:  logical replication table synchronization worker for subscription "rep_part_sub", table "stock_sales" has started

2020-09-09 12:21:33.997 CEST [22976] ERROR:  table "public.stock_sales" not found on publisher

2020-09-09 12:21:33.999 CEST [20346] LOG:  background worker "logical replication worker" (PID 22976) exited with exit code 1

 

While not being aware of underlying mechanics of logical replication, I was expecting this use case to work given table names and column names/types are the same on both sides.

 

Is this the intended behavior and simply out of the scope of the changes introduced by PG13 ?

 

Thank you,

Best regards,

 

 

Nicolas SORNIN                   
IT Team Lead

 

This email message as well as all attachments are for the sole use of the intended recipient(s) and may contain confidential and privileged information.

Any unauthorized review, use, disclosure or distribution is prohibited.
If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Thank you.

 

Reply | Threaded
Open this post in threaded view
|

Re: PG13 partitioned table logical replication

Peter Eisentraut-6
On 2020-09-10 14:13, Nicolas Sornin wrote:

> I just made some test of upcoming version of porstgresql, especially the
> ability to publish partitioned table via root partition.
>
> I tried to setup PG13 to PG12 replication with different partitioning
> schemes.
>
> My first attempt was to replicate time partitioned table in PG13 cluster
> to a regular table in PG12 cluster, but after looking at the logs I saw
> the following error :
>
> 2020-09-09 12:21:33.964 CEST [22976] LOG:  logical replication table
> synchronization worker for subscription "rep_part_sub", table
> "stock_sales" has started
>
> 2020-09-09 12:21:33.997 CEST [22976] ERROR:  table "public.stock_sales"
> not found on publisher
>
> 2020-09-09 12:21:33.999 CEST [20346] LOG:  background worker "logical
> replication worker" (PID 22976) exited with exit code 1
>
> While not being aware of underlying mechanics of logical replication, I
> was expecting this use case to work given table names and column
> names/types are the same on both sides.
>
> Is this the intended behavior and simply out of the scope of the changes
> introduced by PG13 ?

When the subscriber running on PG12 is trying to fetch information about
the table from the publisher, it is looking for a real table, which it
doesn't find, because it's a partitioned table.  So this combination
doesn't work.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

RE: PG13 partitioned table logical replication

Nicolas Sornin
Hello Peter,

That's what I thought. I'll find another way.
Thank you for your time.

Regards,

Nicolas SORNIN                   
IT Team Lead


This email message as well as all attachments are for the sole use of the intended recipient(s) and may contain confidential and privileged information.
Any unauthorized review, use, disclosure or distribution is prohibited.
If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Thank you.

-----Message d'origine-----
De : Peter Eisentraut <[hidden email]>
Envoyé : jeudi 10 septembre 2020 17:04
À : Nicolas Sornin <[hidden email]>; [hidden email]
Objet : Re: PG13 partitioned table logical replication

On 2020-09-10 14:13, Nicolas Sornin wrote:

> I just made some test of upcoming version of porstgresql, especially
> the ability to publish partitioned table via root partition.
>
> I tried to setup PG13 to PG12 replication with different partitioning
> schemes.
>
> My first attempt was to replicate time partitioned table in PG13
> cluster to a regular table in PG12 cluster, but after looking at the
> logs I saw the following error :
>
> 2020-09-09 12:21:33.964 CEST [22976] LOG:  logical replication table
> synchronization worker for subscription "rep_part_sub", table
> "stock_sales" has started
>
> 2020-09-09 12:21:33.997 CEST [22976] ERROR:  table "public.stock_sales"
> not found on publisher
>
> 2020-09-09 12:21:33.999 CEST [20346] LOG:  background worker "logical
> replication worker" (PID 22976) exited with exit code 1
>
> While not being aware of underlying mechanics of logical replication,
> I was expecting this use case to work given table names and column
> names/types are the same on both sides.
>
> Is this the intended behavior and simply out of the scope of the
> changes introduced by PG13 ?

When the subscriber running on PG12 is trying to fetch information about the table from the publisher, it is looking for a real table, which it doesn't find, because it's a partitioned table.  So this combination doesn't work.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services