The other day, I wanted to export a sample of one of my big Postgres tables from
the production server to my local computer. This was a huge table and I didn’t
want to move around a few GBs just to get a sample onto my local environment.
pg_dump doesn’t support exporting of partial tables. I looked
around and found a utility called pg_sample
which is supposed to help you with this. However, I wasn’t comfortable with
installing this on my production server or letting my production data through
this script. Thinking a little more made the solution obvious. The idea was
- Create a table called
page_cachesis the table that you want to copy using
pg_dumpusing the following SQL in
psql, this gives you a lot of freedom on SELECTing just the rows you want.
CREATE TABLE tmp_page_caches AS (SELECT * FROM page_caches LIMIT 1000);
- Export this table using
pg_dumpas below. Here we are exporting the data to a sql file and transforming our table name to the original table name midstream.
pg_dump app_production --table tmp_page_caches | sed 's/public.tmp_/public./' > page_caches.sql
- Copy this file to the local server using
scpand now run it against the local database:
scp firstname.lastname@example.org:page_caches.sql .
psql app_development < page_caches.sql
- Get rid of the temporary table on the production server
DROP TABLE tmp_page_caches; -- be careful not to drop the real table!
Voila! We have successfully copied over a sample of our production table to our local environment. Hope you find it useful.