mdyingstar
Reputation: 73
Data from spring application is not being persisted in the dockerized Postgres database
I would really appreciate any help. I've been looking for a solution to my problem all day but I can't find anything that works. I have several docker containers: a spring application, a postgres database, keycloak, and a postgres database backup. When I create a new entity through my endpoint using Postman or curl, I can also retrieve it through another endpoint. However, nothing is being saved in the database. When I restart the spring container, all the data is gone! I just don't understand why the data is not being persisted in the database.
this is my docker-compose file:
services:
sales-service:
container_name: service-container
image: "mab123/sales-service:latest"
environment:
PG_HOST: postgres-container
ports:
- "8081:8081"
networks:
- internal
depends_on:
- postgres
postgres:
container_name: postgres-container
image: postgres
environment:
POSTGRES_USER: ${POSTGRES_USER:-postgres}
POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-postgres}
POSTGRES_MULTIPLE_DATABASES: sales, keycloak
POSTGRES_INITDB_ARGS: "--auth-host=scram-sha-256 --auth-local=scram-sha-256"
PGDATA: /data/postgres
volumes:
- postgres:/data/postgres
- ./src/main/resources/pg-init-scripts:/docker-entrypoint-initdb.d
ports:
- "5432:5432"
restart: unless-stopped
healthcheck:
test: "PGPASSWORD=${POSTGRES_PASSWORD:-postgres} pg_isready -h 127.0.0.1 -U ${POSTGRES_USER:-postgres} -d sales"
networks:
- internal
flyway:
container_name: flyway
image: flyway/flyway
command: -url=jdbc:postgresql://postgres:5432/sales -user=postgres -password=postgres -locations=filesystem:/flyway/sql -connectRetries=60 migrate
volumes:
- ./src/main/resources/db/migration/:/flyway/sql/
depends_on:
postgres:
condition: service_healthy
networks:
- internal
keycloak:
container_name: keycloak-container
image: "quay.io/keycloak/keycloak:latest"
volumes:
- ./src/main/resources/keycloak/realms.json:/opt/keycloak/data/import/realms.json
env_file:
- src/main/resources/keycloak/keycloak.env
ports:
- "8080:8080"
restart: unless-stopped
entrypoint: [ "/opt/keycloak/bin/kc.sh", "start-dev", "--import-realm" ]
networks:
internal:
aliases:
- keycloak.keycon.com
depends_on:
- postgres
volumes:
postgres:
networks:
internal:
I have not found anything that could be wrong in the docker-compose file, but if you have any ideas, please let me know.
Here are my application-dev settings:
spring:
datasource:
url: jdbc:postgresql://${PG_HOST:localhost}:5432/sales
username: postgres
password: postgres
flyway:
datasources:
default:
enabled: true
PG_HOST is the name of the postgres docker container.
As previously mentioned, when I create a new entity, I receive a JSON response containing the DTO object with the assigned ID within the entity. However, when I access the Postgres database container and execute a psql command, the result displays zero rows.
Upvotes: 4
Views: 926
Answers (1)
Bench Vue
Reputation: 9310
It is interesting Topic, I never figure out but from your initial idea, it give me how to shows content from database. Thanks!
I can't browser your sales database due to no your spring boot source but I can see keycloak database.
This is my experiment for keycloak database from postgres docker.
I think your sales database not public or something wrong.
database name is keycloak and user is keycloak in Postgres
It will use later to access database.
I am sure, this steps give an idea how to see your sales database.
I hope to help to debug between keycloak and database.
docker-compose.yml
version: '3.7'
services:
postgres:
image: postgres
volumes:
- postgres_data:/var/lib/postgresql/data
environment:
POSTGRES_DB: keycloak
POSTGRES_USER: keycloak
POSTGRES_PASSWORD: password
keycloak:
image: quay.io/keycloak/keycloak:18.0.2-legacy
environment:
DB_VENDOR: POSTGRES
DB_ADDR: postgres
DB_DATABASE: keycloak
DB_USER: keycloak
DB_SCHEMA: public
DB_PASSWORD: password
KEYCLOAK_USER: admin
KEYCLOAK_PASSWORD: admin
ports:
- 8080:8080 # Expose to user with localhost:8080
restart: always
depends_on:
- postgres
volumes:
postgres_data: # Keycloack volume
driver: local
#1 Step 1, get postgres container ID
docker ps -a
->
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
c48f3f8fea9f quay.io/keycloak/keycloak:18.0.2-legacy "/opt/jboss/tools/do…" 54 minutes ago Up 54 minutes 0.0.0.0:8080->8080/tcp, 8443/tcp docker-compose-keycloak-1
254a2038d581 postgres "docker-entrypoint.s…" 54 minutes ago Up 54 minutes 5432/tcp docker-compose-postgres-1
#2 Step 2, go inside postgres container
(* note: I am using Windows git bash, winpty is not need in Linux)
winpty docker exec -it 254a2038d581 bin/bash
-->
root@254a2038d581:/#
#3 Step 3, check Linux version
cat /etc/os-release
-->
PRETTY_NAME="Debian GNU/Linux 11 (bullseye)"
NAME="Debian GNU/Linux"
VERSION_ID="11"
VERSION="11 (bullseye)"
And check who am I
whoami
-->
root
#4 Step 4, add user 'keycloak` and add sudo user
(password is password)
adduser keycloak
-->
Adding user `keycloak' ...
Adding new group `keycloak' (1000) ...
Adding new user `keycloak' (1000) with group `keycloak' ...
usermod -aG sudo keycloak
#5 Step 5, switch user keycloak and connect database
su keycloak
-->
keycloak@254a2038d581:/$
psql -U keycloak -w
-->
psql (14.5 (Debian 14.5-1.pgdg110+1))
Type "help" for help.
keycloak=#
#6 Step 6, get connected information
\conninfo
-->
You are connected to database "keycloak" as user "keycloak" via socket in "/var/run/postgresql" at port "5432".
#7 Step 7, list tables and schemas
\dt
-->
List of relations
Schema | Name | Type | Owner
--------+-------------------------------+-------+----------
public | admin_event_entity | table | keycloak
public | associated_policy | table | keycloak
public | authentication_execution | table | keycloak
public | authentication_flow | table | keycloak
public | authenticator_config | table | keycloak
public | authenticator_config_entry | table | keycloak
public | broker_link | table | keycloak
public | client | table | keycloak
public | client_attributes | table | keycloak
public | client_auth_flow_bindings | table | keycloak
public | client_initial_access | table | keycloak
public | client_node_registrations | table | keycloak
public | client_scope | table | keycloak
public | client_scope_attributes | table | keycloak
public | client_scope_client | table | keycloak
public | client_scope_role_mapping | table | keycloak
public | client_session | table | keycloak
public | client_session_auth_status | table | keycloak
public | client_session_note | table | keycloak
public | client_session_prot_mapper | table | keycloak
public | client_session_role | table | keycloak
public | client_user_session_note | table | keycloak
public | component | table | keycloak
public | component_config | table | keycloak
public | composite_role | table | keycloak
public | credential | table | keycloak
public | databasechangelog | table | keycloak
public | databasechangeloglock | table | keycloak
public | default_client_scope | table | keycloak
public | event_entity | table | keycloak
public | fed_user_attribute | table | keycloak
public | fed_user_consent | table | keycloak
public | fed_user_consent_cl_scope | table | keycloak
public | fed_user_credential | table | keycloak
public | fed_user_group_membership | table | keycloak
public | fed_user_required_action | table | keycloak
public | fed_user_role_mapping | table | keycloak
public | federated_identity | table | keycloak
public | federated_user | table | keycloak
public | group_attribute | table | keycloak
public | group_role_mapping | table | keycloak
public | identity_provider | table | keycloak
public | identity_provider_config | table | keycloak
public | identity_provider_mapper | table | keycloak
public | idp_mapper_config | table | keycloak
public | keycloak_group | table | keycloak
public | keycloak_role | table | keycloak
public | migration_model | table | keycloak
public | offline_client_session | table | keycloak
public | offline_user_session | table | keycloak
public | policy_config | table | keycloak
public | protocol_mapper | table | keycloak
public | protocol_mapper_config | table | keycloak
public | realm | table | keycloak
public | realm_attribute | table | keycloak
public | realm_default_groups | table | keycloak
public | realm_enabled_event_types | table | keycloak
public | realm_events_listeners | table | keycloak
public | realm_localizations | table | keycloak
public | realm_required_credential | table | keycloak
public | realm_smtp_config | table | keycloak
public | realm_supported_locales | table | keycloak
public | redirect_uris | table | keycloak
public | required_action_config | table | keycloak
public | required_action_provider | table | keycloak
public | resource_attribute | table | keycloak
public | resource_policy | table | keycloak
public | resource_scope | table | keycloak
public | resource_server | table | keycloak
public | resource_server_perm_ticket | table | keycloak
public | resource_server_policy | table | keycloak
public | resource_server_resource | table | keycloak
public | resource_server_scope | table | keycloak
public | resource_uris | table | keycloak
public | role_attribute | table | keycloak
public | scope_mapping | table | keycloak
public | scope_policy | table | keycloak
public | user_attribute | table | keycloak
public | user_consent | table | keycloak
public | user_consent_client_scope | table | keycloak
public | user_entity | table | keycloak
public | user_federation_config | table | keycloak
public | user_federation_mapper | table | keycloak
public | user_federation_mapper_config | table | keycloak
public | user_federation_provider | table | keycloak
public | user_group_membership | table | keycloak
public | user_required_action | table | keycloak
public | user_role_mapping | table | keycloak
public | user_session | table | keycloak
public | user_session_note | table | keycloak
public | username_login_failure | table | keycloak
public | web_origins | table | keycloak
If you want to more information, \dt+
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------------------+-------+----------+-------------+---------------+------------+-------------
public | admin_event_entity | table | keycloak | permanent | heap | 8192 bytes |
public | associated_policy | table | keycloak | permanent | heap | 8192 bytes |
public | authentication_execution | table | keycloak | permanent | heap | 88 kB |
public | authentication_flow | table | keycloak | permanent | heap | 64 kB |
...
#8 Step 8, clients list and detail information
(if you query SELECT * FROM client; , can get all of fields)
SELECT name, client_id FROM client;
-->
name | client_id
----------------------------------+------------------------
master Realm | master-realm
${client_account} | account
${client_account-console} | account-console
${client_broker} | broker
${client_security-admin-console} | security-admin-console
${client_admin-cli} | admin-cli
| app3
| app1
example Realm | example-realm
| app2
${client_realm-management} | realm-management
${client_account} | account
| demo
${client_account-console} | account-console
${client_broker} | broker
${client_security-admin-console} | security-admin-console
${client_admin-cli} | admin-cli
#9 Step 9, realms list and detail information
SELECT name, id, access_code_lifespan FROM realm;
-->
name | id | access_code_lifespan
---------+--------------------------------------+----------------------
master | master | 60
example | e78f0c77-b44b-48da-850b-9d157e24a439 | 60
#10 Step 10, list user
SELECT * FROM user_entity;
-->
id | email | email_constraint | email_verified | enabled | federation_link | first_name | last_name | realm_id | username | created_timestamp | service_account_client_link | not_before
--------------------------------------+-------+--------------------------------------+----------------+---------+-----------------+------------+-----------+--------------------------------------+----------------------+-------------------+--------------------------------------+------------
c028a1c0-c9e1-449c-a7b4-2bcc0209748f | | fe9c8176-c624-46c4-adcf-26f97bfd7c47 | f | t | | | | master | admin | 1663581309951 | | 0
d87bf317-62fa-42c7-a97e-d354b5f8abab | | 849e6b1c-ee36-49af-8d86-db273571136f | f | t | | | | e78f0c77-b44b-48da-850b-9d157e24a439 | service-account-demo | 1671620871610 | 5ccdb298-b68e-433b-a09e-27b417bc5bd7 | 0
22286745-ca01-4e79-9ead-bf87b6d3ef42 | | 47fac099-52e2-4baa-b97e-a260cbc293bd | f | t | | | | e78f0c77-b44b-48da-850b-9d157e24a439 | user2 | 1671585500197 | | 0
f2b7cd6b-7f6c-4f77-b2f8-a8f406a9fec4 | | 0b2edfb4-476c-40c5-a83f-77c7998e3417 | f | t | | | | e78f0c77-b44b-48da-850b-9d157e24a439 | user3 | 1671586002650 | | 0
4c220068-dba4-414f-909d-979d549e8824 | | 432e3b6c-9697-49ef-bfde-da6e6ba86308 | f | t | | | | e78f0c77-b44b-48da-850b-9d157e24a439 | service-account-app3 | 1669770194446 | b06ed222-f458-4bb5-aeda-7a7ef759ce79 | 0
d9bb2e1e-47b1-417b-b62c-c91ee9903823 | | 6ca1dd29-4f5b-4fb8-a860-52db1a6afecb | f | t | | | | e78f0c77-b44b-48da-850b-9d157e24a439 | service-account-app1 | 1669770220095 | 291ebd07-f061-4a73-84aa-43bffe9d04cb | 0
7f46d52f-130c-4b18-9a73-bc1617edac09 | | 86e5372e-ca08-4616-8191-cc7f15806744 | f | t | | | | e78f0c77-b44b-48da-850b-9d157e24a439 | service-account-app2 | 1669770245105 | f931e207-820a-4f19-b9e5-255be90366ed | 0
2b397d5d-2c54-4cb9-932d-9fdd18d018ba | | 117960f6-5071-42d3-b052-f9e9f420468b | f | t | | | | e78f0c77-b44b-48da-850b-9d157e24a439 | test | 1670617022033 | | 0
417a6295-08b4-4bcc-86a8-ce2c1f158ede | | b835e6a9-627a-414c-9d05-00748afadc47 | f | f | | | | e78f0c77-b44b-48da-850b-9d157e24a439 | user1 | 1671585149910 | | 0
References
PostgreSQL: Show tables in PostgreSQL
Upvotes: 1
Related Questions
- Docker-compose v3 not persisting postgres database
- postgres database doesn't exist in docker container
- Populating a Dockerized PostgreSQL Database through a Dockerized Spring Boot Application
- my spring boot app container can't connect to my postgresql database
- Access postgres docker container with spring boot application
- Why isn't my PostgreSQL data persistent in Docker using volumes?
- dockerized postgres and dockerized Spring boot app
- Docker volume does not persist data
- Postgres in Docker persistent data
- How do i get postgres data to persist using docker-compose