psql -o not what I expected (how to output db response to an output file)

Question

I am creating a PostgreSQL database from the command line (i.e. using psql).

There are some errors in my SQL statements and I want to find out where the errors are occuring (too many objects to fill the screen buffer - so I need to save thios to file)

I have tried just about everything, from using the -o option, the -L option and using tee - I still cant capture the information that scrolls past on the screen.

How do I log this?

This is what I have tried so far:

• psql -U -o dbcreate.log -f file.sql
• psql -U -L dbcreate.log -f file.sql
• psql -U -a -f file.sql | tee dbcreate.log

NONE of which results in the data flashing accross the screen being logged to file - how do I do this?

Answer 1

You need to redirect stderr. On Un*x and Linux:

psql ... 2>error.log

or both stdout and stderr:

psql ... &>error.log

On the other hand if you like to investigate the errors one by one:

psql -v ON_ERROR_STOP=1 ...

A helpful article about executing SQL scripts with psql - here.