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

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

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

Altair 8800 – Video #29 – Music on an Altair 8800

The Source of Water

Former ULA Chief Bruno Joins Blue Origin

How tax trackers influence wealthy Americans' holiday plans

Quantum computing in the second quantum century

Procedural Kernel (Neural) Networks (2022)

We will never fucking trust Americans again

Flock and Urban Surveillance

Filming Bullets at 20,000,000 FPS

Show HN: Open-source LLM playground for VS Code

Show HN: Me and my AI gf invented free energy from death puddles (public domain)

Reality crushed Ÿnsect startup that had raised over $600M for insect farming

Nix-prompt: a clean and modular bash prompt with just the right amount of custo

Solid-state batteries charge faster, last longer: review

Xkcd: A Universal Outlet

Perry Bamonte, guitarist and keyboardist for the Cure, dies

Exposing the Gambling Epidemic [video]

The Myth of Meritocracy on Wall Street

Read Something Wonderful (More Easily)

AI can help get fusion from lab to energy grid by the 2030s – WEF

Exe.dev/

To get better at filtering the good ideas from the bad, by paying attention

What Dan Read

Show HN: Reverse Engineering (Kind of) SQLite in Go

Ask HN: Hacker News Critical Thinking

Show HN: ISON – Data format that uses 30-70% fewer tokens than JSON for LLMs

Ask HN: Practical AI setup for staying on top of personal messages?

What's causing populism around the world? It's the Internet Stupid (Fukuyama)

Show HN: Spacelist, a TUI for Aerospace window manager

Economics of the Attention Economy