pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedMoving data from other databases
2+ messages / 2 participants
[nested] [flat]
* Moving data from other databases
@ 2002-08-28 05:07 Casey Allen Shobe <cshobe@secureworks.net>
0 siblings, 1 reply; 2+ messages in thread
From: Casey Allen Shobe @ 2002-08-28 05:07 UTC (permalink / raw)
To: pgsql-php
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Moving data from other databases
@ 2002-08-28 05:27 Joe Conway <mail@joeconway.com>
parent: Casey Allen Shobe <cshobe@secureworks.net>
0 siblings, 0 replies; 2+ messages in thread
From: Joe Conway @ 2002-08-28 05:27 UTC (permalink / raw)
To: Casey Allen Shobe <cshobe@secureworks.net>; +Cc: pgsql-php
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-08-28 05:27 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-28 05:07 Moving data from other databases Casey Allen Shobe <cshobe@secureworks.net>
2002-08-28 05:27 ` Joe Conway <mail@joeconway.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox