Is PostgreSQL fully supported in DevLake?

[michelelcham]

Hello,

i am facing lots of validation issue when trying to install devlake with postgres external option,

please can someone help with that

[klesh]

Will you post the error so others can help?

[solivansantana]

I'm also having problems when trying to use a Postgresql database. First, the helpers are aimed at using MySql and not at the Postgresql option. It is necessary to manipulate settings such as database.url (connection syntax only for mysql). There are no statefulsets when you have the Postgresql option, only for Mysql. It is necessary to configure a statefulset template for Postgresql if you do not want to use the external option. When trying to use Devlake through the UI, it is necessary to perform the script migration process. If migration is requested, several errors such as "error executing migration (400) Wraps: (2) There is a migration in progress. (400) Error types: (1) *hintdetail.withDetail (2) *errors.errorString" are displayed. The migration process runs infinitely in the Lake and the database complains about problems in tables. I believe there may be a pre-set script and it is not yet compatible with PostgreSQL. My solution was to leave PostgreSql and go back to MySql.

Database logs:

PostgreSQL init process complete; ready for start up.
2025-02-14 09:50:05.799 UTC [1] LOG: starting PostgreSQL 14.5 (Debian 14.5-2.pgdg110+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2025-02-14 09:50:05.800 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
2025-02-14 09:50:05.800 UTC [1] LOG: listening on IPv6 address "::", port 5432
2025-02-14 09:50:05.810 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2025-02-14 09:50:05.887 UTC [63] LOG: database system was shut down at 2025-02-14 09:50:05 UTC
2025-02-14 09:50:05.897 UTC [1] LOG: database system is ready to accept connections
2025-02-14 09:50:46.996 UTC [72] ERROR: cached plan must not change result type
2025-02-14 09:50:46.996 UTC [72] STATEMENT: SELECT * FROM "_tool_gitlab_pipelines" ORDER BY "_tool_gitlab_pipelines"."connection_id" LIMIT 1
2025-02-14 09:50:48.400 UTC [72] ERROR: cached plan must not change result type
2025-02-14 09:50:48.400 UTC [72] STATEMENT: SELECT * FROM "_tool_gitlab_jobs" ORDER BY "_tool_gitlab_jobs"."connection_id" LIMIT 1
2025-02-14 09:50:50.993 UTC [72] ERROR: cached plan must not change result type
2025-02-14 09:50:50.993 UTC [72] STATEMENT: SELECT * FROM "_devlake_blueprints" ORDER BY "_devlake_blueprints"."id" LIMIT 1
2025-02-14 09:50:51.391 UTC [72] ERROR: cached plan must not change result type
2025-02-14 09:50:51.391 UTC [72] STATEMENT: SELECT * FROM "_devlake_pipelines" ORDER BY "_devlake_pipelines"."id" LIMIT 1
2025-02-14 09:50:52.891 UTC [72] ERROR: cached plan must not change result type
2025-02-14 09:50:52.891 UTC [72] STATEMENT: SELECT * FROM "commit_files" ORDER BY "commit_files"."id" LIMIT 1
2025-02-14 09:50:55.011 UTC [72] ERROR: cached plan must not change result type
2025-02-14 09:50:55.011 UTC [72] STATEMENT: SELECT * FROM "_tool_jenkins_builds" ORDER BY "_tool_jenkins_builds"."created_at" LIMIT 1
2025-02-14 09:51:00.504 UTC [72] ERROR: cached plan must not change result type
2025-02-14 09:51:00.504 UTC [72] STATEMENT: SELECT * FROM "_tool_jenkins_jobs" ORDER BY "_tool_jenkins_jobs"."connection_id" LIMIT 1
2025-02-14 09:51:09.293 UTC [72] ERROR: cached plan must not change result type
2025-02-14 09:51:09.293 UTC [72] STATEMENT: SELECT * FROM "_tool_github_repos" ORDER BY "_tool_github_repos"."connection_id" LIMIT 1
2025-02-14 09:51:14.087 UTC [72] ERROR: cached plan must not change result type
2025-02-14 09:51:14.087 UTC [72] STATEMENT: SELECT * FROM "_devlake_tasks" ORDER BY "_devlake_tasks"."id" LIMIT 1
2025-02-14 09:51:20.808 UTC [72] ERROR: cached plan must not change result type
2025-02-14 09:51:20.808 UTC [72] STATEMENT: SELECT * FROM "_tool_bitbucket_issues" ORDER BY "_tool_bitbucket_issues"."connection_id" LIMIT 1
2025-02-14 09:51:21.751 UTC [71] LOG: unexpected EOF on client connection with an open transaction
2025-02-14 09:56:04.635 UTC [75] ERROR: column "owner_id" does not exist
2025-02-14 09:56:04.635 UTC [75] STATEMENT: ALTER TABLE "_tool_bitbucket_repos" RENAME COLUMN "owner_id" TO "owner"
2025-02-14 09:58:57.923 UTC [96] ERROR: relation "_devlake_migrations" does not exist at character 15
2025-02-14 09:58:57.923 UTC [96] STATEMENT: SELECT * FROM _devlake_migrations WHERE is_applied = false;
2025-02-14 09:59:12.998 UTC [96] ERROR: relation "_devlake_migrations" does not exist at character 13
2025-02-14 09:59:12.998 UTC [96] STATEMENT: DELETE FROM _devlake_migrations WHERE is_applied = false;
2025-02-14 10:01:18.078 UTC [96] ERROR: relation "_devlake_migrations" does not exist at character 15
2025-02-14 10:01:18.078 UTC [96] STATEMENT: SELECT * FROM _devlake_migrations WHERE is_applied = false;

Lake logs:

time="2025-02-14 14:28:16" level=error msg="HTTP 428 error\n\tcaused by: Database migration is in progress. Please wait until it is completed. (428)"
[GIN] 2025/02/14 - 14:28:16 | 428 | 129.503µs | 10.129.40.165 | GET "/store/onboard"
[GIN] 2025/02/14 - 14:28:21 | 200 | 3.2µs | 10.129.38.106 | GET "/ping"
[GIN] 2025/02/14 - 14:28:21 | 200 | 1µs | 10.129.38.106 | GET "/ping"
[GIN] 2025/02/14 - 14:28:26 | 200 | 2.7µs | 10.129.38.106 | GET "/ping"
[GIN] 2025/02/14 - 14:28:26 | 200 | 1.5µs | 10.129.38.106 | GET "/ping"
[GIN] 2025/02/14 - 14:28:31 | 200 | 2.6µs | 10.129.38.106 | GET "/ping"
[GIN] 2025/02/14 - 14:28:31 | 200 | 800ns | 10.129.38.106 | GET "/ping"
[GIN] 2025/02/14 - 14:28:36 | 200 | 2µs | 10.129.38.106 | GET "/ping"
[GIN] 2025/02/14 - 14:28:36 | 200 | 3µs | 10.129.38.106 | GET "/ping"
time="2025-02-14 14:28:41" level=error msg="HTTP 400 error\n\tcaused by: error executing migration (400)\n\tWraps: (2) There is a migration in progress. (400)\n\tError types: (1) *hintdetail.withDetail (2) *errors.errorString"
[GIN] 2025/02/14 - 14:28:41 | 400 | 185.705µs | 10.129.11.122 | GET "/proceed-db-migration"