If you are really just duplicating the database, not using replication (transactions
performed on both databases more or less simultaneously) this is a very easy thing to
do.
Dump the table defininitions with the MS SQL Server scripting tool. Include the
indexes. Use your favorite scripting language (or do it by hand) to force the whole
thing to lower case, and replace things like IDENTITY with SERIAL and twiddle any
datatypes that need it. Also, they user ALTER TABLE in unsupported ways, like putting
multiple alter statements in a comma separated list. This is an issue since all
referential integrity constraints are created using ALTER rather than in the CREATE
TABLE statement.
Then, create a file containing the table names. Use a handy dandy script to read that
list, and call BCP to dump each table to text. The only gotchas here are that you
have to specify 'keep nulls' and then nulls are "" (empty string), and the fact that
embedded newlines in text fields will booger things up. A handy dandy script can fix
those.
After that, it's as easy as using that same list of table names on the PG box to call
psql with a one line query to COPY FROM ... to load your data. To get the data files
from one box to the other, I use ftp with fget using the same list of table names. I
think this takes care of translating CR/LF as well. At least it did not give ma any
trouble.
I would seriously discourage you from using a kluge like DTS for this. It is beta
quality and will cost you time.
If you love TCL like I do, I can send you the scripts I used.
Ian
Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: [EMAIL PROTECTED]
>>> Joshua Jore <[EMAIL PROTECTED]> 07/21/01 02:47PM >>>
This is a pretty standard task for something like Lotus Enterprise
Integrator. While that package has it's limitations and I curse it
occasionally, it does ok. Either that or just code something up to do the
replication. You could even use a common scripting language <cough perl
cough> and do it simply.
Josh
On Sat, 21 Jul 2001, Richard Huxton wrote:
> From: "Nate Carlson" <[EMAIL PROTECTED]>
>
> > We have a need to replicate a Microsoft SQL server out to a PostgreSQL
> > server. Pretty much, the client uses SQL server right now, and we don't
> > want to expose that to the internet in any way, so we want to set up a
> > Postgres box with the same data at the colo facility. Also helps to have a
> > box you can really admin remotely. :)
> >
> > The data will never be updated on the Postgres box, so it will be a
> > one-way replication.
>
> Not heard of anyone doing this - be interested in hearing how you make out.
>
> MS-SQL <=> MS-SQL replication is probably going to be tricky to hack for
> this situation, but you might be able to do something with Access
> replicating from the MS-SQL server. Link to the Postgres server via ODBC.
> Can't say I've tried it, but that would be my first attempt.
>
> - Richard Huxton
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
>
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
