Closed
Description
Hi!
I just updated my coder to v2.9.0, and during the startup, I got the following migration error:
Started HTTP listener at http://0.0.0.0:8080
Started TLS/HTTPS listener at https://0.0.0.0:8443
View the Web UI: https://coder.myhost
2024-03-16 12:02:26.842 [erro] connect to postgres failed ...
error= migrate up:
github.com/coder/coder/v2/cli.ConnectToPostgres
/home/runner/actions-runner/_work/coder/coder/cli/server.go:2121
- up:
github.com/coder/coder/v2/coderd/database/migrations.Up
/home/runner/actions-runner/_work/coder/coder/coderd/database/migrations/migrate.go:74
- 2 errors occurred:
* run statement: migration failed: relation "public.template_versions" does not exist in line 0: -- We cannot alter the column type while a view depends on it, so we drop it and recreate it.
DROP VIEW template_version_with_user;
-- Turns the list of provider names into JSONB with the type `Array<{ id: string; optional?: boolean }>`
-- eg. `'{github,gitlab}'::text[]` would become `'[{"id": "github"}, {"id": "gitlab"}]'::jsonb`
CREATE OR REPLACE FUNCTION migrate_external_auth_providers_to_jsonb(text[])
RETURNS jsonb
LANGUAGE plpgsql
AS $$
DECLARE
result jsonb;
BEGIN
SELECT
jsonb_agg(jsonb_build_object('id', value::text)) INTO result
FROM
unnest($1) AS value;
RETURN result;
END;
$$;
-- Update the column type and migrate the values
ALTER TABLE template_versions
ALTER COLUMN external_auth_providers TYPE jsonb
USING migrate_external_auth_providers_to_jsonb(external_auth_providers);
-- Make the column non-nullable to make the types nicer on the Go side
UPDATE template_versions
SET external_auth_providers = '[]'::jsonb
WHERE external_auth_providers IS NULL;
ALTER TABLE template_versions
ALTER COLUMN external_auth_providers SET DEFAULT '[]'::jsonb;
ALTER TABLE template_versions
ALTER COLUMN external_auth_providers SET NOT NULL;
-- Recreate `template_version_with_user` as described in dump.sql
CREATE VIEW template_version_with_user AS
SELECT
template_versions.id,
template_versions.template_id,
template_versions.organization_id,
template_versions.created_at,
template_versions.updated_at,
template_versions.name,
template_versions.readme,
template_versions.job_id,
template_versions.created_by,
template_versions.external_auth_providers,
template_versions.message,
template_versions.archived,
COALESCE(visible_users.avatar_url, ''::text) AS created_by_avatar_url,
COALESCE(visible_users.username, ''::text) AS created_by_username
FROM (public.template_versions
LEFT JOIN visible_users ON (template_versions.created_by = visible_users.id));
COMMENT ON VIEW template_version_with_user IS 'Joins in the username + avatar url of the created by user.';
-- Cleanup
DROP FUNCTION migrate_external_auth_providers_to_jsonb;
(details: pq: relation "public.template_versions" does not exist)
* commit tx on unlock: pq: Could not complete operation in a failed transaction
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x18 pc=0x1ef2fa8]
goroutine 1 [running]:
github.com/coder/coder/v2/cli.traceError({0x8eac220, 0x40008945a0})
/home/runner/actions-runner/_work/coder/coder/cli/root.go:1249 +0x68
github.com/coder/coder/v2/cli.cliHumanFormatError({0x400082a600, 0x1267}, {0x8eac220?, 0x40008945a0}, 0x7f727bca70?)
/home/runner/actions-runner/_work/coder/coder/cli/root.go:1131 +0xf4
github.com/coder/coder/v2/cli.cliHumanFormatError({0x40008aca80, 0x98c}, {0x8eac220?, 0x4000894558}, 0x21d02c0?)
/home/runner/actions-runner/_work/coder/coder/cli/root.go:1131 +0x13c
github.com/coder/coder/v2/cli.cliHumanFormatError({0x40009166e0, 0x48}, {0x8ea9de0?, 0x4000a1a340}, 0x4000b456d8?)
/home/runner/actions-runner/_work/coder/coder/cli/root.go:1131 +0x13c
github.com/coder/coder/v2/cli.cliHumanFormatError({0x40009165f0, 0x44}, {0x8ea9d40?, 0x40007180c0}, 0x4000b457b8?)
/home/runner/actions-runner/_work/coder/coder/cli/root.go:1131 +0x13c
github.com/coder/coder/v2/cli.cliHumanFormatError({0x40005ceb00, 0x38}, {0x8ea9d40?, 0x4000718100}, 0x4000b45898?)
/home/runner/actions-runner/_work/coder/coder/cli/root.go:1131 +0x13c
github.com/coder/coder/v2/cli.cliHumanFormatError({0x4000715500, 0x23}, {0x8ea9d40?, 0x4000718200}, 0x0?)
/home/runner/actions-runner/_work/coder/coder/cli/root.go:1131 +0x13c
github.com/coder/coder/v2/cli.cliHumanFormatError({0x0, 0x0}, {0x8ea9d40?, 0x4000718280}, 0x1?)
/home/runner/actions-runner/_work/coder/coder/cli/root.go:1131 +0x13c
github.com/coder/coder/v2/cli.formatRunCommandError(0x4000894378, 0x8ea9da0?)
/home/runner/actions-runner/_work/coder/coder/cli/root.go:1199 +0x1bc
github.com/coder/coder/v2/cli.cliHumanFormatError({0x0, 0x0}, {0x8ea9da0?, 0x4000894378}, 0x8ea9100?)
/home/runner/actions-runner/_work/coder/coder/cli/root.go:1126 +0xbc
github.com/coder/coder/v2/cli.cliHumanFormatError({0x0, 0x0}, {0x8ea91c0?, 0x4000894390}, 0x0?)
/home/runner/actions-runner/_work/coder/coder/cli/root.go:1112 +0x31c
github.com/coder/coder/v2/cli.(*prettyErrorFormatter).format(0x4000b45d78, {0x8ea91c0?, 0x4000894390?})
/home/runner/actions-runner/_work/coder/coder/cli/root.go:1073 +0x48
github.com/coder/coder/v2/cli.(*RootCmd).RunMain(0x400074dea0, {0x40007a3400, 0x2c, 0x50})
/home/runner/actions-runner/_work/coder/coder/cli/root.go:162 +0x25c
main.main()
/home/runner/actions-runner/_work/coder/coder/enterprise/cmd/coder/main.go:11 +0x40
I don't know the codebase, but the I think the migration uses the hardcoded public schema instead of the provided one (coder).
my database env: CODER_PG_CONNECTION_URL=postgresql://user:password@postgres.database.svc.cluster.local/coder