pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIs a PS - MSSQL Logical Replication Possible??
8+ messages / 5 participants
[nested] [flat]
* Is a PS - MSSQL Logical Replication Possible??
@ 2023-07-24 02:29 Erik Serrano <eserranos@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Erik Serrano @ 2023-07-24 02:29 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Dear Masters,
Is it possible to make a logical replica between a postgresql 12 and a sql
server 2019?
Technically I want to make a multimaster logical replica to a subscriber
and this in turn replicates to a MSSQL 2019 AlwaysOn server...
Is it possible to make this type of structure or impossible?
Please tell me...
Regards and thank you very much
Atte.
[image: image.png]
*Erik R. Serrano Saavedra*
*Ingeniero de Sistemas Informáticos*
* Data Base Administrator*
* eserranos@gmail.com <eserranos@gmail.com>*
* 998596691*
Attachments:
[image/png] image.png (74.8K, ../../CA+dvXXsVdVmeYAsi-DwgEPEchizTb1BA=DcXkfpCv3VkdNVzzg@mail.gmail.com/3-image.png)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Is a PS - MSSQL Logical Replication Possible??
@ 2023-07-24 12:42 Dan Smith <j.daniel.smith1@gmail.com>
parent: Erik Serrano <eserranos@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Dan Smith @ 2023-07-24 12:42 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
I think what you are looking for is a Change Data Capture (CDC) solution.
This is going to require a system in the middle that can speak to both
database systems. Note these implementations can be brittle (replication
can fail) and take some tinkering to get working well.
I recommend you look in to tools such as Debezium (https://debezium.io),
AWS Data Migration Service (DMS), or maybe some data lake implementation
and scripts could be used (apache hudi, data bricks delta lake, or other).
Best regards,
Dan Smith
On Sun, Jul 23, 2023, 22:30 Erik Serrano <eserranos@gmail.com> wrote:
>
> Dear Masters,
>
> Is it possible to make a logical replica between a postgresql 12 and a sql
> server 2019?
> Technically I want to make a multimaster logical replica to a subscriber
> and this in turn replicates to a MSSQL 2019 AlwaysOn server...
> Is it possible to make this type of structure or impossible?
> Please tell me...
>
> Regards and thank you very much
>
> Atte.
>
>
>
>
> [image: image.png]
>
>
> *Erik R. Serrano Saavedra*
> *Ingeniero de Sistemas Informáticos*
> * Data Base Administrator*
>
> * eserranos@gmail.com <eserranos@gmail.com>*
> * 998596691*
>
Attachments:
[image/png] image.png (74.8K, ../../CAK50Jrz0j8tE3u06O3ACFJBL+0SQDY0Yf+tOd0ESKRuE4GUtPw@mail.gmail.com/3-image.png)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Is a PS - MSSQL Logical Replication Possible??
@ 2023-07-24 21:42 Erik Serrano <eserranos@gmail.com>
parent: Dan Smith <j.daniel.smith1@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Erik Serrano @ 2023-07-24 21:42 UTC (permalink / raw)
To: Dan Smith <j.daniel.smith1@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Yes, I believe that it is not possible to carry out this architecture
without an additional layer or application (which I do not know) exists.
While it only occurs to me to make an unattended replica (Cron or Daemon
linux in origin that generates a flat format dump (.sql) on a specific
share and that windows can pick it up and in turn carry out the load
through an intermediate job), I don't know... I think it could work...
although it is not what I would have liked to do.
In any case, your collaboration is appreciated... and who knows, an issue
will open up to be carried out
greetings friends
Sincerely
*Erik R. Serrano Saavedra*
*Ingeniero de Sistemas Informáticos*
* Data Base Administrator*
* eserranos@gmail.com <eserranos@gmail.com>*
* 998596691*
El lun, 24 jul 2023 a las 8:42, Dan Smith (<j.daniel.smith1@gmail.com>)
escribió:
> I think what you are looking for is a Change Data Capture (CDC) solution.
> This is going to require a system in the middle that can speak to both
> database systems. Note these implementations can be brittle (replication
> can fail) and take some tinkering to get working well.
>
> I recommend you look in to tools such as Debezium (https://debezium.io),
> AWS Data Migration Service (DMS), or maybe some data lake implementation
> and scripts could be used (apache hudi, data bricks delta lake, or other).
>
>
> Best regards,
>
> Dan Smith
>
> On Sun, Jul 23, 2023, 22:30 Erik Serrano <eserranos@gmail.com> wrote:
>
>>
>> Dear Masters,
>>
>> Is it possible to make a logical replica between a postgresql 12 and a
>> sql server 2019?
>> Technically I want to make a multimaster logical replica to a subscriber
>> and this in turn replicates to a MSSQL 2019 AlwaysOn server...
>> Is it possible to make this type of structure or impossible?
>> Please tell me...
>>
>> Regards and thank you very much
>>
>> Atte.
>>
>>
>>
>>
>> [image: image.png]
>>
>>
>> *Erik R. Serrano Saavedra*
>> *Ingeniero de Sistemas Informáticos*
>> * Data Base Administrator*
>>
>> * eserranos@gmail.com <eserranos@gmail.com>*
>> * 998596691*
>>
>
Attachments:
[image/png] image.png (74.8K, ../../CA+dvXXso=++=3TAEdvrJPWEWLOgu2ShB7Ow0ToA6ynyoBBGDcQ@mail.gmail.com/3-image.png)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Is a PS - MSSQL Logical Replication Possible??
@ 2023-07-25 07:31 Ozgur Kulu <kuluozgur@gmail.com>
parent: Erik Serrano <eserranos@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ozgur Kulu @ 2023-07-25 07:31 UTC (permalink / raw)
To: Erik Serrano <eserranos@gmail.com>; +Cc: Dan Smith <j.daniel.smith1@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi there,
Replicating between postgresql instances on different platforms (windows
and linux) but database should be postgres. In other words, you should use
cdc tool like debezium or attunity.
Best Regards,
Ozgur
25 Temmuz 2023 Salı tarihinde Erik Serrano <eserranos@gmail.com> yazdı:
> Yes, I believe that it is not possible to carry out this architecture
> without an additional layer or application (which I do not know) exists.
> While it only occurs to me to make an unattended replica (Cron or Daemon
> linux in origin that generates a flat format dump (.sql) on a specific
> share and that windows can pick it up and in turn carry out the load
> through an intermediate job), I don't know... I think it could work...
> although it is not what I would have liked to do.
>
> In any case, your collaboration is appreciated... and who knows, an issue
> will open up to be carried out
>
> greetings friends
>
> Sincerely
>
>
> *Erik R. Serrano Saavedra*
> *Ingeniero de Sistemas Informáticos*
> * Data Base Administrator*
>
> * eserranos@gmail.com <eserranos@gmail.com>*
> * 998596691*
>
>
> El lun, 24 jul 2023 a las 8:42, Dan Smith (<j.daniel.smith1@gmail.com>)
> escribió:
>
>> I think what you are looking for is a Change Data Capture (CDC)
>> solution. This is going to require a system in the middle that can speak
>> to both database systems. Note these implementations can be brittle
>> (replication can fail) and take some tinkering to get working well.
>>
>> I recommend you look in to tools such as Debezium (https://debezium.io),
>> AWS Data Migration Service (DMS), or maybe some data lake implementation
>> and scripts could be used (apache hudi, data bricks delta lake, or other).
>>
>>
>> Best regards,
>>
>> Dan Smith
>>
>> On Sun, Jul 23, 2023, 22:30 Erik Serrano <eserranos@gmail.com> wrote:
>>
>>>
>>> Dear Masters,
>>>
>>> Is it possible to make a logical replica between a postgresql 12 and a
>>> sql server 2019?
>>> Technically I want to make a multimaster logical replica to a subscriber
>>> and this in turn replicates to a MSSQL 2019 AlwaysOn server...
>>> Is it possible to make this type of structure or impossible?
>>> Please tell me...
>>>
>>> Regards and thank you very much
>>>
>>> Atte.
>>>
>>>
>>>
>>>
>>> [image: image.png]
>>>
>>>
>>> *Erik R. Serrano Saavedra*
>>> *Ingeniero de Sistemas Informáticos*
>>> * Data Base Administrator*
>>>
>>> * eserranos@gmail.com <eserranos@gmail.com>*
>>> * 998596691*
>>>
>>
Attachments:
[image/png] image.png (74.8K, ../../CAF5PJVy8=60ydga=nvPP6WtkOoA-UQ9SFOiowkRNgBv_UdF__A@mail.gmail.com/3-image.png)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Is a PS - MSSQL Logical Replication Possible??
@ 2023-07-25 17:29 Erik Serrano <eserranos@gmail.com>
parent: Ozgur Kulu <kuluozgur@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Erik Serrano @ 2023-07-25 17:29 UTC (permalink / raw)
To: Ozgur Kulu <kuluozgur@gmail.com>; +Cc: Dan Smith <j.daniel.smith1@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
What you indicate is interesting, I am going to find out about the debezium
and attunity programs.
Thank you very much for answering
Greetings
*Erik R. Serrano Saavedra*
*Ingeniero de Sistemas Informáticos*
* Data Base Administrator*
* eserranos@gmail.com <eserranos@gmail.com>*
* 998596691*
El mar, 25 jul 2023 a las 3:32, Ozgur Kulu (<kuluozgur@gmail.com>) escribió:
> Hi there,
>
> Replicating between postgresql instances on different platforms (windows
> and linux) but database should be postgres. In other words, you should use
> cdc tool like debezium or attunity.
>
> Best Regards,
> Ozgur
>
> 25 Temmuz 2023 Salı tarihinde Erik Serrano <eserranos@gmail.com> yazdı:
>
>> Yes, I believe that it is not possible to carry out this architecture
>> without an additional layer or application (which I do not know) exists.
>> While it only occurs to me to make an unattended replica (Cron or Daemon
>> linux in origin that generates a flat format dump (.sql) on a specific
>> share and that windows can pick it up and in turn carry out the load
>> through an intermediate job), I don't know... I think it could work...
>> although it is not what I would have liked to do.
>>
>> In any case, your collaboration is appreciated... and who knows, an issue
>> will open up to be carried out
>>
>> greetings friends
>>
>> Sincerely
>>
>>
>> *Erik R. Serrano Saavedra*
>> *Ingeniero de Sistemas Informáticos*
>> * Data Base Administrator*
>>
>> * eserranos@gmail.com <eserranos@gmail.com>*
>> * 998596691*
>>
>>
>> El lun, 24 jul 2023 a las 8:42, Dan Smith (<j.daniel.smith1@gmail.com>)
>> escribió:
>>
>>> I think what you are looking for is a Change Data Capture (CDC)
>>> solution. This is going to require a system in the middle that can speak
>>> to both database systems. Note these implementations can be brittle
>>> (replication can fail) and take some tinkering to get working well.
>>>
>>> I recommend you look in to tools such as Debezium (https://debezium.io),
>>> AWS Data Migration Service (DMS), or maybe some data lake implementation
>>> and scripts could be used (apache hudi, data bricks delta lake, or other).
>>>
>>>
>>> Best regards,
>>>
>>> Dan Smith
>>>
>>> On Sun, Jul 23, 2023, 22:30 Erik Serrano <eserranos@gmail.com> wrote:
>>>
>>>>
>>>> Dear Masters,
>>>>
>>>> Is it possible to make a logical replica between a postgresql 12 and a
>>>> sql server 2019?
>>>> Technically I want to make a multimaster logical replica to a
>>>> subscriber and this in turn replicates to a MSSQL 2019 AlwaysOn server...
>>>> Is it possible to make this type of structure or impossible?
>>>> Please tell me...
>>>>
>>>> Regards and thank you very much
>>>>
>>>> Atte.
>>>>
>>>>
>>>>
>>>>
>>>> [image: image.png]
>>>>
>>>>
>>>> *Erik R. Serrano Saavedra*
>>>> *Ingeniero de Sistemas Informáticos*
>>>> * Data Base Administrator*
>>>>
>>>> * eserranos@gmail.com <eserranos@gmail.com>*
>>>> * 998596691*
>>>>
>>>
Attachments:
[image/png] image.png (74.8K, ../../CA+dvXXv7Q9heXqMeX9pGRL9MR-=TnGYKMdpkDH+6U0OyXdp51Q@mail.gmail.com/3-image.png)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Is a PS - MSSQL Logical Replication Possible??
@ 2023-07-25 17:42 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Erik Serrano <eserranos@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Ribe @ 2023-07-25 17:42 UTC (permalink / raw)
To: Erik Serrano <eserranos@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Jul 25, 2023, at 11:29 AM, Erik Serrano <eserranos@gmail.com> wrote:
>
> What you indicate is interesting, I am going to find out about the debezium and attunity programs.
> Thank you very much for answering
I don't know how those two are implemented--but for background on what is possible, you might want to read up on PostgreSQL logical replication slots. Essentially, one can write a plug-in which has access to a serialized stream of all updates being applied. There are examples which exist. So one could write such a thing that pushes to MSSQL, or one could use such a thing which publishes change stream as JSON, and develop something to push this to MSSQL. Not suggesting that *you* have to this--just that it's likely tools are based on this, and it would be good for your understanding.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Is a PS - MSSQL Logical Replication Possible??
@ 2023-07-25 18:54 Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 8+ messages in thread
From: Wetmore, Matthew (CTR) @ 2023-07-25 18:54 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; Erik Serrano <eserranos@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Correct, and with dblink on logical slots. The gotcha is making the logical slots persistent in your network config.
I have done db link from one table to another on my primary and then use that table as the source for processing data or whatever.
But you will have to do custom work.
The most annoying part of this is the MSSQL ::Postgres inconsistencies such as date, numeric and float , bit::bool types.
-----Original Message-----
From: Scott Ribe <scott_ribe@elevated-dev.com>
Sent: Tuesday, July 25, 2023 10:42 AM
To: Erik Serrano <eserranos@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: [EXTERNAL] Re: Is a PS - MSSQL Logical Replication Possible??
> On Jul 25, 2023, at 11:29 AM, Erik Serrano <eserranos@gmail.com> wrote:
>
> What you indicate is interesting, I am going to find out about the debezium and attunity programs.
> Thank you very much for answering
I don't know how those two are implemented--but for background on what is possible, you might want to read up on PostgreSQL logical replication slots. Essentially, one can write a plug-in which has access to a serialized stream of all updates being applied. There are examples which exist. So one could write such a thing that pushes to MSSQL, or one could use such a thing which publishes change stream as JSON, and develop something to push this to MSSQL. Not suggesting that *you* have to this--just that it's likely tools are based on this, and it would be good for your understanding.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Is a PS - MSSQL Logical Replication Possible??
@ 2023-07-29 19:40 Erik Serrano <eserranos@gmail.com>
parent: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
0 siblings, 0 replies; 8+ messages in thread
From: Erik Serrano @ 2023-07-29 19:40 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Dear all,
Yes, I see that there is a small possibility of realizing this
architecture, however I also realize that the consistency is put at risk
and that the HH are high. I am going to deliver a report giving the pros
and cons of this architecture.
Thank you all very much for your contributions.
Erik Serrano
El mar., 25 de julio de 2023 2:54 p. m., Wetmore, Matthew (CTR) <
Matthew.Wetmore@express-scripts.com> escribió:
> Correct, and with dblink on logical slots. The gotcha is making the
> logical slots persistent in your network config.
>
> I have done db link from one table to another on my primary and then use
> that table as the source for processing data or whatever.
>
> But you will have to do custom work.
>
> The most annoying part of this is the MSSQL ::Postgres inconsistencies
> such as date, numeric and float , bit::bool types.
>
> -----Original Message-----
> From: Scott Ribe <scott_ribe@elevated-dev.com>
> Sent: Tuesday, July 25, 2023 10:42 AM
> To: Erik Serrano <eserranos@gmail.com>
> Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> Subject: [EXTERNAL] Re: Is a PS - MSSQL Logical Replication Possible??
>
> > On Jul 25, 2023, at 11:29 AM, Erik Serrano <eserranos@gmail.com> wrote:
> >
> > What you indicate is interesting, I am going to find out about the
> debezium and attunity programs.
> > Thank you very much for answering
>
> I don't know how those two are implemented--but for background on what is
> possible, you might want to read up on PostgreSQL logical replication
> slots. Essentially, one can write a plug-in which has access to a
> serialized stream of all updates being applied. There are examples which
> exist. So one could write such a thing that pushes to MSSQL, or one could
> use such a thing which publishes change stream as JSON, and develop
> something to push this to MSSQL. Not suggesting that *you* have to
> this--just that it's likely tools are based on this, and it would be good
> for your understanding.
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-07-29 19:40 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-24 02:29 Is a PS - MSSQL Logical Replication Possible?? Erik Serrano <eserranos@gmail.com>
2023-07-24 12:42 ` Dan Smith <j.daniel.smith1@gmail.com>
2023-07-24 21:42 ` Erik Serrano <eserranos@gmail.com>
2023-07-25 07:31 ` Ozgur Kulu <kuluozgur@gmail.com>
2023-07-25 17:29 ` Erik Serrano <eserranos@gmail.com>
2023-07-25 17:42 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-07-25 18:54 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-07-29 19:40 ` Erik Serrano <eserranos@gmail.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