Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rpuBR-009PQL-BF for pgsql-admin@arkaria.postgresql.org; Thu, 28 Mar 2024 18:09:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rpuBQ-001HCU-Bu for pgsql-admin@arkaria.postgresql.org; Thu, 28 Mar 2024 18:09:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rpuBP-001HCM-VU for pgsql-admin@lists.postgresql.org; Thu, 28 Mar 2024 18:09:00 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rpuBM-0072Qs-Rn for pgsql-admin@lists.postgresql.org; Thu, 28 Mar 2024 18:08:59 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 9B51D380795; Thu, 28 Mar 2024 19:08:55 +0100 (CET) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id uk1mdWSMI2m5; Thu, 28 Mar 2024 19:08:55 +0100 (CET) Received: from [192.168.234.86] (dynamic-046-114-224-003.46.114.pool.telefonica.de [46.114.224.3]) by rs.plausibolo.de (Postfix) with ESMTPSA id 194993803B8; Thu, 28 Mar 2024 19:08:55 +0100 (CET) Message-ID: Date: Thu, 28 Mar 2024 19:08:53 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.12.0 Content-Language: de-DE To: pgsql-admin@lists.postgresql.org References: From: Holger Jakobs Subject: Re: Mssql to postgres migration In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------dAKPbKsmXpPsdmVYsCJnM44i" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --------------dAKPbKsmXpPsdmVYsCJnM44i Content-Type: multipart/mixed; boundary="------------9e2DGlyYi8JaudOOXzu2xakR"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: Subject: Re: Mssql to postgres migration References: In-Reply-To: --------------9e2DGlyYi8JaudOOXzu2xakR Content-Type: multipart/alternative; boundary="------------yNGZWLEY5qVwQIk9hgmIxukq" --------------yNGZWLEY5qVwQIk9hgmIxukq Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 QW0gMjguMDMuMjQgdW0gMTg6NTcgc2NocmllYiBSYWplc2ggS3VtYXI6DQo+IEhpDQo+DQo+ IElzIGl0IHBvc3NpYmxlIHRvIG1pZ3JhdGUgZnJvbSBtc3NxbCB0byBwb3N0Z3Jlcz8NCj4N Cj4gQW55IGxpbmtzIGF2YWlsYWJsZT8NCj4NCj4gQ2FuIEkgc2V0dXAgcG9zdGdyZXMgREEg bGlrZSBsb2NhdGlvbjE6IDEgbWFzdGVyIGFuZCAxIHNsYXZlIGFuZCANCj4gbG9jYXRpb24y OiAxIG1hc3RlciBhbmQgMSBzbGF2ZSA/DQo+DQo+DQo+IElzIHRoZXJlIGEgYmV0dGVyIHdh eSB0byBzZXQgdXAgREE/DQo+DQo+IEkgYW0gdmVyeSB2ZXJ5IG5ldyB0byB0aGVzZSB0b3Bp Y3MuLkkgaGF2ZSBzZWFyY2hlZCBlbm91Z2ggaW4gDQo+IGludGVybmV0LiBDb3VsZCBub3Qg ZmluZCB0aGUgcmlnaHQgc291cmNlLg0KDQpZZXMsIG9mIGNvdXJzZSBhIG1pZ3JhdGlvbiBp cyBwb3NzaWJsZS4gQW55IG1pZ3JhdGlvbiBpcy4NCg0KVW5mb3J0dW5hdGVseSwgdGhlcmUg YXJlIGxlc3MgdG9vbHMgYXZhaWxhYmxlIGNvbXBhcmVkIHRvIHRoZSBtaWdyYXRpb24gDQpm cm9tIE9yYWNsZS4gSnVzdCBoYXZlIGEgbG9vayBhdCB0aGUgZm9yZWlnbiBkYXRhIHdyYXBw ZXJzIGZvciB0aGVzZSANCnN5c3RlbXMuIFRoZSBPcmFjbGUgRkRXIGlzIHdlbGwgbWFpbnRh aW5lZCwgZG9jdW1lbnRlZCBhbmQgaGFzIG1hbnkgDQpmZWF0dXJlcy4gVGhlIE1TIFNRTCBG RFcgaXMgb2xkLCBub3Qgd2VsbCBkb2N1bWVudGVkIGFuZCBpcyBsYWNraW5nIG1hbnkgDQpm ZWF0dXJlcy4gSXQgY2FuJ3QgZXZlbiB3cml0ZSBvciBwdXNoIGRvd24gYW55dGhpbmcuDQoN ClNvbWUgdG9vbCB0aGF0IG1pZ2h0IGhlbHAgYXJlOg0KDQogICogcGdMb2FkZXINCiAgKiBz cWxzZXJ2ZXIycGdzcWwNCg0KTSQgcGVvcGxlIGxpa2UgdG8gY2FsbCB0aGUgcHJvZHVjdCAi TWljcm9zb2Z0IFNRTCBTZXJ2ZXIiIGp1c3QgIlNRTCANClNlcnZlciIsIGFzIGlmIGl0IHdh cyBUSEUgU1FMIFNlcnZlciBhbmTCoCBQb3N0Z3JlU1FMLCBPcmFjbGUgb3IgREIyIA0Kd2Vy ZW4ndCBTUUwgU2VydmVycy4NCg0KVGhlcmUgaXMgYSBTY2hlbWEgQ29udmVyc2lvbiBUb29s IGJ5IEFXUyBpbiBjYXNlIHlvdSB3YW50IHRvIG1pZ3JhdGUgdG8gDQpQb3N0Z3JlU1FMIG9u IEFXUy4gTWF5YmUgaXQncyBwb3NzaWJsZSB0byBnbyB0aGlzIHdheSBhbmQgdGhlbiBjb250 aW51ZSANCnRvIG9uLXByZW1pc2UgUG9zdGdyZVNRTC4NCg0KV2l0aCBQb3N0Z3JlU1FMIHlv dSBhbHdheXMgaGF2ZSAxIFByaW1hcnkgKGZvcm1lcmx5IGNhbGxlZCBNYXN0ZXIpIGFuZCAN CmFueSBudW1iZXIgb2YgU2Vjb25kYXJpZXMgKGZvcm1lcmx5IGNhbGxlZCBTbGF2ZXMpLiBT byBubywgeW91IGNhbm5vdCANCmhhdmUgdHdvIFByaW1hcmllcywgd2hldGhlciBpbiBhIHNp bmdsZSBvciBpbiB0d28gc2VwYXJhdGUgbG9jYXRpb25zLg0KDQpLZWVwaW5nIHNldmVyYWwg UHJpbWFyaWVzIGNvbXBsZXRlbHkgaW4gU3luYyBhbmQgY29udGludW91c2x5IA0KbWFpbnRh aW5pbmcgQUNJRCBtYWtlcyBldmVyeXRoaW5nIHNvIHNsb3cgdGhhdCBpdCBpcyBoYXJkbHkg YSBzZW5zaWJsZSANCnNvbHV0aW9uLg0KDQoiTXVsdGktTWFzdGVyIiBwcm9taXNlcyBhIGxv dCwgYnV0IGtlZXBzIGxpdHRsZS4NCg0KS2luZCBSZWdhcmRzLA0KDQpIb2xnZXINCg0KLS0g DQoNCkhvbGdlciBKYWtvYnMsIEJlcmdpc2NoIEdsYWRiYWNoLCBUZWwuICs0OS0xNzgtOTc1 OTAxMg0KDQpjaGF0IHdpdGggbWUgb24gU2lnbmFsIG1lc3NlbmdlciANCjxodHRwczovL3Np Z25hbC5tZS8jZXUvR3BJUDI1OWJCbmVfSmlCWUpGdmJaQ0xZSkVMaEItOTJuSmZuY0FDTDFM VWVCT3BoZlhGRmdMWVlWWHZfQUdoZz4NCg0K --------------yNGZWLEY5qVwQIk9hgmIxukq Content-Type: multipart/related; boundary="------------XGybWdxHnlNd792u04QOSDdg" --------------XGybWdxHnlNd792u04QOSDdg Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Am 28.03.24 um 18:57 schrieb Rajesh Kumar:
Hi

Is it possible to migrate from mssql to postgres?=C2=A0

Any links available?

Can I setup postgres DA like location1: 1 maste= r and 1 slave and location2: 1 master and 1 slave ?


Is there a better way to set up DA?

I am very very new to these topics..I have searched enough in internet. Could not find the right source.

Yes, of course a migration is possible. Any migration is.

Unfortunately, there are less tools available compared to the migration from Oracle. Just have a look at the foreign data wrappers for these systems. The Oracle FDW is well maintained, documented and has many features. The MS SQL FDW is old, not well documented and is lacking many features. It can't even write or push down anything.

Some tool that might help are:

  • pgLoader
  • sqlserver2pgsql

M$ people like to call the product "Microsoft SQL Server" just "SQL Server", as if it was THE SQL Server and=C2=A0 PostgreSQL, Ora= cle or DB2 weren't SQL Servers.

There is a Schema Conversion Tool by AWS in case you want to migrate to PostgreSQL on AWS. Maybe it's possible to go this way and then continue to on-premise PostgreSQL.

With PostgreSQL you always have 1 Primary (formerly called Master) and any number of Secondaries (formerly called Slaves). So no, you cannot have two Primaries, whether in a single or in two separate locations.

Keeping several Primaries completely in Sync and continuously maintaining ACID makes everything so slow that it is hardly a sensible solution.

"Multi-Master" promises a lot, but keeps little.

Kind Regards,

Holger

--

Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
3D""
chat with me on Signal messenger

--------------XGybWdxHnlNd792u04QOSDdg Content-Type: image/png; name="SkJ8gBgoAxb5WfIM.png" Content-Disposition: inline; filename="SkJ8gBgoAxb5WfIM.png" Content-Id: Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAAIcAAACHAQMAAAAGKdhJAAAABlBMVEUAAAD///+l2Z/dAAAA AnRSTlP//8i138cAAAAJcEhZcwAACxIAAAsSAdLdfvwAAAG5SURBVEiJ3da7bcMwEAbgM1iw kxYQwDXYcSVpAT0WkFdixzUIcAGqY0Hw8suJHSCNzkCKIIKg4isI3tMm/vnQP5FMtIQ4lbr4 SmSEcnCdHa/W7FQXFosfcEjvK76jfkNmp+6Bm35XqNOxs+8II8ahsxEXeEVxKcjPFIbP95Wx S8FzFN5dJP1dnUvJmm5sVocviiIV5pStyna4FcRrhJItTQUpqrMeehaLVk3TaIlcHZ1Umq2o wsQ0OvrMqkQOhEn1Fs5I+yCVRnEJidHSIZKVChdeiXer2nkTFko7a2cOTluJX1EIJGtzLwqz s7q6BKngnNtjEJp+RiGQrNN61kLt1qDZhMIlzrb2xdwx5l/5uZaMWmOHWLOFODupsI/kUnao hWpWKkdJ94L+xM2xTFgojQY0f3Np47gUIxT2iYvKp6RdS+Xw1PthdI9ZIKk0m45AHSHM550F chSaPGqhDsYgSAVPxuwU9BjqboRy7jpO6OfshtFKBXt1tKh+ajiHxXImxzCrHeuuvCEzqc2r VfP22JBCwa7DT+GIIj7PuRY+V1ZHmAU0AAvlzA9qwQYLeXpl9Up+61/B35IPLEvZkYbCln8A AAAASUVORK5CYII= --------------XGybWdxHnlNd792u04QOSDdg-- --------------yNGZWLEY5qVwQIk9hgmIxukq-- --------------9e2DGlyYi8JaudOOXzu2xakR-- --------------dAKPbKsmXpPsdmVYsCJnM44i Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCZgWyNQUDAAAAAAAKCRCKV5yTsxlhuvn8 AJ9PzE00hxXpeuKxXQJdceqDecRtkgCfbYvlinTxev2CP7FlUIx56jGM/ns= =Hjp2 -----END PGP SIGNATURE----- --------------dAKPbKsmXpPsdmVYsCJnM44i--