Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have a relatively big production postgres based db: ~20GB. The PostgreSQL database is hosted on heroku.

I would like to copy a small subset of the table data to my local database so I can run some tests on them without having to work on production.

I do not want to generate sample data myself, but rather use the data which already exists in the production environment.

~100 rows from each table in the database would be sufficient. Is there an easy way to accomplish this?

share|improve this question
1  
Just out of curiousity, why is 20GB too much disk space? I have 100GB allocated on my test-disk on my VM for just DB scratch space. – jcolebrand Nov 26 '12 at 15:02
    
Since I sometimes don't carry my external hd with me, my ssd simply does not provide enough space. Plus I really only need sample data from my db, and not the whole db. – jottr Nov 26 '12 at 15:36
1  
Fair enough, but I encourage you nonetheless to get creative. Consider an SD card to fit in the SD slot, as I've never had an issue with having more data :-) but that does suck :-\ my personal laptop is down to it's last free 10GB. – jcolebrand Nov 26 '12 at 15:38
up vote 3 down vote accepted

I haven't used this tool, but Jailer promises to do just that

Database Subsetting and Browsing Tool. Exports consistent, referentially intact row-sets from relational databases (JDBC)

http://sourceforge.net/projects/jailer/

share|improve this answer

Another option is something that I've just recently come across (granted, I'm yet to use it, but plan to soon): rdbms-subsetter.

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 \
    [--as PRODUCTION_SNAPSHOT]

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 DATABASE_URL on your-production-app, you'll end up with a DB accessible with PRODUCTION_SNAPSHOT_URL (if you specified --as) on a different app named some-other-app-to-own-forked-database.

share|improve this answer

To get a random set of rows you can use LIMIT like this:

SELECT * FROM my_table LIMIT 100

This is the easiest and will just pick the 100 first rows that PostreSQL came across. It might be the 100 last inserted or perhaps the 100 first. If you need something really random, look at this Stackoverflow answer.

share|improve this answer
    
Thank you for trying to help, but this does not answer my question. – jottr Nov 26 '12 at 15:37
    
I do know how to limit my selection. My problem is, that I need some way to clone the db from heroku with a subset of its data; Dumping the whole db as described on herokus manual is not an option. – jottr Nov 26 '12 at 15:43
1  
@elementz - Is there a reason you can't just loop over the tables and COPY (SELECT ...LIMIT 100) TO 'filename' ... ? Can you not COPY TO in Heroku? – rfusca Nov 26 '12 at 16:56
    
@rfusca - I did not know COPY TO existed. I will try and report back. – jottr Nov 26 '12 at 17:10
3  
@rfusca: using a simple LIMIT won't deal with FK references. For example: 100 rows from the orders table might contain customers which are not present when exporting 100 rows from the customers table. – a_horse_with_no_name Nov 26 '12 at 18:02

Jailer can helps in this situation. I am working on the same one as yours (trying to get ~100 records of each table) and here are steps that I did: - Find the root entity (the one that associated with almost tables) and get subset of the records of a root row (e.g root is people, then I will look up for all the related records to people.id=1...) - After finish step 1, start again with another table (the one that you would like to have 100 records) and get its subset from the result above.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.