0

I am searching for a way to integrate psql statements in bash scripts for devops reasons.

The simplest way I would do that is: CMD='psql -c "DROP DATABASE restore_database;"'

then use a bash function to execute the statement:

   EXEC () {

    $CMD > /dev/null 2>&1
    if [ $? -eq 0 ]
    then
            RETVAR="DONE"
    else
            echo "Command: $CMD is not working."
            echo "- Exiting Function"
            exit 0
    fi
    }

I can't beleave this is not working.... ?

0

Just writing $CMD in the script to execute it doesn't do what you expect in bash, because the arguments get broken down into 4 components like this:

-c
"DROP
DATABASE
restore_database;"

Technically you could write eval $CMD instead, but a better design is not to have an entire command passed to the function, but rather the minimum needed, such as the SQL command string.

| improve this answer | |
  • Yes, great, it is exactly what I noticed. I have to run about 20 commands, delete files set rights and execute postgresql scripts. I don't want to write every command and create a error handling for every single command on its own. Is there a simple solution? – Jens Herrmann Apr 7 at 8:06
  • @JensHerrmann: the baseline is to have set -e at the start of the script and no custom error handling at all, unless you have specific requirements. – Daniel Vérité Apr 7 at 11:07
  • Hi, I don't know what you mean with the set -e, but the eval command works fine: Evan with a very simple error handling. Thank you very much. – Jens Herrmann Apr 7 at 18:32
  • @JensHerrmann: you're welcome. Please accept the answer if you're done with that question. – Daniel Vérité Apr 8 at 8:52
0

So finaly it worked for me.

Maybe someone has a similar problem, although my solution is maybe not good practice:

File : db-transfer.sh


#!/bin/bash

WRKFLDR=/tmp/

EXEC () {
        eval $CMD > /dev/null 2>&1
        if [ $? -eq 0 ]
        then
                echo $?
                echo "Command: $CMD OK"
        else
                echo $?
                echo "Command: $CMD is not working."
                exit 0
        fi
        }

CMD="rm -f $WRKFLDR*.bak"
EXEC

CMD='psql -c "SELECT count(*) FROM pg_stat_activity;"'
EXEC

CMD='psql -c "SELECT count(*) FROM pg_stat_activity WHERE (pg_stat_activity.datname = '\''restore_oms'\'' OR pg_stat_activity.datname = '\''restore_oms_mini'\'') AND pid <> pg_backend_pid();"'
EXEC

CMD='psql -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE (pg_stat_activity.datname = '\''restore_db'\'' OR pg_stat_activity.datname = '\''restore_db_mini'\'') AND pid <> pg_backend_pid();"'
EXEC

Run the script.sh with the following command:

sudo -u someone /opt/scripts/db-transfer.sh > /tmp/result.txt
| 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.