Обсуждение: [ADMIN] Migrate from Oracle 12g to PostgreSQL 9.3
Hi All,
I’m looking for a solution regarding migrate a user from Oracle DB to PostgreSQL having the same permissions, Tablespace Quota, Table Privileges, Roles and so on afterwards.
Any help would be much appreciated!!
Thanks in advance and bets regards
Thorsten Gust
IT-System Management
SPIEGEL-Verlag Rudolf Augstein GmbH & Co. KG
Ericusspitze 1
20457 Hamburg
Tel: +49 40 3007-2759
Fax: +49 40 3007-85 2759
E-Mail: thorsten.gust@spiegel.de
SPIEGEL-Verlag Rudolf Augstein GmbH & Co. KG, Sitz und Registergericht Hamburg HRA 61 755
Komplementärin Rudolf Augstein GmbH, Sitz und Registergericht Hamburg HRB 13 105
Geschäftsführer Thomas Hass
At the Mississippi State Supreme Court, we just recently finished up our migration from Informix to PostgreSQL.
I was writing a pub on how to convert to Postgres primarily aimed at the Informix to Postgres move,
but it was generic enough to be used on other databases.
My conversion also used several Python programs to manage parsing the SCHEMA dumps and creating the
necessary setup sql for tables, primary keys, secondary keyes, foreign keyes as well as Functions.
However, informix's triggers WERE a big pain, and I basically had to review each and do them by hand.
BUT I did document the differences in the pub I was doing.
All's I need is somewhere submit this publication for reference by others (I've released it under the creative commons).
Any suggestions?
It might help in what you are doing.
Ben Duncan - Business Network Solutions, Inc. 336 Elton Road Jackson MS, 39212
"Never attribute to malice, that which can be adequately explained by stupidity"
- Hanlon's Razor
"Never attribute to malice, that which can be adequately explained by stupidity"
- Hanlon's Razor
-------- Original Message --------
Subject: [ADMIN] Migrate from Oracle 12g to PostgreSQL 9.3
From: "Gust, Thorsten" <thorsten.gust@spiegel.de>
Date: Wed, October 25, 2017 8:19 am
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>#wmQuoteWrapper /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}#wmQuoteWrapper @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}#wmQuoteWrapper /* Style Definitions */ p.MsoNormal, #wmQuoteWrapper li.MsoNormal, #wmQuoteWrapper div.MsoNormal {margin:0cm; margin-bottom:.0001pt; font-size:12.0pt; font-family:"Calibri",sans-serif;}#wmQuoteWrapper a:link, #wmQuoteWrapper span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;}#wmQuoteWrapper a:visited, #wmQuoteWrapper span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;}#wmQuoteWrapper span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}#wmQuoteWrapper span.msoIns {mso-style-type:export-only; mso-style-name:""; text-decoration:underline; color:teal;}#wmQuoteWrapper .MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif;}#wmQuoteWrapper @page WordSection1 {size:595.0pt 842.0pt; margin:70.85pt 70.85pt 2.0cm 70.85pt;}#wmQuoteWrapper div.WordSection1 {page:WordSection1;} Hi All,I’m looking for a solution regarding migrate a user from Oracle DB to PostgreSQL having the same permissions, Tablespace Quota, Table Privileges, Roles and so on afterwards.Any help would be much appreciated!!Thanks in advance and bets regardsThorsten GustIT-System ManagementSPIEGEL-Verlag Rudolf Augstein GmbH & Co. KG
Ericusspitze 1
20457 Hamburg
Tel: +49 40 3007-2759
Fax: +49 40 3007-85 2759
E-Mail: thorsten.gust@spiegel.de
SPIEGEL-Verlag Rudolf Augstein GmbH & Co. KG, Sitz und Registergericht Hamburg HRA 61 755
Komplementärin Rudolf Augstein GmbH, Sitz und Registergericht Hamburg HRB 13 105
Geschäftsführer Thomas Hass
On Wed, Oct 25, 2017 at 3:19 PM, Gust, Thorsten <thorsten.gust@spiegel.de> wrote: > I’m looking for a solution regarding migrate a user from Oracle DB to > PostgreSQL having the same permissions, Tablespace Quota, Table Privileges, > Roles and so on afterwards. You should try http://ora2pg.darold.net/, it handles many things automatically. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
On 10/25/2017 10:19 PM, Gust, Thorsten wrote:> Hi All,>> I’m looking for a solution regarding migrate a user from OracleDB to PostgreSQL> having the same permissions, Tablespace Quota, Table Privileges, Roles and so on> afterwards.>> Anyhelp would be much appreciated!! Out of interest, any reason why you're targetting PostgreSQL 9.3? It's currently the oldest of the community-supported releases and will probably be EOL'd some time in late 2018 [*] [*] https://www.postgresql.org/support/versioning/ Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Hi Ian, All, Oops, many thanks for the very fast answers and sorry a mistake of mine, it’s 9.6.3 I’m running at the moment. Thanks in advance and best regards Thorsten Thorsten Gust IT-System Management SPIEGEL-Verlag Rudolf Augstein GmbH & Co. KG Ericusspitze 1 20457 Hamburg Tel: +49 40 3007-2759 Fax: +49 40 3007-85 2759 E-Mail: thorsten.gust@spiegel.de SPIEGEL-Verlag Rudolf Augstein GmbH & Co. KG, Sitz und Registergericht Hamburg HRA 61 755 Komplementärin Rudolf Augstein GmbH, Sitz und Registergericht Hamburg HRB 13 105 Geschäftsführer Thomas Hass On 26.10.17, 04:18, "Ian Barwick" <ian.barwick@2ndquadrant.com> wrote: On 10/25/2017 10:19 PM, Gust, Thorsten wrote: > Hi All, > > I’m looking for a solution regarding migrate a userfrom Oracle DB to PostgreSQL > having the same permissions, Tablespace Quota, Table Privileges, Roles and so on > afterwards. > > Any help would be much appreciated!! Out of interest, any reason why you're targetting PostgreSQL9.3? It's currently the oldest of the community-supported releases and will probably be EOL'd some time inlate 2018 [*] [*] https://www.postgresql.org/support/versioning/ Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Hello Julien, Thanks for that answer, but because we migrated our Oracle Database already to a PostgreSQL one. That’s because we don’tneed to install such tools like ora2pg as we only need a solution to migrate 2 users from Oracle to Postgres, what should not mean that ora2pgis a very nice tool. Thank in advance and best regards Thorsten Gust IT-System Management SPIEGEL-Verlag Rudolf Augstein GmbH & Co. KG Ericusspitze 1 20457 Hamburg Tel: +49 40 3007-2759 Fax: +49 40 3007-85 2759 E-Mail: thorsten.gust@spiegel.de SPIEGEL-Verlag Rudolf Augstein GmbH & Co. KG, Sitz und Registergericht Hamburg HRA 61 755 Komplementärin Rudolf Augstein GmbH, Sitz und Registergericht Hamburg HRB 13 105 Geschäftsführer Thomas Hass On 25.10.17, 20:41, "Julien Rouhaud" <rjuju123@gmail.com> wrote: On Wed, Oct 25, 2017 at 3:19 PM, Gust, Thorsten <thorsten.gust@spiegel.de> wrote: > I’m looking for a solution regardingmigrate a user from Oracle DB to > PostgreSQL having the same permissions, Tablespace Quota, Table Privileges, > Roles and so on afterwards. You should try http://ora2pg.darold.net/, it handles many things automatically. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
On Thu, Oct 26, 2017 at 10:21 AM, Gust, Thorsten <thorsten.gust@spiegel.de> wrote: > Hello Julien, > > Thanks for that answer, but because we migrated our Oracle Database already to a PostgreSQL one. That’s because we don’tneed to install > such tools like ora2pg as we only need a solution to migrate 2 users from Oracle to Postgres, what should not mean thatora2pg is a very nice > tool. Well, ora2pg can be configured to only migrate users and ACL, see the GRANT export type. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin