Skip to content

PostgreSQL

PostgreSQL is a relational database that we use to store various data.

Usage and Ports

PostgreSQL is used by the following services:

  • The controller services (e.g. controller, credential-service, app-service, ...)
  • Argo workflows
  • Keycloak
  • Vault

We have 9 databases:

  • app_service
  • contract_service
  • argo
  • controller
  • keycloak
  • postgres
  • table_security_mapping
  • transaction_service
  • workflows

Each service has its own role that it can use to access the database it needs. For example, the keycloak role only has access to the keycloak database.

The credentials for the initial postgres user are stored in the secret postgresql-credentials-secret. The credentials for the other roles can be found in postgresql-user-credentials-secret.

Database Schema Management

EuroDaT microservices manage their database schema with Flyway . It is checked if the schema is up-to-date every time a pod starts. Flyway keeps a schema history table, therefore an already applied migration script must not be changed.

The migration SQL scripts are located in resources/db/migration for each service. The naming convention is Vx_y_z__description.sql where x_y_z is the next available semantic version followed by a brief description after two underscores.

Connect to a PostgreSQL Instance

Here a few ways to connect to a PostgreSQL instance, which is running on Kubernetes.

Connect Directly

Log into the pods PostgreSQL via:

kubectl -n <NAMESPACE> exec -it eurodat-postgresql-0 -- psql -U postgres -p <port>

Connect via IntelliJ

Connect to PostgreSQL inside your IDE by:

  • Forward the correct pod with the corresponding port:
  kubectl -n <NAMESPACE> port-forward <postgres-pod-id> <local-port>:<port>
  • Inside IntelliJ click on the top right Database tab
  • Click on + > Data Source > PostgreSQL
  • Insert the correct credentials (User: postgres, Password: $YOUR_PASSWORD) and edit the port in the URL section with e.g. jdbc:postgresql://localhost:<your-favorite-port>/postgres