Replicating from MS SQL Ser...

boreddizzyData Management

Dec 16, 2012 (5 years and 21 days ago)

360 views

Replicating from MS SQL Server to PostgreSQL
Magnus Hagander's PostgreSQL Blog
Monday, April 10. 2006
Replicating from MS SQL Server to PostgreSQL
There can be many reasons for wanting to replicate your data from a MS SQL Server
installation to your PostgreSQL installation. For example, as a step in migration or to
be able to use PostgreSQL features for data analysis while not having to touch existing
clients working with MSSQL. For me in this case, I wanted to use tsearch2 to search some
fulltext data, because the fulltext indexer in SQL Server really isn't very good.
It turns out that SQL Server ships with replication functionality that can solve this problem
with relatively little pain (depending on your schema of course), providing full transactional
replication. It's master/slave only, and SQL Server will be the master, but it's still quite
useful.
Here are the steps to do this for a simple example database - should work for more
complex database as well of course. It expects you to set up a user named sqlrepl in the
PostgreSQL database, that the replication system will use to connect with. Make sure that
this user has permissions to connect from the SQL Server machine in pg_hba.conf.
Make sure you have the PostgreSQL ODBC drivers installed on the SQL Server
machine (I'm using version 8.01.02).
Create the example databases:
In SQL Server:
CREATE DATABASE origin
go
USE origin
go
CREATE TABLE tab1(
id int identity not null primary key,
t varchar(128) not null)
INSERT INTO tab1 (t) VALUES ('Test 1')
INSERT INTO tab1 (t) VALUES ('Test 2')
Then, in PostgreSQL:
CREATE DATABASE slave OWNER sqlrepl;
\connect slave
CREATE TABLE tab1(id int not null primary key, t varchar(128) not null);
ALTER TABLE tab1 OWNER TO sqlrepl;
Create a
ODBC
System
Datasource
on the SQL
Server. This
is done using
the odbcad32
command.
Make sure
that you
create a
system
datasource,
and make
sure you use
the PostgreSQL ANSI driver (there are some problems with the UNICODE driver in
the way SQL Server uses it)
Archives
November 2007
October 2007
September 2007
Recent...
Older...
Calendar
November '07
Mon Tue Wed Thu Fri Sat Sun
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30
Quicksearch
Links
Personal home page
Talks I've given
PostgreSQL
Planet PostgreSQL
pgInstaller
Slony-I
Syndicate This Blog

RSS 2.0 feed

ATOM 0.3 feed

ATOM 1.0 feed

RSS 2.0 Comments
Latest comments
Magnus about
Database or
schema
Mon 2007-11-05 00:18
As of SQL Server 2005, probably
about the same level. I haven't
investigated details, but from the
[...]
Hans Olav Norheim about
Database or schema
Sun 2007-11-04 16:09
So would you still say that
schemas in PostgreSQL are much
more flexible than SQL Servers',
or are [...]
Magnus about
Database or
schema
Sun 2007-11-04 13:41
What you're saying is certainly
correct - they have made that
change. Traditionally it's been
that [...]
Hans Olav Norheim about
Database or schema
Sun 2007-11-04 13:22
You say that "...in say SQL
Server, where a schema and a
user is pretty much the same
thing". It [...]
Replicating from MS SQL Server to PostgreSQL - Magnus Hagander's P...http://web.archive.org/web/20071111020218/http://people.planetpostgr...
1 z 5 2009-10-24 11:14
Start SQL Server
Enterprise Manager.
Create a new linked
server. This is done by
right-clicking the
Linked Servers node
under Security and
picking New Linked
Server. Enter the name
of the linked server (in
all uppercase, in our
case PGSLAVE), and
pick the driver
Microsoft OLE DB
Provider for ODBC
Drivers. Note that you
should not pick the
PostgreSQL ODBC
driver here. Finally,
enter the name of the
ODBC datasource just
created. Make sure the
link works by clicking
the Tables node and
verify that you can see
the tables of you
database.
Configure the subscriber:
Right-click on the Replication node and select Configure publishing,
subscribers, and distribution.
Select the tab Subscribers
Click New
Select OLE DB data source
Pick the linked server you created (PGSLAVE). Re-enter the login information.
Click OK and close all dialogs
Create the publication:
Right-click Publication under Replication and select New publication
Select your database, click Next
Select Transactional publication, click Next
Uncheck SQL Server 2000 and check Heterogeneous data sources, click Next
Click Article Defaults
Open the Snapshot tab
Change name conflicts to Keep existing table unchanged. In some cases
it will work with drop and recreate, but I prefer creating the tables
manually to make sure there is no mixup with datatypes and such
(considering MSSQL doesn't really know about PostgreSQL datatypes)
Click OK
Put a checkbox on the tables to replicate (tab1 in this example). Click Next
You will get a warning about IDENTITY properties not being replicated to
subscribers. This will happen if you have any IDENTITY columns in your table.
In most cases, you can just ignore it. Click Next
Possibly modify description if you want to, click Next
Click next through the rest of the Wizard, and click Finish
Create the subscription:
Magnus about
Database or
schema
Sat 2007-11-03 09:50
You could do that, but since all
the clients connect through
appservers running on the same
machine [...]
Blog Administration
Open login screen
Replicating from MS SQL Server to PostgreSQL - Magnus Hagander's P...http://web.archive.org/web/20071111020218/http://people.planetpostgr...
2 z 5 2009-10-24 11:14
Open properties for the publication
Open the Subscriptions tab
Click Push new
Select the created subscriber (PGSLAVE)
Click Next through the rest of the wizard, make sure to check the box for Start
snapshot agent
Now sit back and watch your MSSQL data first being bulk-loaded into PostgreSQL,
and then transactionally replicated (you will see a couple of seconds delay after a
commit, same as when you replicate between two SQL Servers).
If you have more advanced needs (which you often do), you can use most other features of
SQL Server replication, such as row and column filtering.
Posted by
Magnus Hagander in
PostgreSQL at
04:40 |
Comments (13) |
Trackbacks (0)
Vote for articles fresher than 7 days!
Current karma: 0, 0 vote(s) 6926 hits
Trackbacks
Trackback specific URI for this entry
No Trackbacks
Comments
Display comments as (
Linear | Threaded)
Very interesting writing, I've always used DTS for this kind of things, but I'll definitely
take a look at this solution.
Thanks
#1
pabloj (
Homepage) on 2006-04-11 10:13 (
Reply)
Followed the instructions, but it doesn't seem to work. SQL Server 2k seems to be
sending bad data to PG. Any thoughts?
ERROR: 42601: unterminated quoted string at or near "'\" at character 125
LOCATION: yyerror, scan.l:770
STATEMENT: insert into MSrepl7 values (E'NTSO', E'Sdata', E'', 0, 0, E'NTSO-Sdata-
CMSLAVE-12', '2006-08-01 20:01:08'::timestamp, NULL, '\
#2
Jonathan Bailey (
Homepage) on 2006-08-01 18:07 (
Reply)
Certainly looks that way Never seen that one happen. I wonder if it has something
to do with an updated ODBC driver (or server) related to the escaping fixes. IIRC, the
E' stuff was added not too long ago. Could you try with an older ODBC driver, just to
see if it goes away?
#2.1 Magnus on 2006-08-15 02:07 (
Reply)
I went back to the stable driver and all is fine now. The E' escaping is a Postgres
8.1 thing..
#2.1.1
Jonathan Bailey (
Homepage) on 2006-08-21 12:25 (
Reply)
Does this work with mssql 2005?
#3
bert on 2006-08-06 21:57 (
Reply)
Don't know, haven't tested it.
#3.1 Magnus on 2006-08-15 02:10 (
Reply)
There are significant ways in which MS SQL 2005 is set up in relation to creating
linked servers and configuring ODBC drivers. I haven't figured it all out but I'm
working on it. Anyone who is more MS SQL 2005 clueful who can offer some pointers
would be greatly appreciated.
#3.2
Gary on 2006-10-17 16:44 (
Reply)
Replicating from MS SQL Server to PostgreSQL - Magnus Hagander's P...http://web.archive.org/web/20071111020218/http://people.planetpostgr...
3 z 5 2009-10-24 11:14
it does work. i created a linked server as described in this article and then executed a
command that looks like this:
select * from openquery(GANYMEDE, 'select id::int, name::varchar(32) from
public.blah')
and magic happened
#3.3
Sergei Kachanov on 2006-11-22 11:12 (
Reply)
I does work, but does any know how to replicate more db than one, to same postgres
database? I need to get working interdatabase joins :o(
replication of second db gets error like msrepl7 exists, thanks
#4
Honzucha on 2006-11-24 11:31 (
Reply)
Hi,
This is a very interesting and usefule knowledge you have shared and this helps a lot of
my queries. I assume this configuration is for SQL server 2000.
Can you please help me know if the same type of configuration is possible between
PostgreSQL & SQL Server 2005?? If yes, please share some of your findings.
Thanks
Mandy
#5
Mandeep on 2006-12-18 23:25 (
Reply)
I haven't tried on SQL 2005, sorry. I assume you have to do some things differently,
but in principle I don't see why it shuoldn't be possible to make it work.
#5.1 Magnus on 2006-12-19 00:21 (
Reply)
The only problem is after creating Linked Server in SQL Server 2005, it doesn't
show the objects (tab1) associated with PGSLAVE (in this case).
Even if I try to query it, I get message saying that the object is not exposed.
Any suggestions??
#5.1.1
Mandeep on 2006-12-27 05:13 (
Reply)
I have been trying to replicate from Sql Server to Postgres using Enterprise Manager
and the Postgres ANSI ODBC driver version 8.02.02.
I was able to copy tables over when I used the article option to "DROP existing table and
re-create it" only columns of data types datetime and decimal were copied as varchar.
So I tried drop the tables manually and then creating them in postgres with the proper
data types (timestamp without time zone, and numeric). I then tried changing the
article option to "Keep the existing table unchanged" and I get the following error from
the Distribution agent: Multiple-step OLE DB operation generated errors. Check each
OLE DB status value, if available. No work was done.
So I then tried using the article snapshot option "Delete all data in the existing table"
and I got the same error.
Anyone got any suggestions or comments?
#6
Zac Kloepping on 2007-03-26 14:51 (
Reply)
Add Comment
Name
Email
Homepage
In reply to
Replicating from MS SQL Server to PostgreSQL - Magnus Hagander's P...http://web.archive.org/web/20071111020218/http://people.planetpostgr...
4 z 5 2009-10-24 11:14
Comment
Standard emoticons like :-) and ;-) are converted to images.
To prevent automated Bots from commentspamming, please enter the string
you see in the image below in the appropriate input box. Your comment will
only be submitted if the strings match. Please ensure that your browser
supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA
Enter the string from the spam-prevention image above:

Remember Information?
Subscribe to this entry

Replicating from MS SQL Server to PostgreSQL - Magnus Hagander's P...http://web.archive.org/web/20071111020218/http://people.planetpostgr...
5 z 5 2009-10-24 11:14