Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q2Oy0-0006UW-P1 for pgsql-general@arkaria.postgresql.org; Fri, 26 May 2023 04:22:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q2Oxz-0000Cm-Hl for pgsql-general@arkaria.postgresql.org; Fri, 26 May 2023 04:22:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q2Oxz-0000CY-65 for pgsql-general@lists.postgresql.org; Fri, 26 May 2023 04:22:15 +0000 Received: from mail-yw1-x1134.google.com ([2607:f8b0:4864:20::1134]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q2Oxw-002D5o-5s for pgsql-general@lists.postgresql.org; Fri, 26 May 2023 04:22:14 +0000 Received: by mail-yw1-x1134.google.com with SMTP id 00721157ae682-565abf8078eso3935557b3.0 for ; Thu, 25 May 2023 21:22:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1685074931; x=1687666931; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=ff/3YhO6YQKmt0HbUFSENzmmY4Uo8n9R0EocKs7sKHs=; b=ddTT1qW2b2bKx0qkE9Hbkg5bw7Mo/F/IXa/dJDxjrvKIM0X398XTUqyv6kq2ufy86g qZH6cDgi0p1mNu9/VxA1OtlgAcySRhtV8BM2HTNJ/s1BHFLny1Qm6vJqp/q7G6rfKfU4 esowYIdwWjzZ1twzdturNNgqFKNFeJYUZ5YuxORFJz/AWP+zCWmKipzM40nrJ9A11rV0 4Sy4O2RtVtbKYwxHTG/hjHmTc7ccWYS/DNEjlGsC+CCeJ0FPhe42PUo4vac7+0NvpxxO wfIwyc/SqIBHy3lROSHwSHgZNQDCYoA3QBZEZ/7ZpMCYwAFNVANIeMpMeLcG7YsrofJY +baQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1685074931; x=1687666931; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=ff/3YhO6YQKmt0HbUFSENzmmY4Uo8n9R0EocKs7sKHs=; b=aNWV4J2c8q95iUpOFen5g2678/388kSL9wz+nKvX+r7NC0+djgqoKi1/JbRaSa+6iC ckJHqjVbw0kijb1iWo99iV5MBI6fHMPQnAqPjNGiFRPg392B5QsJ4h6h2zdH5GJOslGG EhApT3DMGkhJAJttbPF0GVsnieeSpbVGeuAJ2IhGB1BSUtP0FoYl+fzpEoVZ4DBpmFP4 L6rJB/yQHdgIKyI7OW5scYpcJERmopTrA3KXtP6tPuqmkPr0Q/czhe9dJtPBLc0W5G/x RDnEajgA54V3M5+sWi60mi9xaCtPXqzjTF9YZYSDrWaJdjkdOO0tE9Z3zLK/87o4V6Gx I88w== X-Gm-Message-State: AC+VfDxputwAkDXo2H1l1IrkEhXvoZ/UGkx270+wxmk6sWToWxnAuoZc alnEdPi0EP07FMkO8ll8EZAMRd14y9s= X-Google-Smtp-Source: ACHHUZ5kj6ZBe968nPe4ugne9p9Lk72yLr67HGEK1G3RaLD/J4B71DLEPhGmHv2Ok0yrFbVtSeDtAg== X-Received: by 2002:a0d:ea04:0:b0:562:41c:f622 with SMTP id t4-20020a0dea04000000b00562041cf622mr659847ywe.2.1685074930877; Thu, 25 May 2023 21:22:10 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:5d38:fc4c:f78f:862b? ([2600:1700:bd2c:b810:5d38:fc4c:f78f:862b]) by smtp.googlemail.com with ESMTPSA id x126-20020a0dd584000000b00565271801b6sm905423ywd.59.2023.05.25.21.22.10 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 25 May 2023 21:22:10 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------1xivjH5SDCMPz84iU4aSSnqT" Message-ID: <94cb76a5-f18c-0a1e-cff7-fef61d5769e4@gmail.com> Date: Thu, 25 May 2023 23:22:10 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: DB migration : Sybase to Postgres Content-Language: en-US To: Sengottaiyan T Cc: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------1xivjH5SDCMPz84iU4aSSnqT Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit I don't remember, to be honest.  ALTER TABLE ... VALIDATE CONSTRAINT was /really fast/, though. Having a supporting index (which Pg does /not/ automatically create) is vital, of course. On 5/25/23 23:16, Sengottaiyan T wrote: > Thanks Ron. > > While enabling the constraint (valid constraint) after initial data > import, will it scan all the table data or the validation will happen for > new rows inserted after that point)? > > On Thu, May 25, 2023 at 6:40 PM Ron wrote: > > If I read your email correctly, I see two options > - Apply FK constraints *after* initial data load. > - Load parent data before child data > > (You can create the FKs ahead of time, but use the NOT VALID clause; > then, after data is loaded do ALTER TABLE ... VALIDATE CONSTRAINT.) > > On 5/25/23 06:30, Sengottaiyan T wrote: >> Is there an option to set novalidate constraints in postgres? In my >> source Sybase DB, table structures are not defined properly (only >> primary keys exist and no foreign key) - I'm making necessary changes >> on target Postgres DB (created tables, identifying relationship >> between table columns with respective team). After creating proper >> structure on target, the next step is to load data - I'm sure there >> will be a lot of errors during initial data load (no parent record >> found). How to handle it? >> >> On Tue, Mar 28, 2023 at 3:22 PM Sengottaiyan T >> wrote: >> >> Team, >> >> Need some help regarding Open Source tools for DB migration (from >> SYBASE to PostgreSQL) with real time CDC. Along with this, is >> there any possibility to migrate other objects (like Triggers, >> Stored Procedures and Functions) to PostgreSQL? >> >> Thanks, >> Senko >> > > -- > Born in Arizona, moved to Babylonia. > -- Born in Arizona, moved to Babylonia. --------------1xivjH5SDCMPz84iU4aSSnqT Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit I don't remember, to be honest.  ALTER TABLE ... VALIDATE CONSTRAINT was really fast, though.

Having a supporting index (which Pg does not automatically create) is vital, of course.

On 5/25/23 23:16, Sengottaiyan T wrote:
Thanks Ron.

While enabling the constraint (valid constraint) after initial data import, will it scan all the table data or the validation will happen for new rows inserted after that point)?

On Thu, May 25, 2023 at 6:40 PM Ron <ronljohnsonjr@gmail.com> wrote:
If I read your email correctly, I see two options
- Apply FK constraints *after* initial data load.
- Load parent data before child data

(You can create the FKs ahead of time, but use the NOT VALID clause; then, after data is loaded do ALTER TABLE ... VALIDATE CONSTRAINT.)

On 5/25/23 06:30, Sengottaiyan T wrote:
Is there an option to set novalidate constraints in postgres? In my source Sybase DB, table structures are not defined properly (only primary keys exist and no foreign key) - I'm making necessary changes on target Postgres DB (created tables, identifying relationship between table columns with respective team). After creating proper structure on target, the next step is to load data - I'm sure there will be a lot of errors during initial data load (no parent record found). How to handle it?

On Tue, Mar 28, 2023 at 3:22 PM Sengottaiyan T <techsenko@gmail.com> wrote:
Team,

Need some help regarding Open Source tools for DB migration (from SYBASE to PostgreSQL) with real time CDC. Along with this, is there any possibility to migrate other objects (like Triggers, Stored Procedures and Functions) to PostgreSQL?

Thanks,
Senko

--
Born in Arizona, moved to Babylonia.

--
Born in Arizona, moved to Babylonia.
--------------1xivjH5SDCMPz84iU4aSSnqT--