Migrating to postgresql from oracle

Поиск
Список
Период
Сортировка
От Timo Myyrä
Тема Migrating to postgresql from oracle
Дата
Msg-id 1514055195.1570504.1214442624.3DB93FFD@webmail.messagingengine.com
обсуждение исходный текст
Ответы Re: Migrating to postgresql from oracle  (Pavel Stehule <pavel.stehule@gmail.com>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Melvin Davidson
Дата:
Сообщение: Re: Array of foreign key
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: Migrating to postgresql from oracle