Docker部署SonarQube与PostgreSQL报错：active_rule_parameters表已存在

通过Docker部署Spring Boot+PostgreSQL项目，执行docker-compose up -d --build后容器状态正常，但PostgreSQL和SonarQube日志报错。

SonarQube错误日志 #

2025-03-13 19:03:37 Caused by: java.lang.IllegalStateException: Fail to execute CREATE TABLE active_rule_parameters (uuid VARCHAR (40) NOT NULL,value VARCHAR (4000) NULL,rules_parameter_key VARCHAR (128) NULL,active_rule_uuid VARCHAR (40) NOT NULL,rules_parameter_uuid VARCHAR (40) NOT NULL, CONSTRAINT pk_active_rule_parameters PRIMARY KEY (uuid))
Caused by: org.postgresql.util.PSQLException: ERROR: relation "active_rule_parameters" already exists

PostgreSQL错误日志 #

2025-03-13 19:02:48 2025-03-13 16:02:48.584 UTC [494] ERROR:  relation "active_rule_parameters" already exists
2025-03-13 19:02:48 2025-03-13 16:02:48.584 UTC [494] STATEMENT:  CREATE TABLE active_rule_parameters (uuid VARCHAR (40) NOT NULL,value VARCHAR (4000) NULL,rules_parameter_key VARCHAR (128) NULL,active_rule_uuid VARCHAR (40) NOT NULL,rules_parameter_uuid VARCHAR (40) NOT NULL, CONSTRAINT pk_active_rule_parameters PRIMARY KEY (uuid))

docker-compose.yml配置 #

services:

  postgres:
    image: postgres:latest
    container_name: postgres-container
    restart: always
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - POSTGRES_DB=footballteamdatabase
    volumes:
      - postgres_data:/var/lib/postgresql/data
    networks:
      - footballteamapi_network

  pgadmin:
    image: dpage/pgadmin4:latest
    container_name: pgadmin-container
    restart: always
    ports:
      - "5050:80"
    environment:
      - PGADMIN_DEFAULT_EMAIL=admin@pgadmin.com
      - PGADMIN_DEFAULT_PASSWORD=admin
    depends_on:
      - postgres
    networks:
      - footballteamapi_network

  footballteamapi:
    image: 'footballteamapi:latest'
    build:
      context: .
      dockerfile: Dockerfile
    container_name: footballteamapi
    restart: on-failure
    env_file:
      - .env  # Use the .env file for environment variables
    ports:
      - "3112:3112"
    environment:
      - server.port=3112
      - FOOTBALL_TEAM_DB_IP=postgres
      - FOOTBALL_TEAM_DB_PORT=5432
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - JAVA_OPTS=-Dname=footballteamapi
    depends_on:
      - postgres
      - sonarqube
    networks:
      - footballteamapi_network

  sonarqube:
    image: sonarqube:latest
    container_name: sonarqube
    restart: always
    ports:
      - "9000:9000"
    environment:
      - SONAR_JDBC_URL=jdbc:postgresql://postgres:5432/footballteamdatabase
      - SONAR_JDBC_USERNAME=${POSTGRES_USER}
      - SONAR_JDBC_PASSWORD=${POSTGRES_PASSWORD}
    volumes:
      - sonarqube_data:/opt/sonarqube/data
      - sonarqube_extensions:/opt/sonarqube/extensions
      - sonarqube_logs:/opt/sonarqube/logs
    depends_on:
      - postgres
    networks:
      - footballteamapi_network

  prometheus:
    image: prom/prometheus:latest
    container_name: prometheus
    restart: unless-stopped
    ports:
      - "9090:9090"
    volumes:
      - ./data/prometheus/config:/etc/prometheus/
    command:
      - '--config.file=/etc/prometheus/prometheus.yml'
    networks:
      - footballteamapi_network

  grafana:
    image: "grafana/grafana-oss:latest"
    pull_policy: always
    container_name: grafana
    restart: unless-stopped
    ports:
      - "3000:3000"
    volumes:
      - ./data/grafana:/var/lib/grafana
    environment:
      - GF_SECURITY_ADMIN_PASSWORD=admin
      - GF_SERVER_DOMAIN=localhost
    networks:
      - footballteamapi_network

  loki:
    image: grafana/loki:latest
    container_name: loki
    restart: unless-stopped
    ports:
      - "3100:3100"
    volumes:
      - ./data/loki/config/loki-config.yaml:/etc/loki/loki-config.yaml
    networks:
      - footballteamapi_network

volumes:
  postgres_data:
  sonarqube_data:
  sonarqube_extensions:
  sonarqube_logs:

networks:
  footballteamapi_network:
    driver: bridge

问题根源是SonarQube和Spring Boot项目共享了同一个PostgreSQL数据库，SonarQube初始化时尝试创建专属表，这些表要么因重复启动残留，要么和项目表产生冲突。

解决步骤：

1. 给SonarQube分配独立数据库
   修改docker-compose.yml中SonarQube的环境变量，指定专属数据库：

   sonarqube:
     # 保留其他原有配置
     environment:
       - SONAR_JDBC_URL=jdbc:postgresql://postgres:5432/sonarqubedatabase
       - SONAR_JDBC_USERNAME=${POSTGRES_USER}
       - SONAR_JDBC_PASSWORD=${POSTGRES_PASSWORD}
   

   无需手动创建该数据库，SonarQube初始化时会自动完成（前提是PostgreSQL用户拥有建库权限）。

2. 清理残留数据（可选，若有旧数据干扰）
   如果之前共享库中已存在SonarQube的表，彻底清理后重新部署：

   • 停止并删除所有容器及绑定卷：docker-compose down -v
   • 重新构建启动：docker-compose up -d --build
     注意：此操作会清空所有数据库数据，有重要数据请先备份。

3. 验证隔离效果
   启动后通过pgadmin分别连接footballteamdatabase和sonarqubedatabase，确认两个库的表完全独立，无冲突。

不同服务使用独立数据库是运维最佳实践，能避免表名冲突、权限问题及数据互相干扰。若不想删除现有数据，可手动进入PostgreSQL容器删除SonarQube相关表，再修改SonarQube的JDBC地址指向新数据库。

内容的提问来源于stack exchange，提问作者Sercan Noyan Germiyanoğlu