Migration from Derby to PostgreSQL in TEST 7, bumps in the road

The PostgreSQL harvest database is left in an invalid state.

fullharvests is in v4 even though the v5 alterations have been made.
and
extendedfield are not present with any version even though the tables exists.

Consequentially the fullharvests does not seem to have been imported either!

Dumping derby and importing into postgres reports some errors which would be nice to remedy.

----------------

ij(CONNECTION0)> run 'derby_dump_admindb.sql';
ij(CONNECTION0)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'FILE','FILE.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION0)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'REPLICA','REPLICA.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION0)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'REPLICAFILEINFO','REPLICAFILEINFO.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION0)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'SCHEMAVERSIONS','ADMINDB_SCHEMAVERSIONS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION0)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'SEGMENT','SEGMENT.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION0)> set connection CONNECTION1;
ij(CONNECTION1)> run 'derby_dump_hddb.sql';
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'ORDERTEMPLATES','ORDERTEMPLATES.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'SCHEMAVERSIONS','HDDB_SCHEMAVERSIONS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'DOMAINS','DOMAINS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'CONFIGURATIONS','CONFIGURATIONS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'CONFIG_PASSWORDS','CONFIG_PASSWORDS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'CONFIG_SEEDLISTS ','CONFIG_SEEDLISTS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'SEEDLISTS','SEEDLISTS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'HARVEST_CONFIGS','HARVEST_CONFIGS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'PASSWORDS','PASSWORDS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'OWNERINFO','OWNERINFO.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'HISTORYINFO','HISTORYINFO.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'HARVESTDEFINITIONS','HARVESTDEFINITIONS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'FULLHARVESTS','FULLHARVESTS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'PARTIALHARVESTS','PARTIALHARVESTS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'SCHEDULES','SCHEDULES.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> create VIEW JOBS_VIEW AS SELECT JOB_ID, HARVEST_ID, STATUS, PRIORITY, FORCEMAXBYTES, FORCEMAXCOUNT, ORDERXML, ORDERXMLDOC,
SEEDLIST, HARVEST_NUM, HARVEST_ERRORS, HARVEST_ERROR_DETAILS, UPLOAD_ERRORS, UPLOAD_ERROR_DETAILS, STARTDATE, ENDDATE,
NUM_CONFIGS, EDITION, SUBMITTEDDATE, RESUBMITTED_AS_JOB, FORCEMAXRUNNINGTIME FROM JOBS;
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'JOBS_VIEW','JOBS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> DROP VIEW JOBS_VIEW;
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'JOB_CONFIGS','JOB_CONFIGS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'EXTENDEDFIELD','EXTENDEDFIELD.DATA',NULL,NULL,NULL);
ERROR 38000: The exception 'java.sql.SQLException: Table/View 'EXTENDEDFIELD' does not exist.' was thrown while evaluating an expression.
ERROR 42X05: Table/View 'EXTENDEDFIELD' does not exist.
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'GLOBAL_CRAWLER_TRAP_LISTS','GLOBAL_CRAWLER_TRAP_LISTS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'GLOBAL_CRAWLER_TRAP_EXPRESSIONS','GLOBAL_CRAWLER_TRAP_EXPRESSIONS.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'EXTENDEDFIELDVALUE','EXTENDEDFIELDVALUE.DATA',NULL,NULL,NULL);
ERROR 38000: The exception 'java.sql.SQLException: Table/View 'EXTENDEDFIELDVALUE' does not exist.' was thrown while evaluating an expression.
ERROR 42X05: Table/View 'EXTENDEDFIELDVALUE' does not exist.
ij(CONNECTION1)> create view rjh_view as select jobid, harvestname, hosturl, progress, queuedfilescount, totalqueuescount, activequeuescount, retiredqueuescount,
exhaustedqueuescount, elapsedseconds, alertscount, downloadedfilescount, currentprocessedkbpersec, processedkbpersec,
currentprocesseddocspersec, processeddocspersec, activetoecount, status, tstamp from runningJobsHistory;
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'RJH_VIEW','RUNNINGJOBSHISTORY.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> drop view RJH_VIEW;
0 rows inserted/updated/deleted
ij(CONNECTION1)> create view rjm_view as select jobid, harvestname, hosturl, progress, queuedfilescount, totalqueuescount, activequeuescount, retiredqueuescount,
exhaustedqueuescount, elapsedseconds, alertscount, downloadedfilescount, currentprocessedkbpersec, processedkbpersec,
currentprocesseddocspersec, processeddocspersec, activetoecount, status, tstamp from runningJobsMonitor;
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'RJM_VIEW','RUNNINGJOBSMONITOR.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)> drop view RJM_VIEW;
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'EXTENDEDFIELDTYPE','EXTENDEDFIELDTYPE.DATA',NULL,NULL,NULL);
ERROR 38000: The exception 'java.sql.SQLException: Table/View 'EXTENDEDFIELDTYPE' does not exist.' was thrown while evaluating an expression.
ERROR 42X05: Table/View 'EXTENDEDFIELDTYPE' does not exist.
ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'FRONTIERREPORTMONITOR','FRONTIERREPORTMONITOR.DATA',NULL,NULL,NULL);
0 rows inserted/updated/deleted
ij(CONNECTION1)>

test7_harvestdb=> \i sql/postgres_import_hddb.sql
psql:sql/postgres_import_hddb.sql:12: ERROR: missing data for column "isindexready"
CONTEXT: COPY fullharvests, line 1: "25,-1,,10000000,0"
ALTER TABLE
ALTER TABLE
UPDATE 1
psql:sql/postgres_import_hddb.sql:21: EXTENDEDFIELD.DATA: No such file or directory
psql:sql/postgres_import_hddb.sql:23: EXTENDEDFIELDVALUE.DATA: No such file or directory
psql:sql/postgres_import_hddb.sql:26: EXTENDEDFIELDTYPE.DATA: No such file or directory
CREATE TABLE
INSERT 0 1
DROP TABLE
setval
---------
1706163
(1 row)

setval
--------
37
(1 row)

setval
--------
21
(1 row)

setval
---------
1715460
(1 row)

setval
---------
1715429
(1 row)

setval
----------
16751719
(1 row)

setval
--------
2
(1 row)

setval
--------
3
(1 row)

setval
--------

(1 row)

setval
--------

(1 row)

test7_harvestdb=>