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?

Have you tried pg_dump? cyberciti.biz/tips/tag/pg-dump-command — sje397
– sje397, Commented Dec 16, 2010 at 10:34
-U is for specifying a username, what purpose does it serve on your command line? — Milen A. Radev
– Milen A. Radev, Commented Dec 16, 2010 at 11:52

Milen A. Radev · Accepted Answer · 2010-12-16 13:38:39Z

16

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.

edited Dec 16, 2010 at 13:38

answered Dec 16, 2010 at 12:07

Milen A. Radev

63.4k22 gold badges111 silver badges113 bronze badges

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

skyeagle Over a year ago

Looks like just what I need. Regarding your other question (-U option on the command line) - Is there a way where I can run these command (say from a bash script), totally automated - i.e. without it prompting me for a password?

skyeagle Over a year ago

Actually, I saw that in the postgre docs (before I asked you). problem is I can't find any example of how to use it. Unless 'using it' simply involves entering details of the user in the .pgpass file?

Collectives™ on Stack Overflow

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

1 Answer 1

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related