-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathpg-bytea-batchimport-cs.sh
executable file
·79 lines (66 loc) · 2.24 KB
/
pg-bytea-batchimport-cs.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
#!/bin/sh
#
# PostgreSQL batch-importing binary files, from disk to database table.
# - psql client-side file paths -
#
# by nyov, 2019
# Public Domain
#
# Run as
# ./pg-bytea-batchimport-cs.sh <databasename> <source-directory> [<tablename>]
#
DATABASE="${1}"
FILEPATH="${2}"
TBLENAME="${3:-tmp_docs}"
# add additional options to `find` files more selectively
# (e.g. limited to depth level, or newer than $last-run minutes)
EVAL_CMD="find ${FILEPATH} -type f"
# fallback: `ls`, may not create desired output, will not work with many files
#EVAL_CMD="ls ${FILEPATH}/* -R"
if [ -z "${DATABASE}" ] || [ -z "${FILEPATH}" ]
then
echo "Missing argument"
exit 1
fi
TMPFILE="`mktemp`_loadscript.psql"
SQL="CREATE TABLE \"${TBLENAME}\" (
file_name TEXT,
doc BYTEA,
doc_oid OID
);"
psql -c "${SQL}" "${DATABASE}"
SQL="\copy \"${TBLENAME}\" (file_name) FROM PROGRAM '${EVAL_CMD}' WITH (FORMAT csv, DELIMITER '|')"
psql -c "${SQL}" "${DATABASE}"
# Generate a loading script with psql instructions
SQL="SELECT '\lo_import ' || quote_literal(replace(file_name, '\', '/'))
|| '
UPDATE \"${TBLENAME}\" SET doc_oid = :LASTOID
WHERE file_name = ' || quote_literal(file_name) || ';'
FROM \"${TBLENAME}\";
"
psql -o "${TMPFILE}" -t -c '\x off' -A -c "${SQL}" "${DATABASE}"
echo "Generated ${TMPFILE}"
# CLIENT-SIDE data loading (slow for many files, runs an UPDATE for each file)
# - execute generated psql instructions -
echo "Loading data..."
psql -q -c '\timing off' -f "${TMPFILE}" "${DATABASE}"
if [ $? -eq 0 ]; then
echo "Cleaning up ${TMPFILE}"
rm "${TMPFILE}"
else
echo "PSQL execution failed on importing. Aborting."
echo "Check the generated script for correctness: ${TMPFILE}"
exit 1
fi
# Loading large-object data into BYTEA table column
SQL="UPDATE \"${TBLENAME}\" SET doc = lo_get(doc_oid)"
psql -c "${SQL}" "${DATABASE}"
# Remove large-object stored copies
SQL="SELECT count(lo_unlink(o.oid)) AS lo_unlinked FROM pg_largeobject_metadata AS o WHERE o.oid IN (SELECT doc_oid FROM \"${TBLENAME}\")"
psql -c "${SQL}" "${DATABASE}"
# Build index on file_name column
SQL="ALTER TABLE \"${TBLENAME}\" ADD UNIQUE (file_name)"
psql -c "${SQL}" "${DATABASE}"
# Drop now useless doc_oid column
SQL="ALTER TABLE \"${TBLENAME}\" DROP COLUMN doc_oid"
psql -c "${SQL}" "${DATABASE}"