Thread: Sending errors from psql to error file
Hello, I am trying to migrate a client from one database to another. Basically we designed a web application for them using Postgresql but we have made many changes to the design of our application since version 1. Now they want to upgrade. So basically I have to pg_dump their current data and then import it into our new schema. Now, of course I realize that there are going to be errors. But they have a lot and I mean a lot of data. I don't want to have to sit there and watch the import go by, I want to run a command and then look in a file for any errors after the import is complete. I tried this command but it didn't work: gunzip -c cli_postDataInserts.sql.gz | psql cli_post -U system | grep "ERROR:*" > import_errors Any help is appreciated. Thanks. -- Devin Whalen Programmer Synaptic Vision Inc Phone-(416) 539-0801 Fax- (416) 539-8280 1179A King St. West Toronto, Ontario Suite 309 M6K 3C5 Home-(416) 653-3982
Devin Whalen wrote: > I don't want to have to sit there and watch the import go by, I want to > run a command and then look in a file for any errors after the import is > complete. I tried this command but it didn't work: > gunzip -c cli_postDataInserts.sql.gz | psql cli_post -U system | grep > "ERROR:*" > import_errors Try something like: ... psql cli_post 2>import_errors STDOUT is file-handle 1, STDERR is file-handle 2. You might also want to read up on the "tee" utility. -- Richard Huxton Archonet Ltd
On Thu, 2004-08-12 at 10:50, Oliver Elphick wrote: > On Thu, 2004-08-12 at 15:20, Devin Whalen wrote: > > Hello, > > > > I am trying to migrate a client from one database to another. Basically > > we designed a web application for them using Postgresql but we have made > > many changes to the design of our application since version 1. Now they > > want to upgrade. So basically I have to pg_dump their current data and > > then import it into our new schema. Now, of course I realize that there > > are going to be errors. But they have a lot and I mean a lot of data. > > I don't want to have to sit there and watch the import go by, I want to > > run a command and then look in a file for any errors after the import is > > complete. I tried this command but it didn't work: > > gunzip -c cli_postDataInserts.sql.gz | psql cli_post -U system | grep > > "ERROR:*" > import_errors > > > > Any help is appreciated. > > 1. "didn't work" is not much help Ok sorry. I ran the command and there were errors printed to the screen but they did not get piped into grep and then redirected into the file import_errors. > > 2. Use the --echo-queries and -f options to psql and capture all the > output; a bare error line won't tell you much about what happened nor > where it happened - you need to see what query was running. The -f will > let psql report which line in the input. > > zcat cli_postDataInserts.sql.gz | > psql -d cli_post -U system --echo-queries -f - >trace.file 2>&1 Just a few questions about your command. I tried it with one sql statement that I know doesn't work and the error went into the right file. However, I would like to know WHY it works...hope you don't mind shedding some light on it :). Specifically, the - >trace.file 2>&1 part. I know...well actually think, that the > is redirecting the ouput to the trace.file. But what the hell is 2>&1 doing?? Also, the - ...it kinda just looks like a stray dash to me....although I know it must be doing something ;). Will this put all output? Or just the errors. > Oliver Elphick Thanks for the help BTW, it is a really nice command. -- Devin Whalen Programmer Synaptic Vision Inc Phone-(416) 539-0801 Fax- (416) 539-8280 1179A King St. West Toronto, Ontario Suite 309 M6K 3C5 Home-(416) 653-3982
On Thu, 2004-08-12 at 15:20, Devin Whalen wrote: > Hello, > > I am trying to migrate a client from one database to another. Basically > we designed a web application for them using Postgresql but we have made > many changes to the design of our application since version 1. Now they > want to upgrade. So basically I have to pg_dump their current data and > then import it into our new schema. Now, of course I realize that there > are going to be errors. But they have a lot and I mean a lot of data. > I don't want to have to sit there and watch the import go by, I want to > run a command and then look in a file for any errors after the import is > complete. I tried this command but it didn't work: > gunzip -c cli_postDataInserts.sql.gz | psql cli_post -U system | grep > "ERROR:*" > import_errors > > Any help is appreciated. 1. "didn't work" is not much help 2. Use the --echo-queries and -f options to psql and capture all the output; a bare error line won't tell you much about what happened nor where it happened - you need to see what query was running. The -f will let psql report which line in the input. zcat cli_postDataInserts.sql.gz | psql -d cli_post -U system --echo-queries -f - >trace.file 2>&1 Oliver Elphick
On Thu, 2004-08-12 at 16:01, Devin Whalen wrote: > > zcat cli_postDataInserts.sql.gz | > > psql -d cli_post -U system --echo-queries -f - >trace.file 2>&1 > > Just a few questions about your command. I tried it with one sql > statement that I know doesn't work and the error went into the right > file. However, I would like to know WHY it works...hope you don't mind > shedding some light on it :). Specifically, the - >trace.file 2>&1 > part. I know...well actually think, that the > is redirecting the ouput to the trace.file. But what the hell is 2>&1doing?? > Also, the - ...it kinda just looks like a stray dash to me....although I know it must be doing something ;). > Will this put all output? Or just the errors. ">" redirects standard output "2>" redirects standard error Your command did not redirect standard error, so the errors didn't get to grep The syntax for -f is "-f filename"; the filename "-" means standard input. (Check the man page for psql!) Oliver
On Thu, 2004-08-12 at 11:09, Oliver Elphick wrote: > On Thu, 2004-08-12 at 16:01, Devin Whalen wrote: > > > zcat cli_postDataInserts.sql.gz | > > > psql -d cli_post -U system --echo-queries -f - >trace.file 2>&1 > > > > Just a few questions about your command. I tried it with one sql > > statement that I know doesn't work and the error went into the right > > file. However, I would like to know WHY it works...hope you don't mind > > shedding some light on it :). Specifically, the - >trace.file 2>&1 > > part. I know...well actually think, that the > is redirecting the ouput to the trace.file. But what the hell is 2>&1doing?? > > Also, the - ...it kinda just looks like a stray dash to me....although I know it must be doing something ;). > > Will this put all output? Or just the errors. > > ">" redirects standard output > "2>" redirects standard error > > Your command did not redirect standard error, so the errors didn't get > to grep > > The syntax for -f is "-f filename"; the filename "-" means standard > input. (Check the man page for psql!) > > > Oliver Thanks for you explanations. They are a big help. Now that I understand it better I can modify it to suit my needs. I really only want the errors because if I keep track of all the inserts then the file will be too big. So I just have to use 2>, or rather 2>> so I can append to the file. The syntax for -f is "-f filename"; the filename "-" means standard > input. (Check the man page for psql!) I swear I read it like 15 times and I guess I just scanned over that part every time!! :) Later -- Devin Whalen Programmer Synaptic Vision Inc Phone-(416) 539-0801 Fax- (416) 539-8280 1179A King St. West Toronto, Ontario Suite 309 M6K 3C5 Home-(416) 653-3982
On Thu, 2004-08-12 at 16:09, Oliver Elphick wrote: > "2>" redirects standard error I forgot to say "&1" means the file open on file descriptor 1, which is always standard output. So "2>&1" means send standard error to standard output, so that a pipe (which just takes standard output) can see the errors as well. All that you can find in the man page for bash or sh or whatever your shell is. (If you used csh or tcsh, I think the syntax would be different.) Oliver