how to we want to take a dump of the db #139
Comments
|
Is there no existing postgres cli tool that will do it? Do we need the dump feature in the app? |
|
there is good old fashioned pg_dump/pg_restore approach backup restore depends if you want schema+data ... also complexities arise if you use row security ... baking in backup/restore to the app is also baking in pg dep. |
|
Thank you for sharing, the title is quite vague, I'll add context: Months ago we had a discussion regarding generating the database schema (in development mode) when creating new unit tests, which, in this case, alter the database. What I understood is that the intention would be to obtain the database schema at the end of the tests for later generation of an entity-relationship diagram, as a visual verification step or something like that (I'm not sure). With the focus being on PM-mode, we made some attempts to get the database dump for later diagram generation, until the author of pg_embedded added support for it programmatically https://github.com/theseus-rs/postgresql-embedded/blob/main/postgresql_embedded/tests/dump_command.rs#L10. Overall, I'm still seeing this diagram generation as a development step, not a product step... so I don't yet know if we really need it or how we need this diagram. Since we can run commands (as you mentioned)... still, with the dump.sql, we would need some other external tool for diagram generation using that dump.sql as input. thinking in development-mode only the ideal would be:
and in the end some having and ofc we can always double think to check if we are not creating new unnecessary problems : ) |
|
ah right ... there are tools for automating generation of erd - https://schemaspy.org/ comes to mind (and https://www.schemacrawler.com/downloads.html), otherwise lots of sql editors generate erd as well ... will ask around (and yes looking for non java pref rust options) |
|
even if we want to call that dump via code I found other issue when running the app... ( previously focused on tests only ... when running the app we have this ^) |
|
This technique might be 'clean' enough without wading into the java muck (as opposed to rust muck), with added bonus of emitting mermaid diagrams which default display in github (and text based). |
|
interesting... thanks for sharing 👍 if we continue thinking on the developer-mode diagram approach (run tests and take a look on what is happening with the db) I'm +1 to this and forget the |
|
note: in previous comment I mentioned I found this handy when running PM-mode: erDiagram
advisory {
integer id PK
character_varying identifier
character_varying location
timestamp_with_time_zone modified
timestamp_with_time_zone published
character_varying sha256
character_varying title
timestamp_with_time_zone withdrawn
}
advisory_vulnerability {
integer advisory_id PK,FK
integer vulnerability_id PK,FK
}
affected_package_version_range {
integer id PK
integer advisory_id FK
integer package_version_range_id FK
integer vulnerability_id FK
}
cpe {
integer id PK
character_varying edition
character_varying language
character_varying other
character_varying part
character_varying product
character_varying sw_edition
character_varying target_hw
character_varying target_sw
character_varying update
character_varying vendor
character_varying version
}
cvss3 {
integer advisory_id PK,FK
integer minor_version PK
integer vulnerability_id PK,FK
cvss3_a a
cvss3_ac ac
cvss3_av av
cvss3_c c
cvss3_i i
cvss3_pr pr
cvss3_s s
cvss3_ui ui
}
cvss4 {
integer advisory_id PK,FK
integer minor_version PK
integer vulnerability_id PK,FK
cvss4_ac ac
cvss4_at at
cvss4_av av
cvss4_pr pr
cvss4_sa sa
cvss4_sc sc
cvss4_si si
cvss4_ui ui
cvss4_va va
cvss4_vc vc
cvss4_vi vi
}
fixed_package_version {
integer id PK
integer advisory_id FK
uuid package_version_id FK
integer vulnerability_id FK
}
importer {
character_varying name PK
jsonb configuration
timestamp_with_time_zone last_change
character_varying last_error
timestamp_with_time_zone last_run
timestamp_with_time_zone last_success
uuid revision
integer state
}
importer_report {
uuid id PK
character_varying importer FK
timestamp_with_time_zone creation
character_varying error
jsonb report
}
not_affected_package_version {
integer id PK
integer advisory_id FK
uuid package_version_id FK
integer vulnerability_id FK
}
package {
uuid id PK
character_varying name
character_varying namespace
timestamp_with_time_zone timestamp
character_varying type
}
package_relates_to_package {
uuid left_package_id PK,FK
integer relationship PK,FK
uuid right_package_id PK,FK
integer sbom_id PK,FK
}
package_version {
uuid id PK
uuid package_id FK
timestamp_with_time_zone timestamp
character_varying version
}
package_version_range {
integer id PK
uuid package_id FK
character_varying end
character_varying start
timestamp_with_time_zone timestamp
}
qualified_package {
uuid id PK
uuid package_version_id FK
jsonb qualifiers
timestamp_with_time_zone timestamp
}
relationship {
integer id PK
character_varying description
}
sbom {
integer id PK
character_varying[] authors
character_varying document_id
character_varying location
timestamp_with_time_zone published
character_varying sha256
character_varying title
}
sbom_describes_cpe {
integer cpe_id PK,FK
integer sbom_id PK,FK
}
sbom_describes_package {
uuid qualified_package_id PK,FK
integer sbom_id PK,FK
}
sbom_package {
uuid qualified_package_id PK,FK
integer sbom_id PK,FK
}
seaql_migrations {
character_varying version PK
bigint applied_at
}
vulnerability {
integer id PK
character_varying identifier
timestamp_with_time_zone timestamp
character_varying title
}
vulnerability_description {
integer id PK
integer vulnerability_id FK
character_varying description
character_varying lang
timestamp_with_time_zone timestamp
}
advisory_vulnerability }o--|| advisory: ""
advisory_vulnerability }o--|| vulnerability: ""
affected_package_version_range }o--|| advisory: ""
affected_package_version_range }o--|| vulnerability: ""
affected_package_version_range }o--|| package_version_range: ""
cvss3 }o--|| advisory: ""
cvss3 }o--|| advisory: ""
cvss4 }o--|| advisory: ""
cvss4 }o--|| advisory: ""
fixed_package_version }o--|| advisory: ""
fixed_package_version }o--|| vulnerability: ""
fixed_package_version }o--|| package_version: ""
importer_report }o--|| importer: ""
not_affected_package_version }o--|| advisory: ""
not_affected_package_version }o--|| vulnerability: ""
not_affected_package_version }o--|| package_version: ""
package_relates_to_package }o--|| qualified_package: ""
package_relates_to_package }o--|| relationship: ""
package_relates_to_package }o--|| qualified_package: ""
package_relates_to_package }o--|| sbom: ""
package_version }o--|| package: ""
package_version_range }o--|| package: ""
qualified_package }o--|| package_version: ""
sbom_describes_cpe }o--|| sbom: ""
sbom_describes_cpe }o--|| cpe: ""
sbom_describes_package }o--|| sbom: ""
sbom_describes_package }o--|| qualified_package: ""
sbom_package }o--|| sbom: ""
sbom_package }o--|| qualified_package: ""
vulnerability_description }o--|| vulnerability: ""
not the best detailed option but better than nothing |
2024-05-06T16:47:09.390771Z WARN trustify_server: Authentication is disabled
2024-05-06T16:47:09.390835Z INFO trustify_common::db: connect to postgres://postgres:trustify@localhost:36477/trustify
2024-05-06T16:47:09.523592Z INFO trustify_infrastructure::infra: Infrastructure endpoint is disabled
2024-05-06T16:47:09.523879Z INFO trustify_infrastructure::health::checks::local: received shutdown signal
2024-05-06T16:47:09.524015Z INFO trustify_infrastructure::health::checks::local: check future returnedThe connection string ^:
Copy the output to a mermaid viewer
|
|
@jcrossley3 can we close this ? thanks |
|
Sure! :) |
No description provided.
The text was updated successfully, but these errors were encountered: