Running keycloak along with postgres db via docker compose-CodePudding

I currently have a docker compose file which sets up a psql database and runs around 80 sql files to create the relations for my application. What I want to do now is add a keycloak layer to the application. I'm trying to do this via the compose file as well but I keep getting an error saying that one of my relation already exists. What I think is happening is that when I keycloak service is running its trying to overwrite the database and I don't want that to happen. I'm not sure how to fix this, I've tried a bunch of other methods found online but none of them work.

Here is my compose file (removing some of the sql scripts to make it simpler):

---
version: "3.8"

services:
  database:
    container_name: database
    environment:
    # add multiple schemas
      # POSTGRES_MULTIPLE_DATABASES: ${POSTGRESQL_DATABASE},${POSTGRESQL_KEYCLOAK_DATABASE}
      POSTGRES_USER: ${POSTGRESQL_USER}
      POSTGRES_PASSWORD: ${POSTGRESQL_PASSWORD}
      POSTGRES_DB: ${POSTGRESQL_DATABASE}
      # POSTGRES_KEYCLOAK_USER: ${POSTGRESQL_KEYCLOAK_USER}
      # POSTGRES_KEYCLOAK_PASSWORD: ${POSTGRESQL_KEYCLOAK_PASSWORD}
      # POSTGRES_DB2: ${POSTGRESQL_KEYCLOAK_DATABASE}
    hostname: local
    image: postgres:12
    restart: always
    volumes:
      - /pgdata
      - ./sql/access_attempt.sql:/docker-entrypoint-initdb.d/A.sql
      - ./sql/bceid.sql:/docker-entrypoint-initdb.d/B.sql
      - ./sql/lookup_activitytype.sql:/docker-entrypoint-initdb.d/C.sql
      - ./sql/lookup_gender_pronoun.sql:/docker-entrypoint-initdb.d/D.sql
      - ./sql/client.sql:/docker-entrypoint-initdb.d/E.sql
     
    ports: 
      - "5432:5432"
    networks:
      - db-keycloak

  backend:
    container_name: backend
    entrypoint:
      - "sh"
      - "-c"
      - "npm i && npm run start"
    environment:
      NODE_ENV: development
      POSTGRESQL_HOST: ${POSTGRESQL_HOST}
      POSTGRESQL_USER: ${POSTGRESQL_USER}
      POSTGRESQL_PASSWORD: ${POSTGRESQL_PASSWORD}
      POSTGRESQL_DATABASE: ${POSTGRESQL_DATABASE}
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:3000"]
      interval: 1m30s
      timeout: 10s
      retries: 3
      start_period: 40s
    hostname: backend
    image: node:14
    links:
      - database
    ports:
      - "3001:3000"
    volumes:
      - ./backend:/app:z
      - /app/node_modules
    working_dir: "/app"

  keycloak:
    image: jboss/keycloak:13.0.1
    container_name: keycloak-service
    environment:
      DB_VENDOR: POSTGRES
      DB_ADDR: database
      DB_SCHEMA: public
      DB_DATABASE: ${POSTGRESQL_DATABASE}
      DB_USER: ${POSTGRESQL_USER}
      DB_PASSWORD: ${POSTGRESQL_PASSWORD}
      KEYCLOAK_USER:  ${POSTGRESQL_KEYCLOAK_USER}
      KEYCLOAK_PASSWORD: ${POSTGRESQL_KEYCLOAK_PASSWORD}
    ports:
      - "28080:8080"
    depends_on:
      - database
    restart: always
    links: 
      - database
    networks:
      - db-keycloak

  frontend:
    container_name: frontend
    entrypoint:
      - "sh"
      - "-c"
      - "npm i && npm run start"
    environment:
      NODE_ENV: development
      BACKEND_URL: backend
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:3000"]
      interval: 1m30s
      timeout: 10s
      retries: 3
      start_period: 40s
    hostname: frontend
    image: node:14
    links:
      - backend
    ports:
      - "3000:3000"
    volumes:
      - ./frontend:/app:z
      - /app/node_modules
    working_dir: "/app"

networks:
  db-keycloak:

And here is the error:

18:05:57,142 ERROR [org.keycloak.connections.jpa.updater.liquibase.conn.DefaultLiquibaseConnectionProvider] (ServerService Thread Pool -- 69) Change Set META-INF/jpa-changelog-1.0.0.Final.xml::1.0.0.Final-KEYCLOAK-5461::[email protected] failed.  Error: ERROR: relation "client" already exists [Failed SQL: CREATE TABLE public.CLIENT (DTYPE VARCHAR(31) NOT NULL, ID VARCHAR(36) NOT NULL, ALLOWED_CLAIMS_MASK BIGINT, ENABLED BOOLEAN DEFAULT FALSE NOT NULL, FULL_SCOPE_ALLOWED BOOLEAN DEFAULT FALSE NOT NULL, NAME VARCHAR(255), NOT_BEFORE INT, PUBLIC_CLIENT BOOLEAN DEFAULT FALSE NOT NULL, SECRET VARCHAR(255), BASE_URL VARCHAR(255), BEARER_ONLY BOOLEAN DEFAULT FALSE NOT NULL, MANAGEMENT_URL VARCHAR(255), SURROGATE_AUTH_REQUIRED BOOLEAN DEFAULT FALSE NOT NULL, DIRECT_GRANTS_ONLY BOOLEAN DEFAULT FALSE NOT NULL, REALM_ID VARCHAR(36))]
18:05:57,142 ERROR 
[org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider] (ServerService Thread Pool -- 69) Error has occurred while updating the database: liquibase.exception.MigrationFailedException: Migration failed for change set META-INF/jpa-changelog-1.0.0.Final.xml::1.0.0.Final-KEYCLOAK-5461::[email protected]:
         Reason: liquibase.exception.DatabaseException: ERROR: relation "client" already exists [Failed SQL: CREATE TABLE public.CLIENT (DTYPE VARCHAR(31) NOT NULL, ID VARCHAR(36) NOT NULL, ALLOWED_CLAIMS_MASK BIGINT, ENABLED BOOLEAN DEFAULT FALSE NOT NULL, FULL_SCOPE_ALLOWED BOOLEAN DEFAULT FALSE NOT NULL, NAME VARCHAR(255), NOT_BEFORE INT, PUBLIC_CLIENT BOOLEAN DEFAULT FALSE NOT NULL, SECRET VARCHAR(255), BASE_URL VARCHAR(255), BEARER_ONLY BOOLEAN DEFAULT FALSE NOT NULL, MANAGEMENT_URL VARCHAR(255), SURROGATE_AUTH_REQUIRED BOOLEAN DEFAULT FALSE NOT NULL, DIRECT_GRANTS_ONLY BOOLEAN DEFAULT FALSE NOT NULL, REALM_ID VARCHAR(36))]

CodePudding user response：

According to error your existing postgres database already have table/relation named client. But Keycloak is trying to create client table of its own which is causing this error.

To fix this issue, I would suggest to use two different postgres databases/schemas, one for your application and other for keycloak instance.