How to download Postgres bytea column as file

user_849686 0
Viewed 54789

Currently, i have a number of files stored in postgres 8.4 as bytea. The file types are .doc, .odt, .pdf, .txt and etc.

May i know how to download all the file stored in Postgres because i need to to do a backup. I need them in their original file type instead of bytea format.

Thanks!

postgresql byte file
8 Answers

Here's the simplest thing I could come up with:

psql -qAt "select encode(file,'base64') from files limit 1" | base64 -d

The -qAt is important as it strips off any formatting of the output. These options are available inside the psql shell, too.

user_15768860

base64

psql -Aqt -c "SELECT encode(content, 'base64') FROM ..." | base64 -d > file

xxd

psql -Aqt -c "SELECT encode(content, 'hex') FROM ..." | xxd -p -r > file
user_12125960 
DO $$ 
DECLARE   
   l_lob_id OID;
   r record; BEGIN

  for r in
    select data, filename from bytea_table

   LOOP
    l_lob_id:=lo_from_bytea(0,r.data);
    PERFORM lo_export(l_lob_id,'/home/...'||r.filename);
    PERFORM lo_unlink(l_lob_id);   
    END LOOP;

END; $$
user_110218120

If you want to do this from a local windows, and not from the server, you will have to run every statement individually, and have PGAdmin and certutil:

  1. Have PGAdmin installed.

  2. Open cmd from the runtime folder or cd "C:\Program Files\pgAdmin 4\v6\runtime"

  3. Run in PGAdmin query to get every statement that you will have to paste in cmd:

    SELECT 'set PGPASSWORD={PASSWORD} && psql -h {host} -U {user} -d {db name} -Aqt -c "SELECT encode({bytea_column}, ''base64'') FROM {table} WHERE id='||id||'" > %a% && CERTUTIL -decode %a% "C:\temp{name_of_the_folder}\FileName - '||{file_name}||' ('||TO_CHAR(current_timestamp(),'DD.MM.YYYY,HH24 MI SS')||').'||{file_extension}||'"' FROM table WHERE ....;

Replace {...}

It will generate something like:

set PGPASSWORD=123  psql -h 192.1.1.1 -U postgres -d my_test_db -Aqt -c "SELECT encode(file_bytea, 'base64') FROM test_table_bytea WHERE id=33" > %a% && CERTUTIL -decode %a% "C:\temp\DB_FILE\FileName - test1 - (06.04.2022,15 42 26).docx"
set PGPASSWORD=123  psql -h 192.1.1.1 -U postgres -d my_test_db -Aqt -c "SELECT encode(file_bytea, 'base64') FROM test_table_bytea WHERE id=44" > %a% && CERTUTIL -decode %a% "C:\temp\DB_FILE\FileName - test2 - (06.04.2022,15 42 26).pdf"
  1. Copy paste all the generated statements in CMD. The files will be saved to your local machine.
user_69146340
Related