This seems like it should be easy but it isn't, and I've had to look it up a couple times. Here's the magic juice:
copy (select array_to_json(array_agg(row_to_json(t)))
from (
select * from tweet_pgh limit 5
) t
) to '/home/dantasse/data_dump/foo.json';
gets 5 rows from the tweet_pgh table and dumps them to that file. (the directory has to be world-writeable I think, because it'll execute as the postgres user.)
Edit:
COPY (SELECT ROW_TO_JSON(t) FROM (SELECT ST_AsGeoJSON(coordinates), * FROM tweet_austin) t) TO '/data/austin.json';
or just
COPY (SELECT ROW_TO_JSON(t) FROM (SELECT * FROM tweet_austin) t) TO '/data/austin.json';
seems to work better when you're memory limited.
copy (select array_to_json(array_agg(row_to_json(t)))
from (
select * from tweet_pgh limit 5
) t
) to '/home/dantasse/data_dump/foo.json';
gets 5 rows from the tweet_pgh table and dumps them to that file. (the directory has to be world-writeable I think, because it'll execute as the postgres user.)
Edit:
COPY (SELECT ROW_TO_JSON(t) FROM (SELECT ST_AsGeoJSON(coordinates), * FROM tweet_austin) t) TO '/data/austin.json';
or just
COPY (SELECT ROW_TO_JSON(t) FROM (SELECT * FROM tweet_austin) t) TO '/data/austin.json';
seems to work better when you're memory limited.