Unable to get psql error when running on remote server

Question

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:

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

Any idea how can I sort this out?

Thanks

Fred · Accepted Answer · 2017-01-16 17:24:56Z

1

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.

answered Jan 16, 2017 at 17:24

Fred

7,10512 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Diogo Over a year ago

Thanks a lot Fred. In my case the $? was always returning 0, I guess because the .sql ran in the server and not localy.

cdarke Over a year ago

The doc says: "psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own occurs (e.g. out of memory, file not found), 2 if the connection to the server went bad and the session was not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set." postgresql.org/docs/9.2/static/app-psql.html

MikA Over a year ago

It worked for me :) psql -h $HOST -U $USER -p $PORT -d $SCHEMA -e << EOB \set ON_ERROR_STOP true <my SQLs> EOB result=$? if [ $result = 0 ] then echo "Success" else echo "Failure with error code $result" exit -1 fi

cdarke · Accepted Answer · 2017-01-16 17:11:03Z

0

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.

answered Jan 16, 2017 at 17:11

cdarke

44.8k8 gold badges85 silver badges86 bronze badges

1 Comment

cdarke Over a year ago

@Diogo: please accept the answer if you are happy with it.

Fred · Accepted Answer · 2017-01-16 17:51:36Z

0

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.

answered Jan 16, 2017 at 17:51

Fred

7,10512 silver badges22 bronze badges

Collectives™ on Stack Overflow

Unable to get psql error when running on remote server

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related