How to export binary file with psql (without PGCOPY header)?

Question

I have a bytea column in PostgreSQL DB which holds PDF files. How can I export that file using psql?

I tried:

psql -U <USER> -h <HOST> -p <PORT> -d <DB> -c "\copy (select <column> from <table> where <column> = <id>) to STDOUT with BINARY;" > output.pdf

That saves the file and I can open it in a PDF reader. But when I check the file with hexdump -C output.pdf | head, I see it has a header starting with PGCOPY.

How can export that file without PGCOPY header?

Answer 1

I got it using Postgre's encode() to hex and bash xxd to decode from hex:

psql -U <USER> -h <HOST> -p <PORT> -d <DB> -c "\copy (SELECT encode(<column>, 'hex') from <table> where <column> = <id>) to STDOUT"  | xxd -p -r > output

File looks ok:

$ hexdump -C output | head -n 5
00000000  25 50 44 46 2d 31 2e 36  0d 25 e2 e3 cf d3 0d 0a  |%PDF-1.6.%......|
00000010  38 37 20 30 20 6f 62 6a  0d 3c 3c 2f 4c 69 6e 65  |87 0 obj.<</Line|
00000020  61 72 69 7a 65 64 20 31  2f 4c 20 31 30 32 33 32  |arized 1/L 10232|
00000030  32 35 2f 4f 20 38 39 2f  45 20 31 35 36 35 30 36  |25/O 89/E 156506|
00000040  2f 4e 20 31 37 2f 54 20  31 30 32 32 38 30 36 2f  |/N 17/T 1022806/|

Answer 2

Binary COPY OUT files are only intended for consumption by COPY IN commands. There is no way to prevent Postgres from writing the file/row/field headers.

You could try to strip them off yourself - either after creating the file, or by piping it straight into dd using the COPY TO PROGRAM statement - though keep in mind that the headers could potentially change in future releases of Postgres.

As far as I'm aware, the only mechanism provided by Postgres to write binary files is lo_export, though you'll have to jump through a few hoops to convert your data to the Large Object format.

Alternatively, you can write your own function in an untrusted procedural language (PL/PerlU or PL/PythonU).