how to save sql command results into file(postgresql)

Question

I have a large batch of of comprising over 300000 insert commands in PostgreSQL. I can not even load it into pgAdmin (it crashes). so I am using the command line and \i to execute the file. I'd like to see the error messages (if any). How may I save the messages into a log file through command line? appreciate your help thank you

Edit I have already seen some other posts but couldn't help.

300000 insert commands? Wouldn't it be easier to read the data from a file? — wildplasser
– wildplasser, Commented Apr 9, 2012 at 9:39
@wildplasser :) I have just converted an XML file into insert commands to populate a DB. that is what the employer requires: a database! "read the data from a file"? are you suggesting another solution to get it done? please help. thank you — rahman
– rahman, Commented Apr 9, 2012 at 9:43
If the XML consists if only one (or a few) rectangular data-tables, it could be transformed to .csv (or .tsv) files relatively easy. (easier than converting it into insert statements, IMHO) — wildplasser
– wildplasser, Commented Apr 9, 2012 at 9:46
I had already considered CSV, but ,actually I couldn't do that. However, it is still important for me if it is possible. So, what to you mean by rectangular data-tables? if you mean not hierarchichal, No, the data is hierarchical(nested). Still possible? what are the tools? thanks — rahman
– rahman, Commented Apr 9, 2012 at 9:50
I meant rectangular in the sense of (a collection of) rows*columns data. Just plain table data. If you mean hierarchical in the sense of "denormalised" you would be in trouble. If you only mean (FK) relations between tables, it's Ok. — wildplasser
– wildplasser, Commented Apr 9, 2012 at 9:58

wildplasser · Accepted Answer · 2012-04-09 10:14:14Z

2

From the unix-prompt: (windows may have a similar mechanism).

psql -U my_username my_databasename < the_script.sql >the_output.log

UPDATE: The above command assumes that the user that executes it is able to read "the_script.sql" and that the user can write to "the_output.log" (in this case in the current directory) If you can't write there for some reason, you could always write to /tmp, as in psql -U my_username my_databasename < the_script.sql >/tmp/other_output.log

edited Apr 9, 2012 at 10:14

answered Apr 9, 2012 at 9:41

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

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

8 Comments

rahman Over a year ago

I am logged in with my own account(vahid) and database owner is postgres. so the command looks like this: vahid@vahid-ThinkPad-T420:~/workspace/MATSIM$ psql -U postgres MATEST < testing.sql > ./res.txt psql: FATAL: Peer authentication failed for user "postgres"

rahman Over a year ago

doing a sudo didn't change anything

rahman Over a year ago

changing the output file owner(and middle directories) to postgres also yields another problem:$ psql -U postgres MATEST < testing.sql > /sqlresult/res.txt bash: /sqlresult/res.txt: Permission denied Any Idea? thanks

rahman Over a year ago

changing chmod the permission of output file sudo chmod 777 /sqlresult/res.txt gets me back to the original problem: FATAL: Peer authentication failed for user "postgres"

wildplasser Over a year ago

Pleas update your question, and show us exactly which command(s) you used. "doing a sudo didn't change anything" is not very informative.

|

CSchulz · Accepted Answer · 2016-06-13 13:52:31Z

0

psql -e -f file_name db_name 2>&1 > log.txt

most of the interesting logs you will find are sent to stderr from psql, so redirecting stderr to stdout and then put them in a log.txt file should work.

edited Jun 13, 2016 at 13:52

CSchulz

11.1k11 gold badges63 silver badges117 bronze badges

answered Jun 13, 2016 at 13:14

James Doherty

1,4211 gold badge9 silver badges7 bronze badges

Collectives™ on Stack Overflow

how to save sql command results into file(postgresql)

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related