Sending errors from psql to error file

[cbbrowne-HInyCGIudOg at public.gmane.org]

> Hey,
> 
> 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 to our new version.  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 and monitor every insert, 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 dataInserts.sql.gz | psql dbname -U username | grep "ERROR:*"
> > import_errors
> 
> (obviously dbname and username have been change to protect the innocent
> ;))
> 
> Any help is appreciated.

Looks like a questionable approach to me.

What I'd do (what I /have done/) is to set up a schema for the 'legacy
data,' and take the following approach:

1.  Extract, from the old system, the existing data, into an
"extraction" schema.

  select [fields] into extraction.t1 from public.t1 where [probably take
     all the data];
  -- And do this for all the tables you want to convert

2.  Dump "extraction" and load it into the new system

  pg_dump -n extraction old_database | psql new_database

3.  Set up selects to take the data from the "extraction" tables and
    stuff them into the new schema...

   select [fields] into public.t1 from extraction.t1;

I actually took it some steps further, where:

 a) The "extraction" used joins and such to make the data look as clean
     as possible, getting rid of any internal pointer-like numbers...

 b) The "insertion" into the new system involved setting up
    stored procedures to create the assorted objects, so that
    I'd do things like:

    select create_domain(domain attributes) 
      from extraction.internet_domains;
    select create_something_else (its attributes) 
      from extraction.something_else;

This approach has the unfortunate quality that as soon as you hit a
"borked" record, it'll crash and burn.  But if you can address
"cleansing" the data well, it'll load it all in one fell swoop :-).

And it keeps the old data handy nearby for reference, _untouched_.
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://www.ntlug.org/~cbbrowne/internet.html
(THASSERT (PLANNER RG))
                -- Example of HACKER statement.
--
The Toronto Linux Users Group.      Meetings: http://tlug.ss.org
TLUG requests: Linux topics, No HTML, wrap text below 80 columns
How to UNSUBSCRIBE: http://tlug.ss.org/subscribe.shtml