Timescaledb: BDR compatibility

[Kanshiroron]

Hello,

Has someone already tried to use TimescaleDB along with BDR (https://www.2ndquadrant.com/en/resources/bdr/)?

Thanks for your feedback!

[cevian]

We aren't aware of any testing of TimescaleDB and BDR. Btw, which version of BDR do you want to use?

[cevian]

Also, do you mind describing the use-case?

[Kanshiroron]

Hello @cevian
Thanks for your answer.

About the version we are planning to use, since it'll be a new install we will be using BDR 2.0 on Postgresql 9.6 (BDR doesn't seem to be compatible with PSQL 10 yet).

Our use case is the following: we are planning to deploy a monitoring system on AWS, multi-AZ, based on the following stack: Prometheus and TimescaleDB on PSQL. Also, we're aiming at being highly available, hence the need to have a sync db. BDR seemed to us to be the only viable solution.

The issue is that we don't know how the sync would work with hypertables and other modifications you made on PostgreSQL. I asked the same question on their side, I'll get back to you as soon as I received an answer.

Thanks for your help

[Kanshiroron]

Just got an answer, nobody on 2ndquadrant side is aware of any testing either.

[fasmit]

I managed to patch 2ndquadrant's pg_logical to be able to logically replicate timescaledb tables. I imagine the same wouldwork for BDR. The problem is that logical WAL will receive changes on hyper chunk partitions instead of the parent hypertable, so whenever pglogical logically decodes the WAL I scan if it is a table in the _timescale_internal schema, and if so I use the pgcatalog to map the name to the parent table. Then on the receiver it will simply be inserted into the parent and properly assigned a chunk automatically. These problems are all similar to why pg10 partitioning and logical replication don't work either: it's a mapping problem from inheritance/partition child to parent and/or relation type.

Oh, and you'd have to make some changes to the COPY bulk read sync as the parent tables are empty and a COPY table would not return any data, so you want to rewrite it as COPY (SELECT ...) TO in order for the individual chunks to be read.

[komorebi-san]

@fasmit I am new to both pglogical and timescaledb. Can you please elaborate further?

Sorry for the lengthy question.

so whenever pglogical logically decodes the WAL I scan if it is a table in the _timescale_internal schema, and if so I use the pgcatalog to map the name to the parent table.

Oh, and you'd have to make some changes to the COPY bulk read sync as the parent tables are empty and a COPY table would not return any data, so you want to rewrite it as COPY (SELECT ...) TO in order for the individual chunks to be read.

1. How do you "scan" if its _timescale_internal_schema
2. How do you use "pgcatalog" to map to the parent table? (the parent table refer to the source table?)
3. Lastly, where do you make the changes to the COPY bulk read?

Thanks!

[fasmit]

Hi,

You have to do these things in C code, then recompile and install the
extension. Its pretty low level. If thats within possibilities i can send
you the snippet of code

On Mon, 30 Jul 2018, 12:47 Komorebi, [e-mail] wrote:

@fasmit https://github.com/fasmit I am new to both pglogical and
timescaledb. Can you please elaborate further?

Sorry for the lengthy question.

so whenever pglogical logically decodes the WAL I scan if it is a table in
the _timescale_internal schema, and if so I use the pgcatalog to map the
name to the parent table.

Oh, and you'd have to make some changes to the COPY bulk read sync as the
parent tables are empty and a COPY table would not return any data, so you
want to rewrite it as COPY (SELECT ...) TO in order for the individual
chunks to be read.

1. How do you "scan" if its _timescale_internal_schema
2. How do you use "pgcatalog" to map to the parent table? (the parent
   table refer to the source table?)
3. Lastly, where do you make the changes to the COPY bulk read?

Thanks!

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/timescale/timescaledb/issues/483#issuecomment-408823317,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AkXVX4s4BRYTa0DktcLxVNx4CYZ0yU3Qks5uLuRMgaJpZM4S7DN9
.

[komorebi-san]

@fasmit

Ah I see. Its okay then. I am not comfortable with doing the low level code as I am not familiar with the extension itself well enough.

WIll try to look for an alternative to achieve something similar.

[nekowithscythe]

@fasmit

We are having similiar issues with pglogical and timescaledb.

We can replicate normal tables just fine. The moment we upgrade the table to a hypertable, it stops working.

We have read your workaround with interest. It sounds like a workable solution to the problem we are facing.

In addition to a patched pglogical, did you have to set any configuration? like use_spi true or similiar?

If you don't mind, can you share with us your patch too?

Many Thanks!

[fasmit]

Hi, yes indeed, I have spi turned on. Dont mind sharing the small code
patch, except im on holidays for a week. Please remind me after. It does
work, we've been running it in prepod with a dozen ts tables and some tbs
data.

On Mon, 6 Aug 2018, 11:13 nekowithscythe, [e-mail] wrote:

@fasmit https://github.com/fasmit

We are having similiar issues with pglogical and timescaledb.

We can replicate normal tables just fine. The moment we upgrade the table
to a hypertable, it stops working.

We have read your workaround with interest. It sounds like a workable
solution to the problem we are facing.

In addition to a patched pglogical, did you have to set any configuration?
like use_spi true or similiar?

If you don't mind, can you share with us your patch too?

Many Thanks!

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/timescale/timescaledb/issues/483#issuecomment-410642311,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AkXVXzI2yfiaB9M5r-mVPPQ2PbPwMAJbks5uOAi2gaJpZM4S7DN9
.

[nekowithscythe]

@fasmit

Roger that. Enjoy your hols~! =)

[esatterwhite]

I would would love to see this for BDR3 / postgres 10

[nekowithscythe]

@fasmit

Hopefully it was a great holiday for you =)

Can we have the patch, and also the configuration options that makes it work?

Thanks~!

[nekowithscythe]

@fasmit

Been a while, everything ok over there? =)