Automating a Nightly CSV Export With cron and psql

A nightly export looks like a one-line job: a query, a redirect and a crontab entry. The one-liner works on the day you write it. Over the following months it meets a database restart in the middle of the run, a job that takes longer than the interval, a disk that fills, an environment variable nobody knew it depended on, and a consumer that picks up whatever is in the folder at 6 a.m. This article builds a script that survives those, tests each failure, and then runs it under real cron, where it fails for a reason that has nothing to do with the script.

What can go wrong

Think about the 6 a.m. consumer, who sees only the folder. Any of these gives it a bad file or no file without a warning:

• the export died halfway and left a partial file under the real name

• the export hit an error and the script ignored it because a pipe hid the status

• the previous run was still going when the next one started, and both wrote to the same name

• the environment differed from your shell, and the job failed before it started

• the job failed and nobody found out

The script below deals with each in turn.

The script

#!/usr/bin/env bash
# Nightly orders export: one gzip CSV per UTC day, written under a temporary name, checked, then renamed.
# Exit 0 only when the final file exists, passed its checks and has a manifest.
set -euo pipefail

DEST=${DEST:-/batches/csv-0001-0200/work/export/nightly}
KEEP_DAYS=${KEEP_DAYS:-7}
DAY=$(date -u +%F)
NAME=orders-$DAY.csv.gz
LOG=$DEST/export.log

mkdir -p "$DEST"
log() { printf '%s %s\n' "$(date -u +%FT%TZ)" "$*" >> "$LOG"; }

exec 9> "$DEST/.lock"
if ! flock -n 9; then
  log "FAILED: the previous export is still running"
  exit 3
fi

trap 'log "FAILED at line $LINENO (exit $?)"; rm -f "$DEST/$NAME.part"' ERR

log "start $NAME"
expected=$(psql -Atc "SELECT count(*) FROM orders")

psql -qc "COPY (SELECT * FROM orders ORDER BY order_id) TO STDOUT WITH (FORMAT csv, HEADER)" | gzip > "$DEST/$NAME.part"

gzip -t "$DEST/$NAME.part"
rows=$(( $(zcat "$DEST/$NAME.part" | wc -l) - 1 ))
if [ "$rows" -ne "$expected" ]; then
  log "FAILED: $rows rows in the file, $expected in the table"
  rm -f "$DEST/$NAME.part"
  exit 4
fi

mv "$DEST/$NAME.part" "$DEST/$NAME"
printf '{"file": "%s", "rows": %d, "bytes": %d, "sha256": "%s"}\n' \
  "$NAME" "$rows" "$(stat -c %s "$DEST/$NAME")" "$(sha256sum "$DEST/$NAME" | cut -d' ' -f1)" > "$DEST/$NAME.manifest.json"

find "$DEST" -name 'orders-*.csv.gz*' -mtime +"$KEEP_DAYS" -delete
log "done $NAME $rows rows"

Reading from the top:

• set -euo pipefail stops the script on any error, on an unset variable and, importantly, on a failed stage of a pipeline. Without pipefail the export pipe reports only gzip's status, as the gzip article shows.

• The date is the UTC day, so the file name does not depend on the machine's zone.

• flock -n 9 takes a lock on a file and fails at once if another run holds it. The lock disappears when the process exits, however it exits, so there is no stale lock to clean up.

• The ERR trap writes a log line and deletes the temporary file when anything fails.

• The query runs **to a .part name**. The real name never exists until the file has passed gzip -t and the row count matches the table's, and then the script renames it. A rename within one file system is atomic, so a consumer sees either no file or a complete one.

• It writes a manifest last, with the file name, row count, byte size and SHA-256, so the consumer can check what it picked up. This is the first piece of the contract.

• It prunes files older than seven days, and logs done.

One caveat in the code is a real one. The count and the export are two statements, so they are two snapshots, and a busy table can change between them and fail the check even when nothing is wrong. For a table that changes during the run, take both from one repeatable-read transaction, or export from a replica or a table that is frozen for the night. I did not test that variant.

Run it by hand first

time bash lab/export/nightly_export.sh; echo "exit status: $?"
ls -l work/export/nightly | awk 'NR>1 {print $5, $9}'
cat work/export/nightly/*.manifest.json
tail -n 2 work/export/nightly/export.log

real	0m6.967s
user	0m6.617s
sys	0m0.666s
exit status: 0
116 export.log
42200899 orders-2026-10-26.csv.gz
151 orders-2026-10-26.csv.gz.manifest.json
{"file": "orders-2026-10-26.csv.gz", "rows": 2000000, "bytes": 42200899, "sha256": "542be2dd863924aa02393845662370def40a929954756eadb8efc5cd7b1b371f"}
2026-10-26T02:45:43Z start orders-2026-10-26.csv.gz
2026-10-26T02:45:50Z done orders-2026-10-26.csv.gz 2000000 rows

It took 6.66 s and exited 0. The folder holds the log, the compressed file (42,200,899 bytes, 2,000,000 rows) and the manifest, whose SHA-256 begins 542be2dd. The export itself takes about 4.6 s and the rest is the gzip -t, the zcat | wc -l and the checksum: three more passes over the data, which is the price of checking. For nightly data that price is worth paying, and for a table ten times larger the check is a few minutes.

When the database is unreachable

before=$(sha256sum work/export/nightly/orders-*.csv.gz | cut -c1-16)
PGHOST=no-such-host bash lab/export/nightly_export.sh 2>&1 | tail -n 2; echo "exit status: ${PIPESTATUS[0]}"
after=$(sha256sum work/export/nightly/orders-*.csv.gz | cut -c1-16)
[ "$before" = "$after" ] && echo "the earlier good file is unchanged: $after"
ls -a work/export/nightly | tr '\n' ' '; echo
tail -n 2 work/export/nightly/export.log

psql: error: could not translate host name "no-such-host" to address: Name or service not known
exit status: 2
the earlier good file is unchanged: 542be2dd863924aa
. .. .lock export.log orders-2026-10-26.csv.gz orders-2026-10-26.csv.gz.manifest.json 
2026-10-26T02:45:50Z start orders-2026-10-26.csv.gz
2026-10-26T02:45:50Z FAILED at line 24 (exit 2)

With PGHOST=no-such-host the script exited with status 2 (psql's code for a connection failure), logged FAILED at line 24 (exit 2), and left no .part file in the folder. The earlier good file kept its checksum, 542be2dd863924aa: a failed run does not touch yesterday's output, because the temporary name is the only thing it writes and the rename is the last step. A consumer at 6 a.m. sees the old file with its old date, which is an honest answer.

When the last run is still going

( bash lab/export/nightly_export.sh & ) ; sleep 0.5
bash lab/export/nightly_export.sh; echo "second run exit status: $?"
sleep 9
tail -n 3 work/export/nightly/export.log

second run exit status: 3
2026-10-26T02:45:50Z start orders-2026-10-26.csv.gz
2026-10-26T02:45:51Z FAILED: the previous export is still running
2026-10-26T02:45:57Z done orders-2026-10-26.csv.gz 2000000 rows

A second run started half a second after the first. It exited with status 3 and logged FAILED: the previous export is still running; the first run carried on and logged done ... 2000000 rows about six seconds later. An overlap is a failure worth reporting, because it means the job is slower than its schedule, and the script treats it as one. Without the lock, two runs would both write to orders-DAY.csv.gz.part and the result would be a corrupt mix.

Under cron

So far it has only been run from an interactive shell. Cron runs it differently (crontab(5) describes the environment it provides). First, a plain crontab:

crontab -l

* * * * * /batches/csv-0001-0200/lab/export/nightly_export.sh >> /tmp/cron1.out 2>&1

I set it to run every minute for the test, and a real nightly line would read 15 2 * * * for 02:15 in the machine's time zone (see man 5 crontab). After the minute passed:

echo "--- what cron printed:"; cat /tmp/cron1.out
echo "--- the job's own log:"; tail -n 2 work/export/nightly/export.log

--- what cron printed:
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?
--- the job's own log:
2026-10-26T02:47:01Z start orders-2026-10-26.csv.gz
2026-10-26T02:47:02Z FAILED at line 24 (exit 2)

It failed. Cron printed psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory, and the job's own log shows FAILED at line 24. In this container, PGHOST, PGUSER and PGPASSWORD were set by the container's environment, and cron does not hand its jobs that environment: it starts them with a minimal one. psql fell back to a local socket. The script was fine. Every environment variable your shell has and cron does not is a latent bug of this kind, including PATH, locale and the proxy settings. Set what the job needs, explicitly.

Two ways, and I used both. Environment variables can be assigned at the top of a crontab, and cron passes them to every job. The password does not belong there, because crontabs are readable and logged; it goes in ~/.pgpass with mode 600, which psql reads on its own:

crontab -l

PGHOST=pg
PGUSER=postgres
PGDATABASE=lab
* * * * * /batches/csv-0001-0200/lab/export/nightly_export.sh >> /tmp/cron2.out 2>&1

echo "--- what cron printed:"; cat /tmp/cron2.out; echo "(nothing)" | grep -c . >/dev/null
echo "--- the job's own log:"; tail -n 2 work/export/nightly/export.log
ls -l work/export/nightly | awk 'NR>1 {print $5, $9}'

--- what cron printed:
--- the job's own log:
2026-10-26T02:48:01Z start orders-2026-10-26.csv.gz
2026-10-26T02:48:07Z done orders-2026-10-26.csv.gz 2000000 rows
216 export.log
42200899 orders-2026-10-26.csv.gz
151 orders-2026-10-26.csv.gz.manifest.json

The next run printed nothing, which is what cron should print on success, and the log shows start at 02:48:01 and done six seconds later with 2,000,000 rows. The directory holds the file and the manifest. The cron daemon mails whatever a job prints to the crontab's owner, if mail is set up, so a job that prints only on failure produces an alert only on failure. That is why the script sends its progress to a log file and not to standard output.

Knowing it failed

A scheduler that fails quietly is the worst outcome of all. The script tells you in three places: the exit status, the log and the age of the newest file. The exit status is for cron's mail or a wrapper that posts to a chat. The log is for diagnosis. And the age of the newest manifest is the one that catches the case where the job never started: a check that runs separately, at 6 a.m., and complains when the newest orders-*.csv.gz.manifest.json is older than 26 hours. Silence means success only if something else is watching for it. A job that failed to start, because cron was not running or the server was down, leaves no log line at all.

How to tell it worked

Run it by hand and read the log. Break each thing on purpose: an unreachable host, a wrong table name (the row count then fails), a full disk if you can arrange one, and a second concurrent run. After each, check that the real file name still holds yesterday's good file or does not exist, and that no .part file is left. Then run it under cron with a one-minute schedule once, from a clean environment, before you change it to the nightly time. Finally, check the manifest against the file with sha256sum and a row count from another tool.

When cron is not enough

Cron runs a command at a time. It does not retry, does not know about dependencies, and does not tell you when it has not run. If the export must run after an upstream job finished, a time-based schedule is a guess, and a workflow tool, or a systemd service that is ordered after the upstream one, does the job properly. If several machines could run it, you need a lock that spans machines. If the export is large enough that a single run overnight is risky, split it into key ranges and run the parts in parallel, as in the large-table article. And when the file leaves your network, validate it on the receiving side with the checks in the validation guide.