gzip table to csv on AWS instance, prior to downloading / saving locally with psql

Question

I'm new to PostgreSQL and the psql CLI. My bandwidth is extremely limited, which results in it taking hours to download each table from an AWS instance, that are 1 - 5 GB's each. The current command I use, after logging into the DB with psql:

\copy (SELECT * FROM table) TO table.csv CSV DELIMITER ','

Is it possible to query a table, similar to the above, that actually zips the csv file ON the Amazon PostgreSQL instance, prior to downloading and saving locally, thus reducing the 1 - 5 GB downloads to < 1 GB; significantly reducing the download times?

Something like:

\copy (SELECT * FROM table) TO csv.zip CSV DELIMITER ',' TO table.csv.zip

I came across this gist, but the commands listed appear to be a complete dump of all tables / the entire db. I would like the ability to do the same for tables and subset queries.

EDIT: Solution = \copy (SELECT * FROM table) TO PROGRAM 'gzip > Users/username/folder/folder/my_table.gz' DELIMITER ',' after logging into psql

Is it what you search? COPY (SELECT * FROM table) TO PROGRAM 'gzip > ~/my_table.zip' DELIMITER ','; — Jim Jones
– Jim Jones, Commented Feb 27, 2018 at 9:44

Jim Jones · Accepted Answer · 2018-02-27 10:25:38Z

3

Using psql and the STDOUT. This command will return the output to the client and will compress it:

psql yourdb -c "\COPY (SELECT * FROM table) TO STDOUT;" | gzip > output.gz

Or directly at the database server (also into a compressed file), using a client of your choice:

COPY (SELECT * FROM table) TO PROGRAM 'gzip > /var/lib/postgresql/my_table.gz' DELIMITER ',';

edited Feb 27, 2018 at 10:25

answered Feb 27, 2018 at 10:06

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andrew Over a year ago

Thanks. When I attempted: COPY (SELECT * FROM table) TO PROGRAM 'gzip > /my_table.gz' DELIMITER ','; it advised me to use "\copy" instead; when I used '\copy' instead, it returned

sh: /my_table.gz: Permission denied. could not write COPY data: Broken pipe gzip > /my_table.gz: child process exited with exit code 1

... Is the "/var/lib/postgresql/" portion required?

Jim Jones Over a year ago

Did you make sure the user postgres has permission to write in this folder?

Jim Jones Over a year ago

instead of /my_table.zip try /tmp/my_tyble.zip or any other folder postgres can write in

Andrew Over a year ago

Okay sorted; thank you so much for following up! my problem was that although I opened terminal in the specific folder I import to, which works fine without zipping, I needed to specify the full file path of that folder when zipping. e.g \copy (SELECT * FROM table) TO PROGRAM 'gzip > Users/me/folder/folder/my_table.gz' DELIMITER ',';

Collectives™ on Stack Overflow

gzip table to csv on AWS instance, prior to downloading / saving locally with psql

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related