Tell me more ×
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.
up vote 5 down vote favorite

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

2 Answers

up vote 1 down vote

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
up vote 0 down vote

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

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.