Stuck at db_migrate_mkforeign, relation "schemas" does not exist

[markodvornik]

Hi,

I have installed plugins db_migrator--0.9.0.sql and ora_migrator--0.9.2.sql. For User mapping I used an user with full privileges on Oracle. I have also enabled DEBUG logging in the db_migrate_prepare script.

The output of the migrate step:

SELECT public.db_migrate_prepare(plugin => 'ora_migrator', server => 'oracle');

NOTICE:  Creating staging schemas "fdw_stage" and "pgsql_stage" ...
NOTICE:  Creating foreign metadata views in schema "fdw_stage" ...
DEBUG:  building index "pg_toast_19067_index" on table "pg_toast_19067" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "schemas_pkey" for table "schemas"
DEBUG:  building index "schemas_pkey" on table "schemas" serially
DEBUG:  building index "pg_toast_19075_index" on table "pg_toast_19075" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "tables_pkey" for table "tables"
DEBUG:  building index "tables_pkey" on table "tables" serially
DEBUG:  building index "pg_toast_19089_index" on table "pg_toast_19089" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "columns_pkey" for table "columns"
DEBUG:  building index "columns_pkey" on table "columns" serially
DEBUG:  CREATE TABLE / UNIQUE will create implicit index "columns_unique" for table "columns"
DEBUG:  building index "columns_unique" on table "columns" serially
DEBUG:  building index "pg_toast_19104_index" on table "pg_toast_19104" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "checks_pkey" for table "checks"
DEBUG:  building index "checks_pkey" on table "checks" serially
DEBUG:  building index "pg_toast_19118_index" on table "pg_toast_19118" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "foreign_keys_pkey" for table "foreign_keys"
DEBUG:  building index "foreign_keys_pkey" on table "foreign_keys" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "keys_pkey" for table "keys"
DEBUG:  building index "keys_pkey" on table "keys" serially
DEBUG:  building index "pg_toast_19148_index" on table "pg_toast_19148" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "views_pkey" for table "views"
DEBUG:  building index "views_pkey" on table "views" serially
DEBUG:  building index "pg_toast_19163_index" on table "pg_toast_19163" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "functions_pkey" for table "functions"
DEBUG:  building index "functions_pkey" on table "functions" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "sequences_pkey" for table "sequences"
DEBUG:  building index "sequences_pkey" on table "sequences" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "indexes_pkey" for table "indexes"
DEBUG:  building index "indexes_pkey" on table "indexes" serially
DEBUG:  CREATE TABLE / UNIQUE will create implicit index "indexes_unique" for table "indexes"
DEBUG:  building index "indexes_unique" on table "indexes" serially
DEBUG:  building index "pg_toast_19201_index" on table "pg_toast_19201" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "index_columns_pkey" for table "index_columns"
DEBUG:  building index "index_columns_pkey" on table "index_columns" serially
DEBUG:  building index "pg_toast_19214_index" on table "pg_toast_19214" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "triggers_pkey" for table "triggers"
DEBUG:  building index "triggers_pkey" on table "triggers" serially
DEBUG:  building index "pg_toast_19229_index" on table "pg_toast_19229" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "table_privs_pkey" for table "table_privs"
DEBUG:  building index "table_privs_pkey" on table "table_privs" serially
DEBUG:  building index "pg_toast_19242_index" on table "pg_toast_19242" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "column_privs_pkey" for table "column_privs"
DEBUG:  building index "column_privs_pkey" on table "column_privs" serially
DEBUG:  building index "pg_toast_19255_index" on table "pg_toast_19255" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "migrate_log_pkey" for table "migrate_log"
DEBUG:  building index "migrate_log_pkey" on table "migrate_log" serially
Query returned successfully in 5 min.

The output of the second step SELECT public.db_migrate_mkforeign(plugin => 'ora_migrator', server => 'oracle');

is:

NOTICE:  Creating schemas ...

ERROR:  relation "schemas" does not exist
LINE 1: SELECT schema FROM schemas
                           ^
QUERY:  SELECT schema FROM schemas
CONTEXT:  PL/pgSQL function public.db_migrate_mkforeign(name,name,name,name,jsonb) line 73 at FOR over SELECT rows
SQL state: 42P01

I don't understand fully which schema should be created on what server (ora od psql).

I also noticed that the tempdatabase on Oracle increased its size by 2 GB after the first step. Why is that? How can I test the success status of the first step? (query for the ora server?)

Thanks

[laurenz]

Hm. After db_migrate_prepare you should have a schema fdw_stage that contains a foreign table schemas.

Could you test if these objects exist?

[markodvornik]

Remote Oracle server doesn't have a fdw_stage schema. (We have Oracle Express version)

Should I've had this schema on Postgres or on Oracle server?

I'm sorry to ask such basic questions, but I'm not sure I understand how this should work. I've been reading Arhitecture section of db_migrator and (for me) it is a bit confusing what aplies to remote and what to local instance.

[laurenz]

No need to apologize, I should have been more clear: the fdw_stage schema and the foreign table should be created in the target PostgreSQL database by db_migrate_prepare.

Was there perhaps an error or something else that rolled back the transaction?