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

Question

I have a column byteain a PostgreSQL database that contains PDF files. How can I export this 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

This 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 that it has a header starting with PGCOPY.

How to export this file without a header PGCOPY?

Answer 1

I got it using Postgre encode()for hex and bash xxdto 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

The file looks fine:

$ 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

COPY OUT COPY IN. Postgres //.

- , dd COPY TO PROGRAM, , Postgres.

, , Postgres , lo_export, , Large Object.

(PL/PerlU PL/PythonU).