WSO2 APIM 4.0.0迁移PostgreSQL时报'tenant_domain'列不存在错误

我按照官方文档将WSO2 APIM 4.0.0的数据库从H2迁移至PostgreSQL，严格执行了所有步骤和产品自带的数据库脚本，但启动后系统报错ERROR: column "tenant_domain" does not exist，且已确认对应表中该列确实存在，相关日志如下：

[2024-01-25 13:54:46,974] ERROR - GlobalThrowableMapper Error while retrieving webhooks subscription request
[2024-01-25 13:54:46,410] ERROR - KeymanagersApiServiceImpl Error while retrieving key manager configurations
org.wso2.carbon.apimgt.api.APIManagementException: Error while retrieving key manager configuration for Resident Key Manager in tenant carbon.super
        at org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.getKeyManagerConfigurationByName_aroundBody392(ApiMgtDAO.java:8720) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
        at org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.getKeyManagerConfigurationByName(ApiMgtDAO.java:8713) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
        at org.wso2.carbon.apimgt.impl.service.KeyMgtRegistrationService.registerDefaultKeyManager_aroundBody0(KeyMgtRegistrationService.java:51) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
        at org.wso2.carbon.apimgt.impl.service.KeyMgtRegistrationService.registerDefaultKeyManager(KeyMgtRegistrationService.java:47) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
        at org.wso2.carbon.apimgt.impl.APIAdminImpl.getKeyManagerConfigurationsByTenant_aroundBody28(APIAdminImpl.java:318) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
        at org.wso2.carbon.apimgt.impl.APIAdminImpl.getKeyManagerConfigurationsByTenant(APIAdminImpl.java:315) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
        at org.wso2.carbon.apimgt.internal.service.impl.KeymanagersApiServiceImpl.keymanagersGet(KeymanagersApiServiceImpl.java:46) [classes/:?]
        at org.wso2.carbon.apimgt.internal.service.KeymanagersApi.keymanagersGet(KeymanagersApi.java:48) [classes/:?]
        at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
        at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
        at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
        at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
        at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:179) [cxf-core-3.3.7.jar:3.3.7]
        at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96) [cxf-core-3.3.7.jar:3.3.7]
        at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:201) [cxf-rt-frontend-jaxrs-3.3.7.jar:3.3.7]
        at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:104) [cxf-rt-frontend-jaxrs-3.3.7.jar:3.3.7]
        at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:59) [cxf-core-3.3.7.jar:3.3.7]
        at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:96) [cxf-core-3.3.7.jar:3.3.7]
        at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308) [cxf-core-3.3.7.jar:3.3.7]
        at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121) [cxf-core-3.3.7.jar:3.3.7]
        at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:267) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
        at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:234) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
        at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:208) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
        at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:160) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
        at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:225) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
        at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:296) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
        at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doGet(AbstractHTTPServlet.java:220) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:634) [tomcat-servlet-api_9.0.34.wso2v1.jar:?]
        at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:271) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) [tomcat_9.0.34.wso2v1.jar:?]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat_9.0.34.wso2v1.jar:?]
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) [tomcat_9.0.34.wso2v1.jar:?]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat_9.0.34.wso2v1.jar:?]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat_9.0.34.wso2v1.jar:?]
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) [tomcat_9.0.34.wso2v1.jar:?]
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat_9.0.34.wso2v1.jar:?]
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) [tomcat_9.0.34.wso2v1.jar:?]
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [tomcat_9.0.34.wso2v1.jar:?]
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat_9.0.34.wso2v1.jar:?]
        at org.wso2.carbon.identity.context.rewrite.valve.TenantContextRewriteValve.invoke(TenantContextRewriteValve.java:107) [org.wso2.carbon.identity.context.rewrite.valve_1.4.25.jar:?]
        at org.wso2.carbon.identity.authz.valve.AuthorizationValve.invoke(AuthorizationValve.java:110) [org.wso2.carbon.identity.authz.valve_1.4.25.jar:?]
        at org.wso2.carbon.identity.auth.valve.AuthenticationValve.invoke(AuthenticationValve.java:102) [org.wso2.carbon.identity.auth.valve_1.4.25.jar:?]
        at org.wso2.carbon.tomcat.ext.valves.CompositeValve.continueInvocation(CompositeValve.java:101) [org.wso2.carbon.tomcat.ext_4.6.2.jar:?]
        at org.wso2.carbon.tomcat.ext.valves.TomcatValveContainer.invokeValves(TomcatValveContainer.java:49) [org.wso2.carbon.tomcat.ext_4.6.2.jar:?]
        at org.wso2.carbon.tomcat.ext.valves.CompositeValve.invoke(CompositeValve.java:62) [org.wso2.carbon.tomcat.ext_4.6.2.jar:?]
        at org.wso2.carbon.tomcat.ext.valves.CarbonStuckThreadDetectionValve.invoke(CarbonStuckThreadDetectionValve.java:145) [org.wso2.carbon.tomcat.ext_4.6.2.jar:?]
        at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690) [tomcat_9.0.34.wso2v1.jar:?]
        at org.wso2.carbon.tomcat.ext.valves.CarbonContextCreatorValve.invoke(CarbonContextCreatorValve.java:57) [org.wso2.carbon.tomcat.ext_4.6.2.jar:?]
        at org.wso2.carbon.tomcat.ext.valves.RequestCorrelationIdValve.invoke(RequestCorrelationIdValve.java:126) [org.wso2.carbon.tomcat.ext_4.6.2.jar:?]
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat_9.0.34.wso2v1.jar:?]
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat_9.0.34.wso2v1.jar:?]
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373) [tomcat_9.0.34.wso2v1.jar:?]
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat_9.0.34.wso2v1.jar:?]
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) [tomcat_9.0.34.wso2v1.jar:?]
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590) [tomcat_9.0.34.wso2v1.jar:?]
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat_9.0.34.wso2v1.jar:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat_9.0.34.wso2v1.jar:?]
        at java.lang.Thread.run(Thread.java:834) [?:?]
Caused by: org.postgresql.util.PSQLException: ERROR: column "tenant_domain" does not exist
  Position: 50
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2712) ~[postgresql-42.7.1.jar:42.7.1]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2400) ~[postgresql-42.7.1.jar:42.7.1]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:367) ~[postgresql-42.7.1.jar:42.7.1]
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498) ~[postgresql-42.7.1.jar:42.7.1]
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415) ~[postgresql-42.7.1.jar:42.7.1]
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) ~[postgresql-42.7.1.jar:42.7.1]
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134) ~[postgresql-42.7.1.jar:42.7.1]
        at jdk.internal.reflect.GeneratedMethodAccessor62.invoke(Unknown Source) ~[?:?]
        at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
        at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[jdbc-pool_9.0.35.wso2v1.jar:?]
        at com.sun.proxy.$Proxy53.executeQuery(Unknown Source) ~[?:?]
        at org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.getKeyManagerConfigurationByName_aroundBody394(ApiMgtDAO.java:8733) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
        at org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.getKeyManagerConfigurationByName(ApiMgtDAO.java:8725) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
        at org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.getKeyManagerConfigurationByName_aroundBody392(ApiMgtDAO.java:8718) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
        ... 59 more

可能的原因 #

• 大小写敏感性问题：PostgreSQL默认对标识符（表名、列名）区分大小写，若创建表时列名用双引号包裹成"TENANT_DOMAIN"，而代码中用小写tenant_domain查询，就会找不到列。检查表结构中列名的实际大小写，确保查询语句和列名一致。
• 数据库连接配置错误：确认deployment.toml中配置的数据库连接是否指向了正确的PostgreSQL库，有可能程序还在连接旧的H2数据库或者其他未正确初始化的库。
• 脚本执行不全或版本不匹配：虽然执行了脚本，但可能遗漏了部分APIM关键组件的初始化脚本，或者使用了错误版本的脚本（比如用了其他APIM版本的脚本）。检查是否执行了apimgt相关的所有PostgreSQL脚本，特别是涉及密钥管理器表的部分。
• Schema（模式）问题：PostgreSQL中如果表存在于特定Schema下（比如public之外），而连接配置中未指定默认Schema，查询时需要显式指定Schema，否则会找不到列。检查数据库连接URL是否添加了currentSchema参数，或者表是否在预期的Schema中。
• 缓存或未重启生效：修改数据库配置后未完全重启WSO2服务，导致旧的H2连接缓存未清除。彻底停止服务，清理tmp和work目录后再重新启动。

内容的提问来源于stack exchange，提问作者Noman Khan