0

I have the following script:

#!/bin/bash -e
set -e
DATA_DIR=/home/admin/backup_avl_historico/data
DB_HOST=myHost
DB_USER=myPass

#extract table list
logger 'Extracting Table List'
psql -h $DB_HOST -U $DB_USER -c "select table_name from information_schema.tables where table_name like 'avl_historico_%';" -t -o $DATA_DIR/tables.list
array=($(wc -l $DATA_DIR/tables.list))
logger ''$array
total_tables=${array[0]}
logger 'Total tables: '$total_tables

#Get max date
max_date=$(psql -h $DB_HOST -U $DB_USER -t -c "select now() - interval '12 months'")
logger 'Max date: '$max_date

array=($max_date)
date=${array[0]}
logger 'Only date: '$date

#Dump each table
while read table_name
do
logger 'looping...'
        if [ ! -z "$table_name" ]; then
                logger 'Processing table '$table_name
                output=${table_name}_pre_${date}.csv
                psql -h $DB_HOST -U $DB_USER -t -F , -c "COPY (select * from reports.$table_name where fecha < '$max_date') TO STDOUT WITH CSV" -o ${DATA_DIR}/$output
                if [ -f ${DATA_DIR}/$output ];then
                        if test -s ${DATA_DIR}/$output
                        then
                                logger 'Deleting records'
                                psql -h $DB_HOST -U $DB_USER -c "delete from reports.$table_name where fecha < '$max_date'"
                                logger 'Gzipping '$output
                                pigz  ${DATA_DIR}/$output
                                logger 'Moving to S3'
                                aws s3 mv ${DATA_DIR}/$output.gz s3://my-bucket/avl_historico/
                                logger 'Vacuuming table'
                                psql -h $DB_HOST -U $DB_USER -c "vacuum full analyze reports.$table_name"
                        else
                                rm ${DATA_DIR}/$output
                        fi
                fi
        fi
done < $DATA_DIR/tables.list

The problem I'm having is that when PostgreSQL exits a statement with the following error:

ERROR:  canceling statement due to lock timeout

The entire script gets aborted and doesn't continue with the next iteration of do the loop.

Any ideas on how to avoid that exit condition would be appreciated, so the script could just skip one iteration, but continue with the rest

  • 3
    The -e option means "if any pipeline ever ends with a non-zero ('error') exit status, terminate the script immediately" – Dalvenjia Apr 11 '17 at 17:26
  • Thanks @DalvenjiaI see that I have TWO -e flags, on the first 2 lines, which one should I delete? Both of them? – Gonzalo Vasquez Apr 11 '17 at 17:29
  • @GonzaloVasquez Yes, both of them. – Kusalananda Apr 14 '17 at 15:40
2

If you want your script to execute all commands disregarding any failure remove both -e flags. On the other hand, if you still want to terminate the script in case of error, but want to catch a specific one (PostgreSQL in your case) leave only one of the -e flags, it doesn't matter which one but a personal preference is on the script not on the shebang, and the way to catch the error is to add an || (logical OR) to the end of the command exiting with non 0. What this || do is if the previous command exit code is not 0 execute the following one:

psql -h $DB_HOST -U $DB_USER -c "delete from reports.$table_name where fecha < '$max_date'" || true

The above example will silently catch psql non 0 exit codes and continue, you can replace the true command for anything you like (log the error, wait some time, etc...) just make sure it exits with 0 or you will end in the same situation. The true command does absolutely nothing at all, it just exits with 0 code.

  • Ok, I get it, now what if I want to actually "catch" the possible error from psql invocations to decide which actions to take? – Gonzalo Vasquez Apr 11 '17 at 18:28

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.