Thread: Logging ALL output from psql

Logging ALL output from psql

From
Bobby Dewitt
Date:
I can not find a way to log ALL output from a psql session when running a script.  Basically, I'm running a large deployment script that has thousands of SQL statements and I need to know which statements fail.  I want the output generated when running the script to be saved to a log file so that I can grep through the output and look for errors.  I don't want the script to stop processing using the ON_ERROR_STOP variable.  It doesn't matter to me if the script is run interactively or not.

I've tried redirecting the output to a log file, but this only gets the SQL statements issued and their results and not the errors that may be encountered:
psql -f test_psql_deployment_logging.sql > test_psql_deployment_logging.log

In my SQL script I've set the following variables, but none of them seem to make a difference:
\set VERBOSITY verbose
\set ECHO all

I've tried using the \o option in my script to save the results to a file, but as the documentation mentions it doesn't output error messages :

"Query results" includes all tables, command responses, and notices obtained from the database server, as well as output of various backslash commands that query the database (such as \d), but not error messages.


Using the -L gets me closer, but it too does not include error output, only the queries issued and query results:
psql -f test_psql_deployment_logging.sql -L test_psql_deployment_logging.log

Any suggestions on how to get this to work?

Thanks,
Bobby

Re: Logging ALL output from psql

From
Raymond O'Donnell
Date:
On 19/10/2011 22:10, Bobby Dewitt wrote:
> I can not find a way to log ALL output from a psql session when running
> a script.  Basically, I'm running a large deployment script that has
> thousands of SQL statements and I need to know which statements fail.  I
> want the output generated when running the script to be saved to a log
> file so that I can grep through the output and look for errors.  I don't
> want the script to stop processing using the ON_ERROR_STOP variable.  It
> doesn't matter to me if the script is run interactively or not.
>
> I've tried redirecting the output to a log file, but this only gets the
> SQL statements issued and their results and not the errors that may be
> encountered:
> psql -f test_psql_deployment_logging.sql > test_psql_deployment_logging.log
>

You need to redirect stderr as well - add "2>&1" to the end of the command.

Ray.



--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie