postgresql 10+ logical replication documentation

[jthornhill]

Hey folks! I've got a use case to do a logical replica of our hypertables using pgsql's native functionality (see https://www.postgresql.org/docs/10/logical-replication.html ) and I'm trying to understand whether/how this is supported on hypertables. I checked the documentation and couldn't find anything beyond the fact that it's "not recommended" (per the FAQ https://docs.timescale.com/v1.2/faq )

483 is an open issue on a similar question related to BDR, which seems to share several of the same limitations of postgres native replication, so I'm guessing this is in the same boat.

Anyway it would be great if there could be some clarification on whether this works at all and/or why it's not recommended. Thanks!

[mfreed]

Hi @jthornhill To our knowledge, PG10's logical replication will not work with hypertables, because it actually doesn't replicate DDL commands, and instead just does a pub/sub on the data.

So all the chunks (partitions) of a hypertable are postgres tables, and in fact an insert on a primary can, as part of the insert, actually create one or more chunks (tables). Except now the replica is not "subscribed" to this newly-created chunk, and so we're not actually replicating the entire hypertable.

You can see the same issues with the native "partitioned tables" in PG11: https://www.postgresql.org/docs/11/logical-replication-restrictions.html

[wrobell]

I am in a need to use PostgreSQL logical replication. As this might never be supported I had to implement my own script to synchronize tables. While the script is quite limited (copy data from source having time later than last timestamp in target), it will copy data in chunks avoiding any memory issues and within a single transaction.

You can find it in bin directory at https://gitlab.com/n23/n23/-/tree/master/bin (look for n23-db-sync). I hope anyone in similar need will find it useful.

[wrobell]

Maybe logical replication will be possible with PostgreSQL 13?

https://www.depesz.com/2020/04/17/waiting-for-postgresql-13-add-logical-replication-support-to-replicate-into-partitioned-tables/

[jflambert]

Pg13 is here with 2.1.0, what does this mean for logical replication if anything?

[stvhanna]

@mfreed Thanks for creating a great tool! Any updates to logical replications now with Postgres 14 and Timescale 2.6? Broader question: How to best stream data from Postgres to Timescale for creating more efficient analytics queries?

[jflambert]

@mfreed @svenklemm Should we create a new ticket to formally request logical replication support in timescaledb, or is this the one?