Re: Oracle DB migration to Postgrsql

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Fri, 14 Apr 2023 17:01:30 -0400
Message-ID: <76d1ff16-2d63-65ed-331c-eaeabcd6055f_at_gmail.com>

On 4/14/23 13:55, Jeremiah Cetlin Wilton wrote:
> AWS has a free tool:
>
> https://aws.amazon.com/dms/schema-conversion-tool/
> https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html
>
> It doesn't do everything (what does?) but it is super helpful.
>
> Jeremiah

Having said that, there is no substitute to understanding both databases. Migrating to PostgreSQL implies that you know PostgreSQL really well and can manage the application using Postgres back end. You will need to rewrite procedures, change behavior with respect to unique indexes and many other things. You will also need to rethink the use of partitions. Migrating a simple schema is easy:

|mgogala_at_umajor PgSQL]$ psql -U scott Password for user scott: psql (15.2) Type "help" for help. scott=> \d List of relations Schema | Name

| Type | Owner --------+-------------------------+-------+---------- 

public | pg_stat_statements | view | postgres public | pg_stat_statements_info | view | postgres scott | bonus | table | scott scott | dept | table | scott scott | emp | table | scott scott | salgrade | table | scott (6 rows) scott=> select * from emp; empno | ename | job | mgr | hiredate | sal | comm | deptno

-------+--------+-----------+------+---------------------+------+------+-------- 

7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | | 20 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | | 20 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 7698 | BLAKE

| MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | | 30 7782 | CLARK | 
MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | | 10 7788 | SCOTT | 
ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | | 20 7839 | KING | 

PRESIDENT | | 1981-11-17 00:00:00 | 5000 | | 10 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | | 20 7900 | JAMES | CLERK | 7698 |

1981-12-03 00:00:00 | 950 | | 30 7902 | FORD | ANALYST | 7566 | 
1981-12-03 00:00:00 | 3000 | | 20 7934 | MILLER | CLERK | 7782 | 
1982-01-23 00:00:00 | 1300 | | 10 (14 rows) However, this isn't very 

useful, except for playing with the syntax, which is what I usually do with PgSQL these days. I would almost recommend using Babelfish and the tools to convert to SQL server. |

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 14 2023 - 23:01:30 CEST