The COPY command in PostgreSQL is a simple way to copy data between a file and a table. COPY can either copy the content of a table to or from a table. Traditionally data was copied between PostgreSQL and a file. However, recently a pretty cool feature was added to PostgreSQL: It is now possible to send data directly to the UNIX pipe.
Table of Contents
The ability to send data directly to the UNIX pipe (or Linux command line) can be pretty useful. You might want to compress your data or change the format on the fly. The beauty of the UNIX shell is that it allows you all kinds of trickery.
If you want to send data to an external program – here is how it works:
|
1 2 3 |
test=# COPY (SELECT * FROM pg_available_extensions) TO PROGRAM 'gzip -c > /tmp/file.txt.gz'; COPY 43 |
In this case, the output of the query is sent to gzip, which compresses the data coming from PostgreSQL and stores the output in a file. As you can see, this is pretty easy and really straight forward.
However, in some cases users might desire to store data on some other machine. Note that the program is executed on the database server and not on the client. It is also important to note that only superusers can run COPY … TO PROGRAM. Otherwise people would face tremendous security problems, which is not desirable at all.
Once in a while ,people might not want to store the data exported from the database on the server but send the result to some other host. In this case SSH comes to the rescue. SSH offers an easy way to move data.
Here is an example:
|
1 |
echo 'Lots of data' | ssh user@some.example.com 'cat > /directory/big.txt' |
In this case “Lots of data” will be copied over SSH and stored in /directory/big.txt.
The beauty is that we can apply the same technique to PostgreSQL:
|
1 2 3 |
test=# COPY (SELECT * FROM pg_available_extensions) TO PROGRAM 'ssh user@some.example.com ''cat > /tmp/result.txt'' '; COPY 43 |
To make this work in real life, you have to make sure that SSH keys are in place and ready to use. Otherwise the system will prompt for a password, which is of course not desirable at all. Also keep in mind that the SSH command is executed as “postgres” user (in case your OS user is called “postgres” too).
+43 (0) 2622 93022-0
office@cybertec.at
connecting to remote db server to create compressed csv dump using 7zip on linux terminal:
PGPASSWORD="somepassword" psql --host=db-host.url.eu-west-1.rds.amazonaws.com --port=5432 --username=dbuser --dbname=my_db_name -c "COPY (SELECT column_name FROM table_name) TO PROGRAM '7z a -tgzip -mx=1 -si /home/ubuntu/path/to/compressed/csv/db_dump.csv.gz';"Or do it directly client side:
psql -d postgres --tuples-only -c "select * from pg_available_extensions;" | gzip -c > /tmp/file.txt.gz