Ask HN: Any good tools to pgdump multi tenant database?

3•polishdude20•11mo ago

Just joined a new company and they run a multi tenant database in postgres where each tenant is distinguished by their account ID. I'd like to be able to dump the data of a single account to my local instance to assist in debugging.

Problem is, many of the entities do not include an account ID. For example, a user can have an account ID but a user post will not but it will be tied to a user ID.

Also foreign keys aren't really implemented fully.

Are there any tools or techniques that could help make this process easier to do?

Comments

tudorg•11mo ago

You can try with data subsetting tools, a few that I know are: greenmask, jailer, tonic condenser, and I think there are more. They are not exactly for your use case, but might help you a bit. The problem is most of these tools can walk foreign keys, but only in one directions.

Also, since you said FK are not fully implemented, then there just won't be enough information for them to work with. So you can either work on fixing the foreign keys or have more complex scripts.

polishdude20•11mo ago

Thanks for the suggestions!

Yeah due to the unavailability of FK's, I'd have to somehow map certain ID columns to the tables they're for, accounting for slight naming convention differences.

woyten•11mo ago

In case you don’t have FKs you could specify virtual foreign keys in Greenmask.

Check out docs for details

https://docs.greenmask.io/latest/database_subset/#virtual-re...

abhisek•11mo ago

This is poor design. Multi-tenant data model design must have a tenant or segment ID for every tenant segmented tables. While it may not be a problem initially but as the business grows, you may need to move tenant data to separate DBs or even different regions based on compliance requirements. IMHO it is a good idea to run DB migrations and have a tenant ID populated in all tenant segmented tables

polishdude20•11mo ago

Oh yes I agree with you. It may become a big push to convince my org to do that

Running Shoes Have Evolved – From Ancient Greece to Record-Breaking Marathons

Why do crabs walk sideways? Scientists trace it back 200M years

US Navy signs deal with AI firm for training underwater drones

New Netflix documentary reexamines Winnie Mandela's divisive legacy

Reaching for the stars: enduring symbols of Soviet science in pictures

I touched a ZX Spectrum for the first time in decades

The Wayfinders

How to prepare to be a startup founder (2021)

Care Homes and Hotels in Japan Shut as Expansion Strategy Unravels

Training language models to be warm can reduce accuracy and increase sycophancy

Why Nostr Will Win Where Mastodon and Bluesky Failed

A Common Proof of the Riemann Hypothesis and the Collatz Conjecture

Maryland Is First to Ban A.I.-Driven Price Increases in Grocery Stores

Treat Agent Output Like Compiler Output

Clandestine network smuggling Starlink tech into Iran to beat internet blackout

The math behind how LLMs are trained and served

ORBA: Orthogonal Reflection Bounded Ablation

Wyoming celebrates 'nuclear Renaissance' as feds approve license for a reactor

Show HN: Lisa a live dynamic wavetables synth you play and program in real-time

Virtual Scrolling

A Falcon 9 rocket will hit the Moon this summer at 7 times the speed of sound

Intel Inside the Micro Revolution: 8008 Origins

About St Helena, in the South Atlantic Ocean

Batteries: The game has changed – and it's not what you think

Class Variance Authority

Am I the only one who hates delivery robots?

Driverless cars can now get citations; autonomous trucks approved for testing CA

OpenAI's o1 correctly diagnosed 67% of ER patients vs. 50-55% by triage doctors

Show HN: A (de)compiler and parser/engine for custom Magic: The Gathering cards

Introducing Advanced Account Security