I am trying export data from a postgres database to a file in bash. But I would like to make sure that the file is only overwritten if the connection to the database doesn't fail (i.e. I get some data back)

Tried the use the pipefail option, however if the first command fails with an error (host doesn't exist for example), the cat command still executes and generates an empty file (wiping out the last good content from it which I would like to prevent). In the example below, myhost is invalid host, so the psql command would simply fail.

So the larger question is how to make sure that when pipefail is set, subsequent commands are not executed when the first command fails.

set -o nounset
set -o errexit
set -o pipefail


psql $PG_HOST -At -F$'\t' -c "SELECT * FROM mytable" | cat > /tmp/mytable.txt
  • I don't think that's what pipefail does. If I may sidestep the direct question for the overall goal, what about piping the psql output to a temporary file and renaming the file only if it was successful? psql ... > temp && mv temp mytable ?
    – Jeff Schaller
    Feb 16 '16 at 20:26
  • on a separate side note, it appears that psql has a "--output=filename" option; does that clobber the output file if the overall command fails?
    – Jeff Schaller
    Feb 16 '16 at 20:28
  • Pipes run in parallel; their very nature make it difficult to stop the truncation of /tmp/mytable.sh. Note that that will happen irrespective of the cat running successfully - the file is truncated, then set up as the output for cat, and then cat is started. Maybe you should look into sponge from moreutils.
    – muru
    Feb 16 '16 at 20:43
  • The psql is just one use case, understand that I can achieve the desired output in multiple steps. Trying to understand how pipe behaves stream data between 2 systems elegantly. An other use case would be to take the output of the first command from STDOUT and pipe it into an other Postgres server that would copy the dat from STDIN. psql -H host1 -c "select * from mytable" | psql -H host2 -c "TRUNCATE TABLE mytable; COPY mytable FROM STDIN" The problem is that if the first command fails the 2nd still runs, truncates the table and copies the empty stream.
    – BraveHeart
    Feb 16 '16 at 21:04
  • pipefail doesn't abort if there is an error; it merely means that an error at any point in a pipe chain will be preserved for the exit code even if the final command is successful. set -o pipefail; if ! /bin/false | /bin/yes; then echo foo; fi will indeed output foo even though yes returns 0. What you might really be looking for is trap 'echo "Something went awry"; exit 1' ERR.
    – DopeGhoti
    Feb 16 '16 at 23:07

set -o pipefail errexit does prevent subsequent commands to be executed, but that doesn't help you, because you are not trying to prevent a subsequent command from being executed. In a pipeline producer | consumer, the producer and consumer commands execute in parallel. You can't prevent consumer from starting if producer fails because barring a freak timing accident, it has already started.

If the only two possibilities are “consumer succeeds and produces non-empty output” and “consumer fails and produces no output”, you can use ifne from Joey Hess's moreutils.

producer | ifne consumer

I don't think that works in your use case though — there could happen to be no matching rows (false negative, and you get stale data), the database connection could be lost in the middle (false positive, and you get truncated data).

If you need to know whether the producer succeeded, then you need to wait until it's finished before starting the consumer. And since the consumer isn't around yet, something needs to store the output.

If the output doesn't contain null bytes and isn't overly large, you can store it in a shell variable.

output=$(producer); producer_status=$?
if [ $producer_status -ne 0 ]; then
  echo >&2 "Producer failed with status $producer_status"
  exit $producer_status
printf '%s\n' "$output" | consumer

In ksh93, bash or zsh, that last line can be simplified to consumer <<<"$output".

Note that command substitution strips trailing newlines. If trailing empty lines are relevant, a workaround is to change the first line to

output=$(producer; echo a); producer_status=$?; output=${output%?}

If the output it potentially too large or may contain null bytes, store it in a temporary file.

  • what if there's multiple consecutive commands and I want to fail the pipe on any of them, but I don't want to check each? There's no straightforward option/command?
    – xeruf
    May 17 '20 at 20:07
  • @Xerus You mean a | b | c and you don't even want to run b if a fails? Then a pipe is the wrong construct. a, b and c run in parallel, so by the time a finishes, the others have already started. May 18 '20 at 8:30
  • yeah, I guessed so. I have now broken down the pipe. Actually, this ifne would be a perfect match, but I don't want to add dependencies and I couldn't find its source code on that site to potentially embed it
    – xeruf
    May 19 '20 at 10:26

As DopeGhoti said, pipefail ... merely means that error at any point in a pipe chain will be preserved for the exit code [of a pipeline].

To cause the script to exit on error, use set -e.

To prevent creation of the file, create a temporary one and rename it on success, viz:

set -e 
psql $PG_HOST -At -F$'\t' -c \
    "SELECT * FROM mytable"  >  /tmp/mytable.txt~
                          # ^^^ cf. Useless Use of Cat
mv /tmp/mytable.txt~ /tmp/mytable.txt

I always use make for this sort of thing, because it stops on error and lets me build restartable pipelines.

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.