Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add step to sync-wrds-db step function that recreates foreign tables #1050

Open
shawncrawley opened this issue Jan 17, 2025 · 0 comments · May be fixed by #1060
Open

Add step to sync-wrds-db step function that recreates foreign tables #1050

shawncrawley opened this issue Jan 17, 2025 · 0 comments · May be fixed by #1060

Comments

@shawncrawley
Copy link
Collaborator

A shortcoming in our sync-wrds-location-db step function surfaced during our UAT testing for v2.1.8. The man_rnr_* pipeline was failing every run due to issues with the underlying wrds_location3 database as accessed via the VIZ db foreign table connections. The two main errors were:

  1. The "nwm_feature_id" column of the external.full_crosswalk_view had data in it that exceeded the size of its "integer" dtype.
  2. The "external.nwm_routelink_3_0_conus" table does not exist.

To be clear, these were occurring after syncing the underlying wrds_location3 database. After digging, I found that the reason was due to how foreign tables work in PostgreSQL. Essentially a copy of the foreign tables are made on the reference database. This means that if anything about those table schemas change (or if new tables need a foreign connection) then the tables need to be synced or re-synced.

We have code that resyncs these foreign tables as part of our automated Terraform deployment - Core/EC2/RDSBastion/scripts/utils/setup_foreign_tables.tftpl. But that is an ephemeral script that gets executed only once among the first steps of a deployment. We need that same code to be executed as the final step after the wrds_location3 database dump is deployed so that any tweak in the underlying databases can be reflected properly.

The manual step in the meantime to fix the above errors was to execute the following sql:

DROP FOREIGN TABLE external.full_crosswalk_view, nwm_routelink_3_0_conus;
IMPORT FOREIGN SCHEMA public LIMIT TO (full_crosswalk_view, nwm_routelink_3_0_conus) FROM SERVER wrds_location INTO external;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant