Importing plain (raw) files into a postgres table (bytea and large objects)

Question

What is a good way to insert/copy plain files into a postgres table, preferrably using the psql command-line?

In my case the files are a bunch of eMails from Maildir archives, so I tried to use COPY:

psql -c "COPY emails (data) FROM '/tmp/emailfile' WITH (FORMAT text);" emails

which I would use in a for-loop shell script (for file in $(ls dir); do psql ...; done).

However, I have trouble finding a good "delimiter" that couldn't possibly be in the file, and I'm getting these errors: ERROR: extra data after last expected column.

So I considered using the COPY ... FORMAT binary version and a BYTEA field in the db (then convert the column to TEXT inside the db), but that requires a file header and trailer, which I have no easy way of building on-the-fly.

Is there an easy way to do this from the commandline, or do I need to write a python script for this?

Answer 1

Server-side files

Assuming the account has the pg_read_server_files role (or is superuser), if the files are accessible on a filesystem mounted on the server, and their paths have been collected in the table, this will grab the contents efficiently:

UPDATE emails SET mail_data = pg_read_binary_file(emails.fullpath);

It is more efficient than using large objects as an intermediate storage area.

Client-side files

When the files are not accessible server-side or the account does not have elevated permissions, with psql a more generic solution may be, for each file:

\set clientpath '/path/to/file'
-- assume clean paths (without any character that would be special to the shell)
\set contents `base64 :clientpath`

insert into email_data([other columns...], mail_data)
 values ( [other columns values...], decode(:'contents','base64'));

A base64 intermediate representation is used because psql doesn't support parameters in binary form. The :'contents' syntax instructs psql to inject the variable in text form into the query.

Answer 2

This PDF document PGOpen2018_data_loading.pdf I found, explains data imports using PG's Large Objects support.
It's been a bit unclear, and is slightly cumbersome; so I scripted it to automate the process (see below).

The procedure works as follows:

-- Create table
CREATE TABLE tmp_docs(file_name text PRIMARY KEY);
-- Pull list of filenames from folder
psql -c "\copy tmp_docs FROM PROGRAM 'ls /file/location/data/* -R' WITH (FORMAT csv);" emails
-- Add fields to hold file link ID and blob of the files
ALTER TABLE tmp_docs ADD COLUMN doc bytea, ADD COLUMN doc_oid oid;
-- Add the documents to large object storage and return the link id
UPDATE tmp_docs SET doc_oid = lo_import(file_name);
-- Pull documents from large object storage into table
UPDATE tmp_docs SET doc = lo_get(doc_oid);
-- Delete the files from large object storage
SELECT lo_unlink(doc_oid) FROM tmp_docs;

Note, the last SELECT did error out for me, I increased shared_buffers and max_locks_per_transaction for my number of files.

[...]
WARNING:  out of shared memory
WARNING:  out of shared memory
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

---

I've written scripts to automate the whole process for me, a server-side variant and a client-side (remote) version using psql-local functions.

They've become slightly large for a SO codeblock, so instead they got a GitHub repo here

https://github.com/nyov/postgres-bindata-batchimport

and this is their usage:

mkdir /tmp/randomtext
for OF in `seq 101 120`; do # twenty random files, please
   curl -s http://metaphorpsum.com/paragraphs/5/5 > /tmp/randomtext/${OF};
done

DB="postgres"

# Usage: ./pg_bytea_batchimport_*.sh <dbname> <source-dir> [<tablename>]
# Run script
./pg_bytea_batchimport_cs.sh $DB /tmp/randomtext tmp_docs
# (or) Server-side version; faster, but requires a local db cluster
#./pg_bytea_batchimport_ss.sh $DB /tmp/randomtext tmp_docs

# Verify; should count 20 rows
psql -c "SELECT count(*) FROM tmp_docs" $DB
psql -c "SELECT encode(doc::bytea, 'escape')::text FROM $TABLE LIMIT 1" $DB

# clean up
rm -r /tmp/randomtext
psql -c "DROP TABLE tmp_docs" $DB
unset DB

Comparing client-side vs server-side script run-time, the client-side variant is about 50% slower on average. Here timed on a test dataset of many smallish text-files:

time ./pg_bytea_batchimport_ss.sh emails /mnt/stor/

real    2m48.045s
user    0m0.368s
sys 0m0.092s

time ./pg_bytea_batchimport_cs.sh emails /mnt/stor/

real    4m46.319s
user    0m1.944s
sys 0m4.448s