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 1v9r5q-009eWv-4j for pgsql-admin@arkaria.postgresql.org; Fri, 17 Oct 2025 20:30:29 +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 1v9r5p-005D3W-47 for pgsql-admin@arkaria.postgresql.org; Fri, 17 Oct 2025 20:30:28 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v9r5o-005D35-HA for pgsql-admin@lists.postgresql.org; Fri, 17 Oct 2025 20:30:27 +0000 Received: from mail-pf1-x435.google.com ([2607:f8b0:4864:20::435]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v9r5l-002LaK-0u for pgsql-admin@lists.postgresql.org; Fri, 17 Oct 2025 20:30:26 +0000 Received: by mail-pf1-x435.google.com with SMTP id d2e1a72fcca58-782bfd0a977so1860061b3a.3 for ; Fri, 17 Oct 2025 13:30:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1760733024; x=1761337824; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=cd5F8EI9B2yJ/1sNY0pJofdGx4lsfKTVcYguqm6mxhc=; b=ME+RTJJbVu6FRR0PFKkyQR5DecMjr8fvnXCd+F023BdPoagL6t1mQMMuPyxFqIlnpt SfBnZ9WBGqV415OuqOztNbrrwLRnQgWzRfIAUi9oKXbwr7jqlxEp2BYga06hkpJwhHvT 7RtdRsXyQ9RPAWCOZuR5DoIpaAuyD76ynY0Rky62oVDkchpbWqiwc8+8uoTkKjMm6ail g2Z9iVPDAeNuUsi6G4MPs3yYXgeLUmnfPthBP0j/Okm5AwL5+sgd2jgc/223hHjeqham sYF3YSzOShRtrLfMlNfjreJLjY+rgHlGZjzi4fQEW8ZD6HMrPVriSFJZUpVD8FvkloBt YWVw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1760733024; x=1761337824; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=cd5F8EI9B2yJ/1sNY0pJofdGx4lsfKTVcYguqm6mxhc=; b=L/fnHLubJFCVMhRywdrCHEGcOCDt2slDFFZTceXYBRHs4OFBXm9DIMogOiqcjUEsa6 o63tjFhl+Q91Xp9WNps1HbHiR0kk7z/oo/i1EQJ3+ChV3820S0L/hsZka+rIz5SNUxYs 8/RHp9/o1Z8p60ciMCQ702vzVOA1ZrkmjZMH4SmK1SoKeUnP1vnaRsaLir/iPUBRY9hb FRz34tFeMUwnlKOsd8+vZCiZBoqKTTQYgc1g+g34aX0EJL0lI6C/qq1N+MYcqz+56m3f /uioGwjrErvFN2Bey/OgdXKNl4CLt5HRfixqp5LZLGUQB9ErxBKzCW6dNbQh3HKKD42k +eIg== X-Gm-Message-State: AOJu0YzL5pSvjj5ZG70+cWVOBxrD7CzcUwsam3EmDtwyyFgd9W3RntJ7 L3zabMdgPAviIRExvndt4fnYA8WnulRf/yCVuKUO5lXMQEkbRSQ+vkx2B9/xkyZwV2i3bGQHUEQ jDMeq3pFPE136O7RDt+0mHtJhAiV14yhPO8z2nSE= X-Gm-Gg: ASbGnctNfUcqeyrx7CotJEIAWbSUToeDHgzZ8nt/sYZrgyALegmtNc2jXcZlz+yYZGO /1i4E1aOUdXK3hde10AMA+sA+XKw2yPbS3LHtugMH1qUVEUNpGdOTrbDwkMCWqKJf61qTT1UJVS yYSrzrRsphMfok2q61XZejecDk1+d12Mso5R3l90j6J5s3s5cDhSf1Yyn6LQ66aGx8vtJc8jymh NFZggptCTtBjjdPhpXyLwyKE8FB9Txta/21AyYq9BHIHy2RwEosCtJuE63Ppf1wZkkrtTg0 X-Google-Smtp-Source: AGHT+IHDhNOzR8dUURuE8oXozaoQBsO9w3abEy4+YI6ud+/QFq/fC2JAPBmXR9rnSH5e6dX0fhE29vXqDCE4yu7gbH0= X-Received: by 2002:a17:902:d484:b0:290:a8fe:24e5 with SMTP id d9443c01a7336-290cb65c662mr67590675ad.55.1760733023642; Fri, 17 Oct 2025 13:30:23 -0700 (PDT) MIME-Version: 1.0 References: <1915052734.3603827.1760618757611@mail.yahoo.com> In-Reply-To: From: mahamood hussain Date: Sat, 18 Oct 2025 02:00:09 +0530 X-Gm-Features: AS18NWD-eXBw0sh6VNiAPBopojJ5hSm891Xq8gO1e-7N3VASNH8Q184L2WI--Eg Message-ID: Subject: Re: Guidance Requested: Migrating Large-Scale DB2 Databases to PostgreSQL To: Brian Crockard Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="0000000000006b299f0641609a2f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006b299f0641609a2f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable A few other points I=E2=80=99d like to highlight =E2=80=94 we use the DB2 L= OAD utility for our daily data loads into tables. Since this utility bypasses transaction logging for performance reasons, any open-source replication tool that relies on transaction logs to replicate data into PostgreSQL will not capture or replicate these changes. Therefore, I'm looking for tools that can specifically address this primary limitation. On Sat, Oct 18, 2025 at 1:19=E2=80=AFAM mahamood hussain wrote: > Hi Brian, Ron, Kurtoglu, > > Thank you all for the thoughtful responses. It took a bit of effort to > pull this information together, but I'm glad to see the insights coming i= n > =E2=80=94 it's given me more confidence that this migration is indeed ach= ievable > with the right approach. > > To clarify our setup: > We're currently running DB2 LUW v11.5.9 (Advanced Enterprise Server > Edition). One of our key concerns is licensing cost, which is driving our > move to PostgreSQL. > A few follow-up questions based on your responses: > > 1. > > InfoSphere Data Replication (CDC): > We understand this is a strong option, but it likely incurs additional > licensing costs. We=E2=80=99re exploring Fivetran for data replication= between DB2 > and PostgreSQL, with the flexibility to fall back to DB2 if performanc= e or > stability issues arise in production. > - > > Can CDC be used for such a failback scenario (i.e., from PostgreSQL > =E2=86=92 DB2) similar to how Fivetran supports bidirectional sync? > 2. > > Open-source Tools: > Do we have any robust open-source tools that support both forward and > backward replication between DB2 and PostgreSQL? This would help us pl= an > for both migration and rollback scenarios without heavy vendor lock-in= . > 3. > > Pitfalls to Avoid During Migration: > Since our primary goal is cost reduction, we would ideally avoid > needing to fall back to DB2. Based on your experience, what are some c= ommon > pitfalls or gotchas we should look out for when moving from DB2 to > PostgreSQL? > 4. > > PostgreSQL Performance Tuning & Config: > If there=E2=80=99s a list of standard PostgreSQL parameters that you'= d > recommend tweaking right after installation =E2=80=94 especially for l= arge datasets > =E2=80=94 that would be very helpful. We're anticipating around 100+ c= oncurrent > connections during peak hours and migrating some very large tables (up= to > 80B rows). > 5. > > Community vs Enterprise Support: > In production, having support with a guaranteed response time (e.g., > under an hour) is crucial. > - > > Does the PostgreSQL community offer any premium or paid support > options with SLAs, or would we need to go with providers like EDB f= or this > level of assurance? > > ------------------------------ > > > On Thu, Oct 16, 2025 at 6:15=E2=80=AFPM Brian Crockard > wrote: > >> You may want to look into an IBM product called InfoSphere Data >> Replciation (CDC) >> >> >> https://www.ibm.com/docs/nl/idr/11.4.0?topic=3Drequirements-supported-so= urce-targets >> >> It will replicate data from DB2 to PostgreSQL. You can set it up to >> actively replciate the data and keep it consistent. Then perform a cutov= er >> at some point to the new system running against the new replicated >> database. You will then have all of your historical data in the new syst= em. >> We used this on an extremely active system and it hard very few issues. >> >> On Wednesday, October 15, 2025 at 05:15:00 PM EDT, mahamood hussain < >> hussain.ieg@gmail.com> wrote: >> >> >> Hi Team, >> >> We are in the process of migrating several DB2 databases to PostgreSQL, >> primarily to reduce the high licensing costs associated with DB2. These >> databases support retail applications (e.g., supermarkets and stores), a= nd >> during peak hours, we anticipate over 100 concurrent connections. >> ------------------------------ >> Current Database Profile: >> >> - >> >> Approximately 3,000 tables in total >> - >> >> Around 100 tables contain active data >> - >> >> Most tables have low data volume >> - >> >> A few large tables range from 10 GB to 2 TB >> - >> >> The largest table contains approximately 80 billion rows >> >> ------------------------------ >> Migration Approach: >> >> - >> >> We are using Ispirer for code conversion (DB2 to PostgreSQL). >> - >> >> For data migration, we are evaluating Fivetran, but noted that it >> relies on the COPY method for data loading. >> >> ------------------------------ >> Questions & Areas Where We Need Guidance: >> >> 1. >> >> Is Fivetran a suitable option for migrating very large datasets >> (e.g., tables with 80+ billion rows)? >> 2. >> >> Are there any reliable open-source tools for DB2 to PostgreSQL data >> migration that we can use internally, without needing to invest in a = tool >> like Fivetran? >> 3. >> >> Are there more scalable or efficient alternatives for both the >> initial load and ongoing/incremental sync to PostgreSQL? >> >> ------------------------------ >> Additional Input Requested: >> >> - >> >> What are the key best practices (Do=E2=80=99s and Don=E2=80=99ts) to = keep in mind >> during a large-scale DB2 =E2=86=92 PostgreSQL migration? >> - >> >> Are there specific PostgreSQL settings or configurations we should >> pay attention to for optimizing performance, especially for large dat= asets >> and DB2-style workloads? >> >> ------------------------------ >> >> We are keen to ensure performance, data integrity, and scalability >> throughout this migration. Any insights=E2=80=94particularly from those = with >> experience in similar large-scale PostgreSQL implementations=E2=80=94wou= ld be >> highly appreciated. >> >> If this is not the right forum for these questions, please do let me kno= w >> if there is a better place to seek this guidance. >> >> Thanks in advance for your support! >> > --0000000000006b299f0641609a2f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
A few other points I=E2=80=99d like to hi= ghlight =E2=80=94 we use the DB2 LOAD utility for our daily data loads into= tables. Since this utility bypasses transaction logging for performance re= asons, any open-source replication tool that relies on transaction logs to = replicate data into PostgreSQL will not capture or replicate these changes.= Therefore, I'm=C2=A0looking for tools that can specifically address th= is primary limitation.
=
On Sat, Oct 18, 2025 at 1:19=E2=80=AF= AM mahamood hussain <hussain.ie= g@gmail.com> wrote:

Hi Brian, = Ron, Kurtoglu,

Thank you all for the thoughtful responses. It took a bit of effort to p= ull this information together, but I'm glad to see the insights coming = in =E2=80=94 it's given me more confidence that this migration is indee= d achievable with the right approach.

To clarify our setup:
We're currently running DB2 LUW v11.5.9 (Advanced Enterprise Server Edi= tion). One of our key concerns is licensing cost, which is driving our move= to PostgreSQL.

A few follow-up questions based on y= our responses:

  1. InfoSphere Data Replication (CDC):
    We understand this is a strong option, but it likely incurs additional lice= nsing costs. We=E2=80=99re exploring Fivetran for data replication between = DB2 and PostgreSQL, with the flexibility to fall back to DB2 if performance= or stability issues arise in production.

    • Can CDC be used for such a failback scenario (i.e., from PostgreSQL =E2= =86=92 DB2) similar to how Fivetran supports bidirectional sync?

  2. Open-source Tools:
    Do we have any robust open-source tools =C2=A0that support both forward and= backward replication between DB2 and PostgreSQL? This would help us plan f= or both migration and rollback scenarios without heavy vendor lock-in.

  3. Pitfalls to Avoid During Migration:
    Since our primary goal is cost reduction, we would ideally avoid needing to= fall back to DB2. Based on your experience, what are some common pitfalls = or gotchas we should look out for when moving from DB2 to PostgreSQL?

  4. PostgreSQL Performance Tuning & Config:
    If there=E2=80=99s a list of standard PostgreSQL parameters =C2=A0that you&= #39;d recommend tweaking right after installation =E2=80=94 especially for = large datasets =E2=80=94 that would be very helpful. We're anticipating= around 100+ concurrent connections during peak hours and migrating some ve= ry large tables (up to 80B rows).

  5. Community vs Enterprise Support:
    In production, having support with a guaranteed response time (e.g., under = an hour) is crucial.

    • Does the PostgreSQL community offer any premium or paid support options = with SLAs, or would we need to go with providers like EDB for this level of= assurance?


On Thu, Oct 16, 2025 at 6:15=E2=80=AFPM Brian Crockard <bcrockard@yahoo.com> wrote:
You may want to look into an IBM product called In= foSphere Data Replciation (CDC)



=20
=20


Hi Team,

We are in the process of migrating several DB2 database= s to PostgreSQL, primarily to reduce the high licensing costs associated wi= th DB2. These databases support retail applications (e.g., supermarkets and= stores), and during peak hours, we anticipate over 100 concurrent connecti= ons.

Current Database Profile= :

  • Approximately 3,000 tables in total

  • Around 100 tables contain active data

  • Most tables have low data volume

  • A few large tables range from 10 GB to 2 TB

  • The largest table contains approximately 80 billion rows

Migration Approach:<= /h3>

  • We are using Ispirer for code conversion (DB2 to PostgreSQL).

  • For data migration, we are evaluating Fivetran, but noted that it relies= on the COPY method for data loading.

Questions & Areas Where= We Need Guidance:

  1. Is Fivetran a suitable option for migrating very large datasets (e.g., t= ables with 80+ billion rows)?

  2. Are there any reliable open-source tools for DB2 to PostgreSQL data migr= ation that we can use internally, without needing to invest in a tool like = Fivetran?

  3. Are there more scalable or efficient alternatives for both the initial l= oad and ongoing/incremental sync to PostgreSQL?

Additional Input Requested:=

  • What are the key best practices (Do=E2=80=99s and Don=E2=80=99ts) to kee= p in mind during a large-scale DB2 =E2=86=92 PostgreSQL migration?

  • Are there specific PostgreSQL settings or configurations we should pay a= ttention to for optimizing performance, especially for large datasets and D= B2-style workloads?

We are keen to ensure performance, data integrity, and scalabil= ity throughout this migration. Any insights=E2=80=94particularly from those= with experience in similar large-scale PostgreSQL implementations=E2=80=94= would be highly appreciated.

If this is not the right forum for these= questions, please do let me know if there is a better place to seek this g= uidance.

Thanks in advance for your support!

--0000000000006b299f0641609a2f--