Take the 2-minute tour ×
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.

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. –  elementz 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
add comment

3 Answers

up vote 2 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
add comment

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. –  elementz 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. –  elementz 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. –  elementz Nov 26 '12 at 17:10
1  
@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
show 2 more comments

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
add comment

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.