Postgres Pro
Downloads
Home   >   mailing lists

Thread: Migrating to postgresql from oracle

Migrating to postgresql from oracle

From
Timo Myyrä
Date:
Hi,

I'm preparing migration of our asset management system database from Oracle 12c to
PostgreSQL 10.  I'm using ora2pg and a bit of sed to mangle the SQL ready for
import to pg but I've hit first problem:
ERROR: referenced relation "..." is not a table

Our Oracle databases uses single 'admin' schema and dedicated schema for each
customer. For example usergroup mappings are held in admin.usergroup table. Then
admin schema has customer-specific view on this table admin.usergroup_customer1
view which limits the full view to just those of that customer. And then each
customers own schema has synonym to that view like customer1.usergroup. The
applications queries use the "usergroup" table to query the group mappings.

Here's hopefully a bit more detailed description of the database structure:

ADMIN SCHEMA:
  TABLES:
    usergroup
    unit
    user
    ...
  VIEWS:
    usergroup_customer1
    unit_customer1
    user_customer1
    ...
CUSTOMER1 SCHEMA:
  TABLES:
    resource
    ...
  SYNONYM:
    usergroup (refers to admin.usergroup_customer1)
    user (refers to admin.user_customer1)
    unit (refers to admin.unit_customer1)
    ...

So it seems that postgresql doesn't support foreign keys in views like Oracle.
Would you have any suggestions how the above Oracle structure would best be
handled in PostgreSQL? I'm pretty new to PostgreSQL so I might overlook
something if I try to solve this by myself.

Timo

Re: Migrating to postgresql from oracle

From
Pavel Stehule
Date:
Hi

2017-12-23 19:53 GMT+01:00 Timo Myyrä <timo.myyra@bittivirhe.fi>:
Hi,

I'm preparing migration of our asset management system database from Oracle 12c to
PostgreSQL 10.  I'm using ora2pg and a bit of sed to mangle the SQL ready for
import to pg but I've hit first problem:
ERROR: referenced relation "..." is not a table

Our Oracle databases uses single 'admin' schema and dedicated schema for each
customer. For example usergroup mappings are held in admin.usergroup table. Then
admin schema has customer-specific view on this table admin.usergroup_customer1
view which limits the full view to just those of that customer. And then each
customers own schema has synonym to that view like customer1.usergroup. The
applications queries use the "usergroup" table to query the group mappings.

Here's hopefully a bit more detailed description of the database structure:

ADMIN SCHEMA:
  TABLES:
    usergroup
    unit
    user
    ...
  VIEWS:
    usergroup_customer1
    unit_customer1
    user_customer1
    ...
CUSTOMER1 SCHEMA:
  TABLES:
    resource
    ...
  SYNONYM:
    usergroup (refers to admin.usergroup_customer1)
    user (refers to admin.user_customer1)
    unit (refers to admin.unit_customer1)
    ...

So it seems that postgresql doesn't support foreign keys in views like Oracle.
Would you have any suggestions how the above Oracle structure would best be
handled in PostgreSQL? I'm pretty new to PostgreSQL so I might overlook
something if I try to solve this by myself.


Regard

Pavel



Timo


Re: Migrating to postgresql from oracle

From
Timo Myyrä
Date:
On Sat, Dec 23, 2017, at 21:05, Pavel Stehule wrote:
Hi

2017-12-23 19:53 GMT+01:00 Timo Myyrä <timo.myyra@bittivirhe.fi>:
Hi,

I'm preparing migration of our asset management system database from Oracle 12c to
PostgreSQL 10.  I'm using ora2pg and a bit of sed to mangle the SQL ready for
import to pg but I've hit first problem:
ERROR: referenced relation "..." is not a table

Our Oracle databases uses single 'admin' schema and dedicated schema for each
customer. For example usergroup mappings are held in admin.usergroup table. Then
admin schema has customer-specific view on this table admin.usergroup_customer1
view which limits the full view to just those of that customer. And then each
customers own schema has synonym to that view like customer1.usergroup. The
applications queries use the "usergroup" table to query the group mappings.

Here's hopefully a bit more detailed description of the database structure:

ADMIN SCHEMA:
  TABLES:
    usergroup
    unit
    user
    ...
  VIEWS:
    usergroup_customer1
    unit_customer1
    user_customer1
    ...
CUSTOMER1 SCHEMA:
  TABLES:
    resource
    ...
  SYNONYM:
    usergroup (refers to admin.usergroup_customer1)
    user (refers to admin.user_customer1)
    unit (refers to admin.unit_customer1)
    ...

So it seems that postgresql doesn't support foreign keys in views like Oracle.
Would you have any suggestions how the above Oracle structure would best be
handled in PostgreSQL? I'm pretty new to PostgreSQL so I might overlook
something if I try to solve this by myself.


Regard

Pavel



Timo

Well, I doubt postgresql limitations apply for ora2pg.

I looked this a bit more closely. Postgresql seems to have nice feature which would apply to this quite nicely called row-level security. I like to limit the 'admin' schema table rows so that each user sees only their own rows.

So a bit more about the table structure is needed to understand the situation.
Lets take admin.unit table, this holds the business units of each customer. The import rows are id and parent_id. Parent_id field might refer to unit id in the same unit table so units can be nested. The top-level unit doesn't have parent_id. The admin.unit.id field gives the top-level id, then theres the admin.project_unit table which has 2 fields, project_id, unit_id which gives mapping to project. Finally we have project table which has the actual project id and name which map to logged in project.

I was testing the row-level security with following query but it gives syntax error for some reason:

CREATE POLICY unit_customer1 ON admin.unit
    FOR ALL
    TO customer1
    USING ( with recursive e(id,parent_id) as (
              select id, parent_id
              from admin.bg_unit
              where id in (select unit_id from admin.project_unit where project_id = 'customer1')
            union all
              select f.id, f.parent_id
              from admin.bg_unit f, e
              where e.id = f.parent_id
            ) select id from e order by id asc
    );

When I run the above query I get just:
ERROR:  syntax error at or near "with"
LINE 4:     USING ( with recursive e(id,parent_id) as (

Can the above policy made to work for my use-case or am I doing something fundamentally wrong?

Timo