CODEWITHSUNDEEP
bashpsql
Diogo
Diogo

Reputation: 3

Unable to get psql error when running on remote server

I wrote a bash script to run psql scrips in a remote server. Basically the command I'm using is:

psql --out test.log -h HOST_IP_ADDRESS -U USER_NAME -d DATABASE -f SQL_FILE

The command runs ok but when I simulate an error in order to test it I cannot pick up the error message even if I see the error in the shell, as you can see below

psql:SQL_FILE.sql:71: ERROR:  relation "TABLE_NAME" does not exist

I tried already:

  1. using -o output_file but it generates an empty file
  2. using var=$(psql command) but the var returns empty
  3. psql command > file.log but the error is not in the file generated.

Any idea how can I sort this out?

Thanks

Upvotes: 0

Views: 777

Answers (3)

Fred
Fred

Reputation: 7015

I do not know psql, but if your problem is related to the the action being remotely executed, one thing you could do is use ssh to connect to the remote host and execute the command there as if it were locally. ssh will then relay the return code.

It would look like this :

ssh user@remote_host psql ARGUMENT LIST

You would remove anything having to do with remote execution from your command, as it would be executed locally.

Upvotes: 0

Fred
Fred

Reputation: 7015

A command that fails normally generates a return code. You can pick up this return code using the $? special variable right after executing the command. For instance, you could do :

psql ARGUMENT LIST
result=$?

if
  [ $result = 0 ]
then
  echo "Success"
else
  echo "Failure with error code $result"
fi

The return code should normally be the most reliable indication of success or failure of a command.

A command may also send messages on either one of two standard output channels : standard output (stdout), and standard error (stderr). Both normally appear on your terminal, but they are really separate, and can be handled separately if you want to.

You can collect stderr, stdout, or both, by using command substitution :

stdout_messages="$(command and args 2>/dev/null)"
stderr_messages="$(command and args 2>&1 1>/dev/null)"
all_messages="$(command and args 2>&1)"

These commands have redirections like X>/dev/null to avoid displaying the massages not collected, but you can remove these to see them on the terminal.

The presence of any message on stderr does not indicate the command has failed, as many programs might use stderr for status messages or errors that are not fatal. Command substitution should be used to collect messages, not determine if the command has failed, unless you have a very specific need.

You can combine both mechanisms, by collecting messages, and then using $? to determine status.

Upvotes: 1

cdarke
cdarke

Reputation: 44434

Try 

psql command 2> errors.log

The 2 means "redirect file descriptor 2", which is standard error. That will save the error into a file called errors.log.

To get the error into a variable:

var=$(psql command 2>&1)
echo "$var"

Here we redirect standard error (2) to file descriptor 1 (standard output).

You probably tried to use the old obsolete backticks ` characters here, but ` is a format character on this site $(...) is preferred.

Upvotes: 0

Related Questions