pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
DB migration : Sybase to Postgres
8+ messages / 4 participants
[nested] [flat]

* DB migration : Sybase to Postgres
@ 2023-03-28 09:52  Sengottaiyan T <techsenko@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Sengottaiyan T @ 2023-03-28 09:52 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: DB migration : Sybase to Postgres
@ 2023-05-25 11:30  Sengottaiyan T <techsenko@gmail.com>
  parent: Sengottaiyan T <techsenko@gmail.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Sengottaiyan T @ 2023-05-25 11:30 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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
>

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: DB migration : Sybase to Postgres
@ 2023-05-25 13:10  Ron <ronljohnsonjr@gmail.com>
  parent: Sengottaiyan T <techsenko@gmail.com>
  1 sibling, 2 replies; 8+ messages in thread

From: Ron @ 2023-05-25 13:10 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: DB migration : Sybase to Postgres
@ 2023-05-26 04:16  Sengottaiyan T <techsenko@gmail.com>
  parent: Ron <ronljohnsonjr@gmail.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Sengottaiyan T @ 2023-05-26 04:16 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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.
>

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: DB migration : Sybase to Postgres
@ 2023-05-26 04:22  Ron <ronljohnsonjr@gmail.com>
  parent: Sengottaiyan T <techsenko@gmail.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Ron @ 2023-05-26 04:22 UTC (permalink / raw)
  To: Sengottaiyan T <techsenko@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: DB migration : Sybase to Postgres
@ 2023-05-26 10:39  Marcos Pegoraro <marcos@f10.com.br>
  parent: Sengottaiyan T <techsenko@gmail.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Marcos Pegoraro @ 2023-05-26 10:39 UTC (permalink / raw)
  To: Sengottaiyan T <techsenko@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

Em qui., 25 de mai. de 2023 às 08:30, Sengottaiyan T <techsenko@gmail.com>
escreveu:

> 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?
>
>>
>> Other options:

create all foreign keys before importing your data and do ...

This way table triggers are disabled for all users;
ALTER TABLE T1 DISABLE TRIGGER ALL; ALTER TABLE T2 DISABLE TRIGGER ALL; ALTER
TABLE T3 DISABLE TRIGGER ALL;
--Import all your data
ALTER TABLE T1 ENABLE TRIGGER ALL; ALTER TABLE T2 ENABLE TRIGGER ALL; ALTER
TABLE T3 ENABLE TRIGGER ALL;

or

This way table triggers are disabled for this session only;
SET SESSION_REPLICATION_ROLE = REPLICA;
--Import all your data
SET SESSION_REPLICATION_ROLE = ORIGIN;

Obviously if your data doesn't have correct foreign keys matching to their
parent you'll never be able to do a dump/restore properly.

Marcos

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: DB migration : Sybase to Postgres
@ 2023-05-26 13:38  Peter J. Holzer <hjp-pgsql@hjp.at>
  parent: Ron <ronljohnsonjr@gmail.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Peter J. Holzer @ 2023-05-26 13:38 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 2023-05-25 08:10:42 -0500, Ron wrote:
> (You can create the FKs ahead of time, but use the NOT VALID clause; then,
> after data is loaded do ALTER TABLE ... VALIDATE CONSTRAINT.)

I don't think this will work:

| Normally, [ADD CONSTRAINT] will cause a scan of the table to verify
| that all existing rows in the table satisfy the new constraint. But if
| the NOT VALID option is used, this potentially-lengthy scan is
| skipped. The constraint will still be enforced against subsequent
| inserts or updates
(https://www.postgresql.org/docs/current/sql-altertable.html)

However, you can define a constraint as DEFERRABLE, and then defer
checking until commit:

hjp=> create table master(id int primary key, t text);
CREATE TABLE
hjp=> create table detail(id int primary key, master int references master DEFERRABLE INITIALLY IMMEDIATE, t text);
CREATE TABLE

hjp=> begin;
BEGIN
hjp=*> set constraints detail_master_fkey deferred;
SET CONSTRAINTS
hjp=*> insert into detail values(1, 1, '1/1');
INSERT 0 1
hjp=*> insert into detail values(2, 1, '1/2');
INSERT 0 1
hjp=*> insert into detail values(3, 2, '2/1');
INSERT 0 1
hjp=*> insert into detail values(4, 3, '3/1');
INSERT 0 1
hjp=*> insert into master values(1, '1');
INSERT 0 1
hjp=*> insert into master values(2, '2');
INSERT 0 1

-- We haven't inserted a master record with id 3 yet, so the commit will
-- fail:

hjp=*> commit;
ERROR:  insert or update on table "detail" violates foreign key constraint "detail_master_fkey"
DETAIL:  Key (master)=(3) is not present in table "master".

(You can also reenable the constraint explicitely before the end of a
transaction with SET CONSTRAINTS ... IMMEDIATE)

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: DB migration : Sybase to Postgres
@ 2023-05-26 14:03  Ron <ronljohnsonjr@gmail.com>
  parent: Peter J. Holzer <hjp-pgsql@hjp.at>
  0 siblings, 0 replies; 8+ messages in thread

From: Ron @ 2023-05-26 14:03 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 5/26/23 08:38, Peter J. Holzer wrote:
> On 2023-05-25 08:10:42 -0500, Ron wrote:
>> (You can create the FKs ahead of time, but use the NOT VALID clause; then,
>> after data is loaded do ALTER TABLE ... VALIDATE CONSTRAINT.)
> I don't think this will work:
>
> | Normally, [ADD CONSTRAINT] will cause a scan of the table to verify
> | that all existing rows in the table satisfy the new constraint. But if
> | the NOT VALID option is used, this potentially-lengthy scan is
> | skipped. The constraint will still be enforced against subsequent
> | inserts or updates
> (https://www.postgresql.org/docs/current/sql-altertable.html)

I was probably thinking of tables with existing data.

-- 
Born in Arizona, moved to Babylonia.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2023-05-26 14:03 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-28 09:52 DB migration : Sybase to Postgres Sengottaiyan T <techsenko@gmail.com>
2023-05-25 11:30 ` Sengottaiyan T <techsenko@gmail.com>
2023-05-25 13:10   ` Ron <ronljohnsonjr@gmail.com>
2023-05-26 04:16     ` Sengottaiyan T <techsenko@gmail.com>
2023-05-26 04:22       ` Ron <ronljohnsonjr@gmail.com>
2023-05-26 13:38     ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-05-26 14:03       ` Ron <ronljohnsonjr@gmail.com>
2023-05-26 10:39   ` Marcos Pegoraro <marcos@f10.com.br>

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