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

3•polishdude20•1y 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•1y 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•1y 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•1y 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•1y 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•1y ago

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

Show HN: Nodrix – open-source IoT cloud that runs in your own Cloudflare account

Ethereum can quantum-proof accounts for just 7 cents, says Kohaku project leader

Teachers of Reddit: Is the "Gen Alpha can't read" crisis real?

Qed: A verified web front end written in Lean4

Claude Code is dead, the future is open

Show HN: Replicant Detector with Datastar, Common Lisp, BKNR Datastore

Show HN: Solaris the Thinking Ocean Simulator

Show HN: cuSBF – faster Bloom filter on GPUs for DNA sequences

'It's a hurricane warning': Guardrails around powerful AI models may be too late

Ask HN: How do I do marketing for an app I made

Setting Node and PNPM Versions in Cloudflare Workers Programmatically

List of RL Env and Human Data Startups

Farside: Alternative front ends to many sites

Kronotop: Distributed, transactional document database on FoundationDB

Show HN: RAIF – an experimental structured I/O format for LLMs

Abu Fanous

Zinnia: A modular 64-bit Unix-like kernel written in Rust

Brains And LLMs Converge On A Shared Conceptual Space Across Different Languages

Proteus the Infinite Fractal Object

The Visa Empire: Borders as a Business

Angry Meta employee hijacks company presentation unleash profanity-laced tirade

More Expensive, Somehow Worse [video]

Hillock – Local, brain-inspired AI memory using SQLite and HDC

Fermat's last theorem in the natural ring of ordinals

Show HN: Buildtopia1 – A sandbox game where an AI builds for you with chat

SillyTavern provides a single unified interface for many LLM APIs

Multimodal Neurons in Artificial Neural Networks

Changing Careers, Cutting Expenses: Software Engineers Contend with AI

RhinoCollab a plugin for real-time editing for Rhino 3D

The APLR(1) algorithm for compact LR(1) parsers is simpler and more capable than