0

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?

  • I think seeing a sample of your input file and the target table definition might be helpful. – mustaccio Nov 15 '19 at 23:57
  • @mustaccio, as stated, the input files are raw emails. Just look at any email source. So in effect, utf-8 encoded text-files with any possible kind of content. The table definition is called emails with a column named mail_data of type TEXT or BYTEA; but can be changed. – nyov Nov 16 '19 at 0:07
  • I'm not sure how I missed this earlier, but: Related: dba.stackexchange.com/q/1742 and another one: stackoverflow.com/a/9786524/9214854 (once you have the answer, you know what to search for ...) – nyov Nov 25 '19 at 11:41
2

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.

|improve this answer|||||
  • +1 Thanks. But I'm sorry I can't accept this as my solution; it looks nice but the server-side version fails with ERROR: absolute path not allowed (and I really don't want to fiddle with moving data into pg's home), and the client-side version is also slower because of the base64 overhead (data+cpu). I'd rather accept the Large Objects overhead. – nyov Nov 25 '19 at 2:00
  • @nyov: this error does not occur on recent versions of Postgres (>=11) with the pg_read_server_files built-in role. IIRC with older versions creating a soft link from inside PGDATA to an outside directory does the trick. – Daniel Vérité Nov 25 '19 at 6:50
  • Alrighty! I'll revisit that once on PG11. And now I wonder how psycopg handles the binary transfer - whether the base64 transcoding couldn't be replaced with something more efficient in psql? For now, I've scripted the \lo_import variant with psql, and written a python script after all. – nyov Nov 25 '19 at 10:25
0

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
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.