search

kestra-io / kestra

:zap: Workflow Automation Platform. Orchestrate & Schedule code in any language, run anywhere, 500+ plugins. Alternative to Zapier, Rundeck, Camunda, Airflow...
https://kestra.io
Apache License 2.0
12.85k stars 1.11k forks source link

can't start with postgres only #1242

Closed ghost closed 1 year ago

ghost commented 1 year ago

Expected Behavior

Postgres server is already setup and I checked both username and password is good. curl my-pg-server:5432 shows ok with tcp connection.

Here is my helm configuration.

configuration:
  kestra:
    datasources:
      postgres:
        url: jdbc:postgresql://my-pg-server:5432/kestra
        driverClassName: org.postgresql.Driver
        username: root
        password: xxxxxxxxxx
        dialect: POSTGRES
    tasks:
      scripts:
        docker:
          volume-enabled: true
    anonymous-usage-report:
      enable: false
    storage:
      type: minio
      minio:
        endpoint: http://xxxxxxxxxxxxxxxx.example.com
        accessKey: "xxxxxxxxxxxxxx"
        secretKey: "xxxxxxxxxxxxx"
        region: "xxxxxxxxxxxx"
        bucket: "xxxxxxxxxxxkestra"
    queue:
      type: postgres
    repository:
      type: postgres

minio:
  enabled: false
elasticsearch:
  enabled: false
postgresql:
  enabled: false
kafka:
  enabled: false

Actual Behaviour

Got this error

2023-05-06 20:44:28,898 INFO  executor     io.kestra.cli.AbstractCommand Starting Kestra with environments [k8s, cloud, cli]
2023-05-06 20:44:29,506 INFO  executor     io.kestra.cli.AbstractCommand Server Running: http://kestra-executor-86d4f5b76f-7bvtd:8080, Management server on port http://kestra-executor-86d4f5b76f-7bvtd:8081/health
io.micronaut.context.exceptions.DependencyInjectionException: Failed to inject value for parameter [dslContext] of class: io.kestra.jdbc.JooqDSLContextWrapper
Message: No bean of type [org.jooq.DSLContext] exists. Make sure the bean is not disabled by bean requirements (enable trace logging for 'io.micronaut.context.condition' to check) and if the bean is enabled then ensure the class is declared a bean and annotation processing is enabled (for Java and Kotlin the 'micronaut-inject-java' dependency should be configured as an annotation processor).
Path Taken: new JooqDSLContextWrapper(DSLContext dslContext,RetryUtils retryUtils) --> new JooqDSLContextWrapper([DSLContext dslContext],RetryUtils retryUtils)
        at io.micronaut.context.AbstractInitializableBeanDefinition.resolveBean(AbstractInitializableBeanDefinition.java:2087)
        at io.micronaut.context.AbstractInitializableBeanDefinition.getBeanForConstructorArgument(AbstractInitializableBeanDefinition.java:1297)
        at io.kestra.jdbc.$JooqDSLContextWrapper$Definition.build(Unknown Source)
        at io.micronaut.context.DefaultBeanContext.resolveByBeanFactory(DefaultBeanContext.java:2354)
        at io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:2305)
        at io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:2251)
        at io.micronaut.context.DefaultBeanContext.createRegistration(DefaultBeanContext.java:3016)
        at io.micronaut.context.SingletonScope.getOrCreate(SingletonScope.java:80)
        at io.micronaut.context.DefaultBeanContext.findOrCreateSingletonBeanRegistration(DefaultBeanContext.java:2918)
        at io.micronaut.context.DefaultBeanContext.resolveBeanRegistration(DefaultBeanContext.java:2879)
        at io.micronaut.context.DefaultBeanContext.resolveBeanRegistration(DefaultBeanContext.java:2800)
        at io.micronaut.context.DefaultBeanContext.getBean(DefaultBeanContext.java:1617)
        at io.micronaut.context.DefaultBeanContext.getBean(DefaultBeanContext.java:867)
        at io.micronaut.context.DefaultBeanContext.getBean(DefaultBeanContext.java:859)
        at io.kestra.jdbc.AbstractJdbcRepository.<init>(AbstractJdbcRepository.java:47)
        at io.kestra.repository.postgres.PostgresRepository.<init>(PostgresRepository.java:19)
        at io.kestra.repository.postgres.PostgresFlowRepository.<init>(PostgresFlowRepository.java:17)
        at io.kestra.repository.postgres.$PostgresFlowRepository$Definition.build(Unknown Source)
        at io.micronaut.context.DefaultBeanContext.resolveByBeanFactory(DefaultBeanContext.java:2354)
        at io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:2305)
        at io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:2251)
        at io.micronaut.context.DefaultBeanContext.createRegistration(DefaultBeanContext.java:3016)
        at io.micronaut.context.SingletonScope.getOrCreate(SingletonScope.java:80)
        at io.micronaut.context.DefaultBeanContext.findOrCreateSingletonBeanRegistration(DefaultBeanContext.java:2918)
        at io.micronaut.context.DefaultBeanContext.resolveBeanRegistration(DefaultBeanContext.java:2879)
        at io.micronaut.context.DefaultBeanContext.resolveBeanRegistration(DefaultBeanContext.java:2800)
        at io.micronaut.context.DefaultBeanContext.getBean(DefaultBeanContext.java:1617)
        at io.micronaut.context.AbstractBeanResolutionContext.getBean(AbstractBeanResolutionContext.java:66)
        at io.micronaut.context.AbstractInitializableBeanDefinition.resolveBean(AbstractInitializableBeanDefinition.java:2065)
        at io.micronaut.context.AbstractInitializableBeanDefinition.getBeanForField(AbstractInitializableBeanDefinition.java:1603)
        at io.kestra.jdbc.runner.$JdbcExecutor$Definition.injectBean(Unknown Source)
        at io.kestra.jdbc.runner.$JdbcExecutor$Definition.build(Unknown Source)
        at io.micronaut.context.DefaultBeanContext.resolveByBeanFactory(DefaultBeanContext.java:2354)
        at io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:2305)
        at io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:2251)
        at io.micronaut.context.DefaultBeanContext.createRegistration(DefaultBeanContext.java:3016)
        at io.micronaut.context.SingletonScope.getOrCreate(SingletonScope.java:80)
        at io.micronaut.context.DefaultBeanContext.findOrCreateSingletonBeanRegistration(DefaultBeanContext.java:2918)
        at io.micronaut.context.DefaultBeanContext.resolveBeanRegistration(DefaultBeanContext.java:2879)
        at io.micronaut.context.DefaultBeanContext.resolveBeanRegistration(DefaultBeanContext.java:2800)
        at io.micronaut.context.DefaultBeanContext.getBean(DefaultBeanContext.java:1617)
        at io.micronaut.context.DefaultBeanContext.getBean(DefaultBeanContext.java:867)
        at io.micronaut.context.DefaultBeanContext.getBean(DefaultBeanContext.java:859)
        at io.kestra.cli.commands.servers.ExecutorCommand.call(ExecutorCommand.java:34)
        at io.kestra.cli.commands.servers.ExecutorCommand.call(ExecutorCommand.java:14)
        at picocli.CommandLine.executeUserObject(CommandLine.java:1953)
        at picocli.CommandLine.access$1300(CommandLine.java:145)
        at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2358)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2352)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2314)
        at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2179)
        at picocli.CommandLine$RunLast.execute(CommandLine.java:2316)
        at picocli.CommandLine.execute(CommandLine.java:2078)
        at io.kestra.cli.App.execute(App.java:69)
        at io.kestra.cli.App.main(App.java:49)
Caused by: io.micronaut.context.exceptions.NoSuchBeanException: No bean of type [org.jooq.DSLContext] exists. Make sure the bean is not disabled by bean requirements (enable trace logging for 'io.micronaut.context.condition' to check) and if the bean is enabled then ensure the class is declared a bean and annotation processing is enabled (for Java and Kotlin the 'micronaut-inject-java' dependency should be configured as an annotation processor).
        at io.micronaut.context.DefaultBeanContext.resolveBeanRegistration(DefaultBeanContext.java:2805)
        at io.micronaut.context.DefaultBeanContext.getBean(DefaultBeanContext.java:1617)
        at io.micronaut.context.AbstractBeanResolutionContext.getBean(AbstractBeanResolutionContext.java:66)
        at io.micronaut.context.AbstractInitializableBeanDefinition.resolveBean(AbstractInitializableBeanDefinition.java:2065)
        ... 54 more

Steps To Reproduce

No response

Environment Information

Example flow

No response

tchiotludo commented 1 year ago

Hey 👋 You have an indent issues with datasources that should be a top lebel configuration 

configuration:
  datasources:
    postgres:
      url: jdbc:postgresql://my-pg-server:5432/kestra
      driverClassName: org.postgresql.Driver
      username: root
      password: xxxxxxxxxx
      dialect: POSTGRES
  kestra:
    tasks:
...
ghost commented 1 year ago

Thanks! It is working but there is more problem with flyway?

  flyway:
    datasources:
      postgres:
        baseline-on-migrate: true
2023-05-06 21:16:07,804 ERROR main         o.f.core.internal.command.DbMigrate Migration of schema "public" to version "3 - queue index" failed! Changes successfully rolled back.
Exception in thread "main" io.micronaut.context.exceptions.BeanInstantiationException: Bean definition [javax.sql.DataSource] could not be loaded: Migration V3__queue_index.sql failed
------------------------------------
SQL State  : 42P01
Error Code : 0
Message    : ERROR: relation "queues" does not exist
Location   : migrations/postgres/V3__queue_index.sql (/app/file:/app/kestra!/migrations/postgres/V3__queue_index.sql)
Line       : 1
Statement  : ALTER TABLE queues
    ADD consumer_indexer BOOLEAN DEFAULT FALSE
        at io.micronaut.context.DefaultBeanContext.initializeContext(DefaultBeanContext.java:1921)
        at io.micronaut.context.DefaultApplicationContext.initializeContext(DefaultApplicationContext.java:249)
        at io.micronaut.context.DefaultBeanContext.readAllBeanDefinitionClasses(DefaultBeanContext.java:3326)
        at io.micronaut.context.DefaultBeanContext.finalizeConfiguration(DefaultBeanContext.java:3684)
        at io.micronaut.context.DefaultBeanContext.start(DefaultBeanContext.java:341)
        at io.micronaut.context.DefaultApplicationContext.start(DefaultApplicationContext.java:194)
        at io.micronaut.configuration.picocli.MicronautFactory.<init>(MicronautFactory.java:59)
        at io.kestra.cli.App.execute(App.java:69)
        at io.kestra.cli.App.main(App.java:49)
Caused by: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: Migration V3__queue_index.sql failed
------------------------------------
SQL State  : 42P01
Error Code : 0
Message    : ERROR: relation "queues" does not exist
Location   : migrations/postgres/V3__queue_index.sql (/app/file:/app/kestra!/migrations/postgres/V3__queue_index.sql)
Line       : 1
Statement  : ALTER TABLE queues
    ADD consumer_indexer BOOLEAN DEFAULT FALSE
        at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:384)
        at org.flywaydb.core.internal.command.DbMigrate.lambda$applyMigrations$1(DbMigrate.java:274)
        at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:55)
        at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:273)
        at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:246)
        at org.flywaydb.core.internal.command.DbMigrate.lambda$migrateAll$0(DbMigrate.java:141)
        at org.flywaydb.core.internal.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:73)
        at org.flywaydb.core.internal.database.postgresql.PostgreSQLAdvisoryLockTemplate.lambda$execute$0(PostgreSQLAdvisoryLockTemplate.java:56)
        at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:55)
        at org.flywaydb.core.internal.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:56)
        at org.flywaydb.core.internal.database.postgresql.PostgreSQLConnection.lock(PostgreSQLConnection.java:96)
        at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:143)
        at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:141)
        at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:98)
        at org.flywaydb.core.Flyway.lambda$migrate$0(Flyway.java:177)
        at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:196)
        at org.flywaydb.core.Flyway.migrate(Flyway.java:129)
        at io.micronaut.flyway.AbstractFlywayMigration.runFlyway(AbstractFlywayMigration.java:97)
        at io.micronaut.flyway.AbstractFlywayMigration.forceRun(AbstractFlywayMigration.java:86)
        at io.micronaut.flyway.AbstractFlywayMigration.run(AbstractFlywayMigration.java:66)
        at io.micronaut.flyway.DataSourceMigrationRunner.lambda$onCreated$1(DataSourceMigrationRunner.java:65)
        at java.base/java.util.Optional.ifPresent(Unknown Source)
        at io.micronaut.flyway.DataSourceMigrationRunner.onCreated(DataSourceMigrationRunner.java:63)
        at io.micronaut.flyway.DataSourceMigrationRunner.onCreated(DataSourceMigrationRunner.java:39)
        at io.micronaut.context.DefaultBeanContext.triggerBeanCreatedEventListener(DefaultBeanContext.java:2407)
        at io.micronaut.context.DefaultBeanContext.postBeanCreated(DefaultBeanContext.java:2385)
        at io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:2309)
        at io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:2251)
        at io.micronaut.context.DefaultBeanContext.createRegistration(DefaultBeanContext.java:3016)
        at io.micronaut.context.SingletonScope.getOrCreate(SingletonScope.java:80)
        at io.micronaut.context.DefaultBeanContext.findOrCreateSingletonBeanRegistration(DefaultBeanContext.java:2918)
        at io.micronaut.context.DefaultBeanContext.loadContextScopeBean(DefaultBeanContext.java:2737)
        at io.micronaut.context.DefaultBeanContext.initializeContext(DefaultBeanContext.java:1915)
        ... 8 more
Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: Migration V3__queue_index.sql failed
------------------------------------
SQL State  : 42P01
Error Code : 0
Message    : ERROR: relation "queues" does not exist
Location   : migrations/postgres/V3__queue_index.sql (/app/file:/app/kestra!/migrations/postgres/V3__queue_index.sql)
Line       : 1
Statement  : ALTER TABLE queues
    ADD consumer_indexer BOOLEAN DEFAULT FALSE
        at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:275)
        at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:222)
        at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:126)
        at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.executeOnce(SqlMigrationExecutor.java:68)
        at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.lambda$execute$0(SqlMigrationExecutor.java:57)
        at org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:27)
        at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:56)
        at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:376)
        ... 40 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "queues" does not exist
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:295)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:290)
        at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
        at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
        at org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:201)
        at org.flywaydb.core.internal.sqlscript.ParsedSqlStatement.execute(ParsedSqlStatement.java:95)
        at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:210)
        ... 46 more
tchiotludo commented 1 year ago

It's a fresh install? Seems that there is some corruption on the migration path. Maybe you try with a fresh database?

ghost commented 1 year ago

It's a pg database that created by a cloud provider, what should I do?

Database looks like this after kestra installed.

kestra
  | - public
      | - flyway-schema-history
      | - settings
tchiotludo commented 1 year ago

seems that you don't have a lot of table, my advice, drop all tables and restart, it will be ok

ghost commented 1 year ago

I tried to execute this SQL manually but it failed with "syntax error at or near RETURN"

CREATE OR REPLACE FUNCTION FULLTEXT_REPLACE(text, text) RETURNS text
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
    RETURN TRIM(BOTH $2 FROM REGEXP_REPLACE(COALESCE($1, ''), '[^a-zA-Z\d:]', $2, 'g'));
tchiotludo commented 1 year ago

The best IMO is to drop all the tables and to restart Kestra I think

loicmathieu commented 1 year ago

Hi, Which version of Postgres do you use ?

ghost commented 1 year ago

@loicmathieu I figured out what is wrong. This cloud provider will add a default schema "public" which will affect flyway migrate. Nothing wrong with Kestra, issue close

loicmathieu commented 1 year ago

@maxmeng-oss thanks for the feedback. Great you sort it out!