Re: Migrating to postgresql from oracle

Hi

2017-12-23 19:53 GMT+01:00 Timo Myyrä <timo(dot)myyra(at)bittivirhe(dot)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.
>

try to ask on https://github.com/darold/ora2pg/issues

Regard

Pavel

> Timo
>
>