Thread: Moving data from other databases

Moving data from other databases

From
Casey Allen Shobe
Date:
I have a large amount of data in DB2 and Microsoft SQL databases that I would
like to copy to new tables in Postgres, primarily for testing and
proof-of-concept purposes.

I have a Linux/PHP installation supporting all three, and was wondering if PHP
would be a good medium for working this task.

Does anyone have any advice?

Thanks,

--
Casey Allen Shobe / Network Security Analyst & PHP Developer
SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144
cshobe@secureworks.net / http://www.secureworks.net
Content is my own and does not necessarily represent my company.

Re: Moving data from other databases

From
Joe Conway
Date:
Casey Allen Shobe wrote:
> I have a large amount of data in DB2 and Microsoft SQL databases that I would
> like to copy to new tables in Postgres, primarily for testing and
> proof-of-concept purposes.
>
> I have a Linux/PHP installation supporting all three, and was wondering if PHP
> would be a good medium for working this task.

I think that depends on how much data is "a large amount" and how much
patience you have ;-)

Seriously, I think you would be better off using MSSQL's BCP (bulk copy)
program to create a tab delimited file and then use PostgreSQL COPY to
import the data. The process would be something like:

- Use bcp to export data file
- Possibly use sed/awk/your-favorite-text-processing-program to adjust
   the output (only *if* necessary) -- things to watch out for here are
   delimiter characters (e.g. tabs) embedded in you data which need
   escaping of some sort, and NULL values. I can't remember how much
   control BCP gives you in representing nulls, but pgsql COPY allows it
   to be specified.
- Create tables in pgsql; do not create indexes or foreign keys if
   possible at this point.
- Import data using COPY
- Create indexes and refint constraints
- Vacuum analyze

This process should be orders of magnatude faster than querying the data
out, looping through row-by-row, building and executing insert
statements, etc. I suppose you could build the import file using PHP and
then use Postgres COPY, but it would still be more work and alot slower
than using vendor provided export utilities.

I'm not familiar with DB2, but I'd guess it comes with some sort of bulk
export utility also.

HTH,

Joe