Pg_flo – Easily move and transform data between PostgreSQL databases | Hacker News Reader

Pg_flo – Easily move and transform data between PostgreSQL databases

github.com

3 pointsshayonj6 comments

Hey this looks interesting!

If my setup is db-per-tenant can I push/pull all data into a single database that would have an additional 'tenant_id' column?

For sake of simplicity, let's assume all tenant dbs have the same schemas.

heya! Thanks for checking it out. Yeah, I believe you should be able to. Are you basically thinking to have multiple rules where if tenant_id=3, send to the db_3, and so on?

Here is a simple example [1] where you can setup a replication for to send data for events tables where tenant_id = `3`. You can setup multiple groups, where each group could be a tenant [2].

Would love to hear more about the use case. I can see the use case being a bit more dynamic, where you don't have to custom define ids per se (?).

[1] https://github.com/shayonj/pg_flo/blob/main/internal/multi_t... [2] https://github.com/shayonj/pg_flo/tree/main?tab=readme-ov-fi...

Thanks for taking the time to respond.

Actually, no. I want a single db to be the destination, receiving data from multiple tenant dbs.

The tenant dbs do not have 'tenant' columns, but the destination would have one to differentiate between them.

The use is being able to run reports against all tenant dbs with tools like metabase, or just plain sql, without having to access each tenant db individually.

It's more involved that what I'm describing as db constraints and foreign keys / pk uniqueness would need to be worked around, but from first glance this tool seems like a good start towards that direction.

What do you think?

Very interesting, I have wanted to solve this issue for a while and it actually overlaps with the "routing" feature for pg_flo that I am working on. Basically you can make map what data gets routed to what table and column in the destination DB, example [1].

Would be curious to get your thoughts/early impressions.

[1] https://gist.github.com/shayonj/6105a6c7dde7a093ef55674fa966...

I pushed out the v1. More deets at: https://github.com/shayonj/pg_flo/blob/main/pkg/routing/READ...

Reply on news.ycombinator.com