Another option is something that I've just recently come across (granted, I'm yet to use it, but plan to soon):
It is a little bit simpler and lighter than Jailer, with a few nice features/perks:
- CLI so easy to wire up to existing tooling
- Open source
- Will follow foreign keys to fetch a coherent subset of data
- If you don't have well-defined foreign keys, the relationships can be provided via a JSON config file. In my case, I plan to generate this config from schema metadata stored elsewhere (thanks, Rails :\)
- You can target a specific row and get all related records (e.g. a specific customer is having issues, so you can pull down everything to make their account work data accessible locally)
- It can either take a constant number of records per table or take a logarithm to get more data from bigger tables without going overboard.
It's worth mentioning another option for Heroku databases specifically, which I've used a lot (as I used to work there).
Heroku is actually pretty fast at bringing up a new snapshotted DB as it pulls the write-ahead logs first to prepare, then connects to the primary DB to catch up, then stops following it. You can create these "forks" across apps to avoid affecting production too much:
$ heroku addons:create heroku-postgres[:specific-plan] \
--fork your-production-app::DATABASE \
--app some-other-app-to-own-forked-database \
Then, you can boot up your app locally pointed to that snapshot. I use this frequently to do a dry run of data or schema migrations or debug customer issues.
In that command above, if you have a DB accessible with
your-production-app, you'll end up with a DB accessible with
PRODUCTION_SNAPSHOT_URL (if you specified
--as) on a different app named