Thread: catching script errors in PSQL and redirecting to file?

Hi,

I have a problem catching the output of my scripts into the file.

For example I have a set of scripts to create my schema. In psql I just type \i all_tables.sql and
it calls a bunch of scripts - drop script and then individual table creation script.

I have all the output inside my psql (which I am starting with -a), so I can see inside my session
if there are errors.

I have trouble spooling this to the file.

I tried to preceed this with "\o log" and afterwards with "\o" command. But this doesn't work. At
best I am getting in this log file something like

DROP TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
CREATE TABLE
.....

All the details of my statements are not recorded. Only first words. No error message either...
Plus my drop script contains 20 drop statements. In the file it is the only DROP TABLE line at
best.

I tried the second option, i.e. calling psql from the command line redirecting output to the file:

psql -U ... -W all_tables.sql > log

Again not everything is recorded into the log file.

How could I catch the full output of my script (no select commands, but only drop, create,
alter...) into the file?

In my particular case I am recreating my schema on and on from scratch. With other databases I am
used to running one script, checking the output file (for example with grep) for the majic word
'ERROR'. Please help.

Andrerw Lapides,
Calgary, Canada

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Tue, Dec 14, 2004 at 09:25:19AM -0800, Andrew Lapides wrote:

> I tried the second option, i.e. calling psql from the command
> line redirecting output to the file:
>
> psql -U ... -W all_tables.sql > log
>
> Again not everything is recorded into the log file.

Some messages go to standard error, so you'll need to redirect that
as well:

psql ... > log 2>&1    (Bourne shell & friends)
psql ... >& log        (C shell & friends)

Another possibility would be to use the "script" command from the
shell:

script log
psql ...
exit
grep ERROR log

> In my particular case I am recreating my schema on and on from
> scratch. With other databases I am used to running one script,
> checking the output file (for example with grep) for the majic word
> 'ERROR'. Please help.

If you're not interested in NOTICE messages like "CREATE TABLE will
create implicit sequence" then you could use client_min_messages
to filter them out:

SET client_min_messages TO ERROR;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/