Deploy Existing Postgres DB

doulikecookiedough commented 2 months ago

To gather data and start working on the new RESTful API, we first need to get the existing postgres db/setup running so that we can investigate/poke-around.

To Do:

[x] Ask Matt/Nick to deploy a k8s namespace specifically for VegBank
[x] Get local Vegbank postgres working
[x] Get a vegbank dump file, and try to restore it
[ ] Create helm chart to deploy VegBank PG
[ ] Deploy the VegBank Postgres on k8s namespace

mbjones commented 2 months ago

@doulikecookiedough I created the vegbank namespace and service account on the dev cluster, so you should be set to get started there.

mbjones commented 1 month ago

@doulikecookiedough I added project info and labels to your ticket, and the milestone.

doulikecookiedough commented 4 weeks ago

Update:

I am able to restore the comprehensive dump file provided by Nick to a Postgres 10.23 without any exceptions.

However, restoring the records only dump file to Postgres 10.23 after executing flyway migrate are throwing the following sample exception messages/logging below.

I am investigating the differences/what I may be missing that is causing this discrepancy in results (for what I assumed is a 1:1 recreation of the db_model/schema).

doumok@Dou-NCEAS-MBP14 ~ % flyway migrate
doumok@Dou-NCEAS-MBP14 ~ % psql -U vegbank -d vegbank -f /Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:634: ERROR:  insert or update on table "address" violates foreign key constraint "party_id"
DETAIL:  Key (party_id)=(17) is not present in table "party".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:663: ERROR:  duplicate key value violates unique constraint "aux_role_pkey"
DETAIL:  Key (role_id)=(16) already exists.
CONTEXT:  COPY aux_role, line 1
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:49255: ERROR:  insert or update on table "classcontributor" violates foreign key constraint "commclass_id"
DETAIL:  Key (commclass_id)=(3745) is not present in table "commclass".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:151688: ERROR:  insert or update on table "commclass" violates foreign key constraint "observation_id"
DETAIL:  Key (observation_id)=(16371) is not present in table "observation".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:190657: ERROR:  insert or update on table "commconcept" violates foreign key constraint "commname_id"
DETAIL:  Key (commname_id)=(176505) is not present in table "commname".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:225042: ERROR:  insert or update on table "commcorrelation" violates foreign key constraint "commstatus_id"
DETAIL:  Key (commstatus_id)=(2628) is not present in table "commstatus".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:328001: ERROR:  insert or update on table "comminterpretation" violates foreign key constraint "commclass_id"
DETAIL:  Key (commclass_id)=(149488) is not present in table "commclass".
COPY 0
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:451718: ERROR:  insert or update on table "commname" violates foreign key constraint "reference_id"
DETAIL:  Key (reference_id)=(32) is not present in table "reference".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:490767: ERROR:  insert or update on table "commstatus" violates foreign key constraint "commconcept_id"
DETAIL:  Key (commconcept_id)=(29763) is not present in table "commconcept".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:637536: ERROR:  insert or update on table "commusage" violates foreign key constraint "commname_id"
DETAIL:  Key (commname_id)=(4851) is not present in table "commname".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:637942: ERROR:  insert or update on table "coverindex" violates foreign key constraint "covermethod_id"
DETAIL:  Key (covermethod_id)=(1) is not present in table "covermethod".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:637981: ERROR:  insert or update on table "covermethod" violates foreign key constraint "reference_id"
DETAIL:  Key (reference_id)=(27) is not present in table "reference".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:637997: ERROR:  duplicate key value violates unique constraint "dba_confidentialitystatus_pkey"
DETAIL:  Key (confidentialitystatus)=(0) already exists.
CONTEXT:  COPY dba_confidentialitystatus, line 1
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:638024: ERROR:  duplicate key value violates unique constraint "dba_cookie_pkey"
DETAIL:  Key (cookie_id)=(18) already exists.
CONTEXT:  COPY dba_cookie, line 1
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:638051: ERROR:  duplicate key value violates unique constraint "dba_cookielabels_pkey"
DETAIL:  Key (cookielabel_id)=(18) already exists.
CONTEXT:  COPY dba_cookielabels, line 1
COPY 9
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:638077: ERROR:  duplicate key value violates unique constraint "dba_datamodelversion_pkey"
DETAIL:  Key (dba_datamodelversion_id)=(1) already exists.
CONTEXT:  COPY dba_datamodelversion, line 1
COPY 2658
COPY 594
COPY 866
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:642228: ERROR:  duplicate key value violates unique constraint "dba_onerow_pkey"
DETAIL:  Key (dba_onerow_id)=(1) already exists.
CONTEXT:  COPY dba_onerow, line 1
COPY 2233737
COPY 55
COPY 114063
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:3039681: ERROR:  insert or update on table "definedvalue" violates foreign key constraint "userdefined_id"
DETAIL:  Key (userdefined_id)=(7) is not present in table "userdefined".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:3077220: ERROR:  insert or update on table "disturbanceobs" violates foreign key constraint "observation_id"
DETAIL:  Key (observation_id)=(81850) is not present in table "observation".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:3078730: ERROR:  insert or update on table "embargo" violates foreign key constraint "plot_id"
DETAIL:  Key (plot_id)=(24806) is not present in table "plot".
COPY 0
COPY 410177
COPY 44949
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:3598451: ERROR:  insert or update on table "namedplace" violates foreign key constraint "reference_id"
DETAIL:  Key (reference_id)=(5) is not present in table "reference".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:3663181: ERROR:  insert or update on table "namedplacecorrelation" violates foreign key constraint "parentplace_id"
DETAIL:  Key (parentplace_id)=(420) is not present in table "namedplace".
COPY 0
COPY 0
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:3778829: ERROR:  value too long for type character varying(30)
CONTEXT:  COPY observation, line 147, column hydrologicregime: "non-tidal - intermittently flooded"
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:3943858: ERROR:  insert or update on table "observationcontributor" violates foreign key constraint "observation_id"
DETAIL:  Key (observation_id)=(2948) is not present in table "observation".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:3947680: ERROR:  insert or update on table "observationsynonym" violates foreign key constraint "synonymobservation_id"
DETAIL:  Key (synonymobservation_id)=(26047) is not present in table "observation".
COPY 4023
COPY 0
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:4186554: ERROR:  insert or update on table "place" violates foreign key constraint "plot_id"
DETAIL:  Key (plot_id)=(3004) is not present in table "plot".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:4479734: ERROR:  insert or update on table "plantconcept" violates foreign key constraint "plantname_id"
DETAIL:  Key (plantname_id)=(458945) is not present in table "plantname".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:4611122: ERROR:  insert or update on table "plantcorrelation" violates foreign key constraint "plantstatus_id"
DETAIL:  Key (plantstatus_id)=(41345) is not present in table "plantstatus".
COPY 0
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:5061313: ERROR:  insert or update on table "plantname" violates foreign key constraint "reference_id"
DETAIL:  Key (reference_id)=(33) is not present in table "reference".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:5335100: ERROR:  insert or update on table "plantstatus" violates foreign key constraint "plantconcept_id"
DETAIL:  Key (plantconcept_id)=(152246) is not present in table "plantconcept"."psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:6267105: ERROR:  insert or update on table "plantusage" violates foreign key constraint "plantname_id"
DETAIL:  Key (plantname_id)=(462013) is not present in table "plantname".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:6382390: ERROR:  insert or update on table "plot" violates foreign key constraint "reference_id"
DETAIL:  Key (reference_id)=(49915) is not present in table "reference".
COPY 261
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:6382858: ERROR:  insert or update on table "projectcontributor" violates foreign key constraint "role_id"
DETAIL:  Key (role_id)=(38) is not present in table "aux_role".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:6383336: ERROR:  insert or update on table "reference" violates foreign key constraint "referencejournal_id"
DETAIL:  Key (referencejournal_id)=(6) is not present in table "referencejournal".
COPY 0
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:6383558: ERROR:  insert or update on table "referencecontributor" violates foreign key constraint "reference_id"
DETAIL:  Key (reference_id)=(27) is not present in table "reference".
COPY 220
COPY 152
COPY 39607
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:6490469: ERROR:  insert or update on table "soilobs" violates foreign key constraint "observation_id"
DETAIL:  Key (observation_id)=(81851) is not present in table "observation".
COPY 34610
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:7125336: ERROR:  insert or update on table "stemcount" violates foreign key constraint "taxonimportance_id"
DETAIL:  Key (taxonimportance_id)=(6250802) is not present in table "taxonimportance".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:7133715: ERROR:  insert or update on table "stemlocation" violates foreign key constraint "stemcount_id"
DETAIL:  Key (stemcount_id)=(11220) is not present in table "stemcount".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:7480820: ERROR:  insert or update on table "stratum" violates foreign key constraint "observation_id"
DETAIL:  Key (observation_id)=(83116) is not present in table "observation".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:7480849: ERROR:  insert or update on table "stratummethod" violates foreign key constraint "reference_id"
DETAIL:  Key (reference_id)=(50803) is not present in table "reference".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:7481061: ERROR:  insert or update on table "stratumtype" violates foreign key constraint "stratummethod_id"
DETAIL:  Key (stratummethod_id)=(1) is not present in table "stratummethod".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:7481837: ERROR:  insert or update on table "taxonalt" violates foreign key constraint "taxoninterpretation_id"
DETAIL:  Key (taxoninterpretation_id)=(1227539) is not present in table "taxoninterpretation".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:7133715: ERROR:  insert or update on table "stemlocation" violates foreign key constraint "stemcount_id"
DETAIL:  Key (stemcount_id)=(11220) is not present in table "stemcount".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:7480820: ERROR:  insert or update on table "stratum" violates foreign key constraint "observation_id"
DETAIL:  Key (observation_id)=(83116) is not present in table "observation".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:7480849: ERROR:  insert or update on table "stratummethod" violates foreign key constraint "reference_id"
DETAIL:  Key (reference_id)=(50803) is not present in table "reference".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:7481061: ERROR:  insert or update on table "stratumtype" violates foreign key constraint "stratummethod_id"
DETAIL:  Key (stratummethod_id)=(1) is not present in table "stratummethod".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:7481837: ERROR:  insert or update on table "taxonalt" violates foreign key constraint "taxoninterpretation_id"
DETAIL:  Key (taxoninterpretation_id)=(1227539) is not present in table "taxoninterpretation".
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:11086744: ERROR:  insert or update on table "taxonimportance" violates foreign key constraint "taxonobservation_id"
DETAIL:  Key (taxonobservation_id)=(538639) is not present in table "taxonobservation".
....
// Many failed record inserts
....
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190866: error: invalid command \N
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190867: error: invalid command \.
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190874: ERROR:  syntax error at or near "1123413"
LINE 1: 1123413 198036 VB.Ob.16534.INW27922 observation
        ^
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190886: error: invalid command \N
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190887: error: invalid command \N
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190888: error: invalid command \N
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190889: error: invalid command \N
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190890: error: invalid command \N
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190895: error: invalid command \N
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190896: error: invalid command \N
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190897: error: invalid command \N
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190898: error: invalid command \N
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190899: error: invalid command \N
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190909: error: invalid command \N
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190910: error: invalid command \.
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190917: ERROR:  syntax error at or near "1"
LINE 1: 1 Cowardin System National Wetland classification system fro...
        ^
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17190918: error: invalid command \.
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
psql:/Users/doumok/Code/testing/vegbank_2024-07-17_09h23m.Wednesday_RecordsOnly.sql:17191998: ERROR:  end-of-copy marker corrupt
CONTEXT:  COPY usr, line 1033

doulikecookiedough commented 4 weeks ago

Update:

I double checked all my commands, files... and noticed I was not using the data only dump file I had thought I was restoring from, but instead a manually created one.

After using the correct data only dump file (and with the existing flyway migrations I put together in develop), some of the same errors persist - but some of the foreign key constraint issues also seem to have been resolved. The remaining exceptions feel much more manageable :slightly_smiling_face:.

SET
SET
SET
SET
COPY 4024
COPY 621
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:4706: ERROR:  duplicate key value violates unique constraint "aux_role_pkey"
DETAIL:  Key (role_id)=(16) already exists.
CONTEXT:  COPY aux_role, line 1
COPY 220
COPY 470
COPY 31
COPY 115277
COPY 261
COPY 34610
COPY 21
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:271284: ERROR:  value too long for type character varying(30)
CONTEXT:  COPY observation, line 147, column hydrologicregime: "non-tidal - intermittently flooded"
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:373717: ERROR:  insert or update on table "commclass" violates foreign key constraint "observation_id"
DETAIL:  Key (observation_id)=(16371) is not present in table "observation".
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:422309: ERROR:  insert or update on table "classcontributor" violates foreign key constraint "commclass_id"
DETAIL:  Key (commclass_id)=(3745) is not present in table "commclass".
COPY 123701
COPY 38961
COPY 39041
COPY 34377
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:761380: ERROR:  insert or update on table "comminterpretation" violates foreign key constraint "commclass_id"
DETAIL:  Key (commclass_id)=(149488) is not present in table "commclass".
COPY 0
COPY 146761
COPY 398
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:908579: ERROR:  duplicate key value violates unique constraint "dba_confidentialitystatus_pkey"
DETAIL:  Key (confidentialitystatus)=(0) already exists.
CONTEXT:  COPY dba_confidentialitystatus, line 1
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:908606: ERROR:  duplicate key value violates unique constraint "dba_cookie_pkey"
DETAIL:  Key (cookie_id)=(18) already exists.
CONTEXT:  COPY dba_cookie, line 1
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:908633: ERROR:  duplicate key value violates unique constraint "dba_cookielabels_pkey"
DETAIL:  Key (cookielabel_id)=(18) already exists.
CONTEXT:  COPY dba_cookielabels, line 1
COPY 9
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:908659: ERROR:  duplicate key value violates unique constraint "dba_datamodelversion_pkey"
DETAIL:  Key (dba_datamodelversion_id)=(1) already exists.
CONTEXT:  COPY dba_datamodelversion, line 1
COPY 2658
COPY 594
COPY 866
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:912810: ERROR:  duplicate key value violates unique constraint "dba_onerow_pkey"
DETAIL:  Key (dba_onerow_id)=(1) already exists.
CONTEXT:  COPY dba_onerow, line 1
COPY 2233737
COPY 55
COPY 114063
COPY 35
COPY 49566
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:3347845: ERROR:  insert or update on table "disturbanceobs" violates foreign key constraint "observation_id"
DETAIL:  Key (observation_id)=(81850) is not present in table "observation".
COPY 1502
COPY 0
COPY 410177
COPY 44949
COPY 64563
COPY 64722
COPY 0
COPY 0
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:4098851: ERROR:  insert or update on table "observationcontributor" violates foreign key constraint "observation_id"
DETAIL:  Key (observation_id)=(2948) is not present in table "observation".
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:4102673: ERROR:  insert or update on table "observationsynonym" violates foreign key constraint "synonymobservation_id"
DETAIL:  Key (synonymobservation_id)=(26047) is not present in table "observation".
COPY 0
COPY 234827
COPY 450175
COPY 293172
COPY 273779
COPY 131380
COPY 0
COPY 931997
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:6418266: ERROR:  insert or update on table "projectcontributor" violates foreign key constraint "role_id"
DETAIL:  Key (role_id)=(38) is not present in table "aux_role".
COPY 0
COPY 152
COPY 206
COPY 39607
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:6525171: ERROR:  insert or update on table "soilobs" violates foreign key constraint "observation_id"
DETAIL:  Key (observation_id)=(81851) is not present in table "observation".
COPY 204
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:6872488: ERROR:  insert or update on table "stratum" violates foreign key constraint "observation_id"
DETAIL:  Key (observation_id)=(83116) is not present in table "observation".
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:8757286: ERROR:  insert or update on table "taxonobservation" violates foreign key constraint "observation_id"
DETAIL:  Key (observation_id)=(83033) is not present in table "observation".
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:12362193: ERROR:  insert or update on table "taxonimportance" violates foreign key constraint "taxonobservation_id"
DETAIL:  Key (taxonobservation_id)=(538639) is not present in table "taxonobservation".
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:12962442: ERROR:  insert or update on table "stemcount" violates foreign key constraint "taxonimportance_id"
DETAIL:  Key (taxonimportance_id)=(6250802) is not present in table "taxonimportance".
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:12970821: ERROR:  insert or update on table "stemlocation" violates foreign key constraint "stemcount_id"
DETAIL:  Key (stemcount_id)=(11220) is not present in table "stemcount".
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:15497093: ERROR:  insert or update on table "taxoninterpretation" violates foreign key constraint "taxonobservation_id"
DETAIL:  Key (taxonobservation_id)=(2865915) is not present in table "taxonobservation".
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:15497869: ERROR:  insert or update on table "taxonalt" violates foreign key constraint "taxoninterpretation_id"
DETAIL:  Key (taxoninterpretation_id)=(1227539) is not present in table "taxoninterpretation".
COPY 404
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:15498288: ERROR:  relation "public.temp_ks_commconcept_acccodelist" does not exist
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:15498414: error: invalid command \.
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:15498421: ERROR:  syntax error at or near "urn"
LINE 1: urn:lsid:ecoobs.vegbank.org:commConcept:17601-{88B33680-6A90...
        ^
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:15498422: error: invalid command \N
// many of these, seems that \N is supposed to represent null and is not recognized correctly
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:17189577: error: invalid command \.
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:17189584: ERROR:  syntax error at or near "1123413"
LINE 1: 1123413 198036 VB.Ob.16534.INW27922 observation
        ^
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:17189585: error: invalid command \.
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0

doulikecookiedough commented 3 weeks ago

Note: Work in progress can be found in branch feature-29-constraint-migration. I will be cleaning up this branch and documenting the steps I've taken in more detail so that it is easy to replicate (and thus easier for the team to assist with feedback).

A very exciting update - I was able to successfully load all the records from the data-only dump file by:

Removing the alter table and adding constraints SQL from V1.0__vegbank.sql
- Adding new migration file with constraints to be added at the end V1.12__add_constraints.sql
- Cannot execute this one successfully after loading data, more info at the end of this comment
Disabling (commenting out completely) the V1.6_vegbank_populate_configtables.sql migration file

Adding a single create table + sequence code to V1.0__vegbank.sql

CREATE TABLE temp_ks_commconcept_acccodelist (
    accessioncode character varying(255)
);

CREATE SEQUENCE dba_preassignacccode_dba_requestnumber_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

Running flyway -target=1.11 migrate
Loading the data-only dump file, only one exception with the known issue of hydrologicregime
Output below for quick reference

SET
SET
SET
SET
SET
 set_config 
------------

(1 row)

SET
SET
SET
SET
COPY 4024
COPY 621
COPY 21
COPY 220
COPY 470
COPY 31
COPY 115277
COPY 261
COPY 34610
COPY 21
psql:/Users/doumok/Code/testing/vegbank_dataonly_20240814.sql:271284: ERROR:  value too long for type character varying(30)
CONTEXT:  COPY observation, line 147, column hydrologicregime: "non-tidal - intermittently flooded"
COPY 102425
COPY 48584
COPY 123701
COPY 38961
COPY 39041
COPY 34377
COPY 102951
COPY 0
COPY 146761
COPY 398
COPY 8
COPY 19
COPY 19
COPY 9
COPY 1
COPY 2658
COPY 594
COPY 866
COPY 1
COPY 2233737
COPY 55
COPY 114063
COPY 35
COPY 49566
COPY 37531
COPY 1502
COPY 0
COPY 410177
COPY 44949
COPY 64563
COPY 64722
COPY 0
COPY 0
COPY 165021
COPY 3814
COPY 0
COPY 234827
COPY 450175
COPY 293172
COPY 273779
COPY 131380
COPY 0
COPY 931997
COPY 191
COPY 0
COPY 152
COPY 206
COPY 39607
COPY 66900
COPY 204
COPY 347097
COPY 1884790
COPY 3604899
COPY 600241
COPY 8371
COPY 2526264
COPY 768
COPY 404
COPY 125
COPY 24099
COPY 293137
COPY 1033
COPY 106
COPY 578
COPY 1372162
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0

However, I am unable to apply the constraints afterwards. I think this is a promising direction, and will continue investigating.

doumok@Dou-NCEAS-MBP14 ~ % flyway migrate
A more recent version of Flyway is available. Find out more about Flyway 10.17.2 at https://rd.gt/3rXiSlV

Flyway Community Edition 10.17.0 by Redgate

See release notes here: https://rd.gt/416ObMi
Database: jdbc:postgresql://localhost:5432/vegbank2 (PostgreSQL 10.23)
Successfully validated 13 migrations (execution time 00:00.109s)
Current version of schema "public": 1.11
Migrating schema "public" to version "1.12 - add constraints"
ERROR: Migration of schema "public" to version "1.12 - add constraints" failed! Changes successfully rolled back.
ERROR: Script V1.12__add_constraints.sql failed
----------------------------------------
SQL State  : 23503
Error Code : 0
Message    : ERROR: insert or update on table "commclass" violates foreign key constraint "observation_id"
  Detail: Key (observation_id)=(16371) is not present in table "observation".
Location   : /Users/doumok/Code/vegbank2/migrations/V1.12__add_constraints.sql (/Users/doumok/Code/vegbank2/migrations/V1.12__add_constraints.sql)
Line       : 267
Statement  : Run Flyway with -X option to see the actual statement causing the problem

Caused by: Script V1.12__add_constraints.sql failed
----------------------------------------
SQL State  : 23503
Error Code : 0
Message    : ERROR: insert or update on table "commclass" violates foreign key constraint "observation_id"
  Detail: Key (observation_id)=(16371) is not present in table "observation".
Location   : /Users/doumok/Code/vegbank2/migrations/V1.12__add_constraints.sql (/Users/doumok/Code/vegbank2/migrations/V1.12__add_constraints.sql)
Line       : 267
Statement  : Run Flyway with -X option to see the actual statement causing the problem

Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "commclass" violates foreign key constraint "observation_id"
  Detail: Key (observation_id)=(16371) is not present in table "observation".

doulikecookiedough commented 3 weeks ago

Update:

Successfully restored vegbank's data-only dump file into a vegbank db created via the flyway migration process. The changes/process can be found via Feature-29: Flyway Migration with Data-Only Dump File. This has been merged into develop.

Next Step: Kubernetes Depoyment and Set-up

doulikecookiedough commented 3 weeks ago

The restoration process described above can also be directly applied to a Postgres 12 (local server) or 16 (docker) instance without exceptions.

mbjones commented 1 day ago

This ticket has successfully migrated to newer postgres using Flyway. Moving the TODO items for building a helm chart to a separate issue (#33). This is now ready for review for the alpha1 release.

NCEAS / vegbank2

Deploy Existing Postgres DB #29