1

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.

Community
  • 1
  • 1
rahman
  • 4,820
  • 16
  • 52
  • 86
  • 300000 insert commands? Wouldn't it be easier to read the data from a file? – wildplasser Apr 09 '12 at 09: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 Apr 09 '12 at 09:43
  • 1
    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 Apr 09 '12 at 09: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 Apr 09 '12 at 09: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 Apr 09 '12 at 09:58
  • I have similar use cases where I have to import (and merge with existing data) large XML files on a regular basis. You can do it all in PostgreSQL, without *any* preparation. [I recently posted a complete working example](http://stackoverflow.com/a/7628453/939860). – Erwin Brandstetter Apr 09 '12 at 18:28

2 Answers2

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

wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • 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 Apr 09 '12 at 09:55
  • doing a sudo didn't change anything – rahman Apr 09 '12 at 09:56
  • 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 Apr 09 '12 at 09:58
  • 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" – rahman Apr 09 '12 at 10:00
  • That is "only" an authorisation issue. Normally an installation does mot allow postgres to connect from remote. Connections from the local machine should always be possible in some way (sometimes via strange constructs, such as other usernames inheriting the postgres ROLE) NB The "bash: /sqlresult/res.txt: Permission denied" message comes from the shell that is not allowed to write into that directory. – wildplasser Apr 09 '12 at 10:04
  • how may I get aroung this issue? – rahman Apr 09 '12 at 10:17
  • 1
    Pleas update your question, and show us exactly which command(s) you used. "doing a sudo didn't change anything" is not very informative. – wildplasser Apr 09 '12 at 10:26
  • @rahman If you are using peer authentication then you should login as postgres before issuing the command. I think the best is to use md5 authentication and optionally create a .pgpass file if you want to skip the password dialog. – Clodoaldo Neto Apr 09 '12 at 11:51
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.

CSchulz
  • 10,882
  • 11
  • 60
  • 114
James Doherty
  • 1,271
  • 1
  • 8
  • 6