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

All code will converge on Rust

Allow-List Architecture

Solo Developer Rebuilds Adobe Creative Suite in Rust Using Claude

Show HN: ScreenCI – Tutorial videos as E2E tests, written by your coding agent

Extract charts and diagrams as vectors from PDF to SVG

Tensorlake is compromised using mini Shai Hulud

Show HN: BerryDB – Open-source macOS database client written in Swift and AppKit

Show HN: Kahawai – An open source, modular media system

It's Probably Time to Start Worrying About the 4G LTE Shutdown Bricking Cars

Mellum2.1 Gets to Work: A Fast Open Model for Coding Agents

Preach+ – AI-assisted sermon prep for preachers (EN/ES)

How Musk, Thiel, and Altman Misread Science Fiction

Show HN: Bot Bowl Bench – Benchmarking LLMs with Blood Bowl

Neat trick to make big PRs readable: reslicing them into small commits

Making np.searchsorted up to 25× Faster in NumPy 2.5

Show HN: Etchv – API for invisible watermarks in images, PDFs and videos

Telnet BBS Guide

Pivot – Real-time analytics on Iceberg, faster than ClickHouse on benchmarks

How HN: Dialtrace – see where the time goes on a voice AI phone call

Introducing XP

What Are the Monkeys Typing? We can see what AI does. We can't tell why

Show HN: Get YouTube Text – free YouTube transcript tool, no signup or ads

Man vs. 'Terminator'-Like Robot in a Cage. California Called It Illegal

SSHing to my reMarkable from anywhere

SpacetimeDB Ax Audit: A Review for AI Agents

Colleges need to teach last-mile knowledge

From Relic to Infrastructure: Can India's Historic Water Systems Work Again?

Distilling Stockfish with One Billion Positions

The Rise and Fall of the Plasma Screen

Senator tried to ban gambling on prediction markets–now she's a Kalshi lobbyist