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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Sending errors from psql to error file

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



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Sending errors from psql to error file

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Sending errors from psql to error file

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Sending errors from psql to error file

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>&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.

">" 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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Sending errors from psql to error file

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>&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.
>
> ">" 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly