ERROR: relation "goose_db_version" does not exist (SQLSTATE 42P01)

[stivens13]

About my setup:

goose@3.22.1 and goose@3.23.0 (go install) postgresql@16 and postgresql@17 (homebrew)

Postgres runs in docker

Running goose with the command below

goose postgres "postgres connect string" -dir database/migrations -table goose_migrations up

Produces

goose run: ERROR 20241119152933_init.sql: failed to run SQL migration: failed to insert new goose version: ERROR: relation "goose_db_version" does not exist (SQLSTATE 42P01)

Goose is able to successfully:

• connect to the database
• access migrations dir
• create migrations table (default goose_db_version and custom tables with -table flag)
• apply migrations (they're visible with -v including rollback)

However, for some reason, goose cannot access the migrations table, that it just created itself, after migrations are applied. Seems like it cannot pick up schema (which is public). Work is done on default db postgres

I didn't find any cli arguments to pass schema or db.

Is there a safe version that doesn't have this bug?

[stivens13]

After intensive debugging, came up with a hack - use schema in -table argument

So the command looks like this

goose postgres "postgres connect string" -dir database/migrations -table public.goose_migrations up

This problem took quite a bit of time. Would be great to add this to documentation

[mfridman]

I'd love to get to the bottom of this with a reproducible example. Below is a starting point. Could you tell me what might be different about your env, setup, migrations, etc?

From the root of this repository.

# spin up a docker container
docker run --rm -d -e POSTGRES_USER=dbuser -e POSTGRES_PASSWORD=password1 -p 5433:5432 postgres:16-alpine

#export a few env variables
export GOOSE_DBSTRING="postgresql://dbuser:password1@localhost:5433/postgres?sslmode=disable"
export GOOSE_DRIVER=postgres
export GOOSE_MIGRATION_DIR=./testdata/migrations

# confirm version
goose -version

goose version: v3.23.0

# run all up migrations
goose up

2024/11/22 09:30:58 OK   00001_users_table.sql (3.28ms)
2024/11/22 09:30:58 OK   00002_posts_table.sql (4.28ms)
2024/11/22 09:30:58 OK   00003_comments_table.sql (4.05ms)
2024/11/22 09:30:58 OK   00004_insert_data.sql (2.32ms)
2024/11/22 09:30:58 OK   00005_posts_view.sql (1.33ms)
2024/11/22 09:30:58 goose: successfully migrated database to version: 5

# cleanup
docker stop -t=0 $(docker ps --filter="label=goose_test" -aq)

No matter how many times I do this, I don't hit this error so there must be something, but I can't spot it?

#!/bin/bash

count=0
runs=5

for ((i = 1; i <= runs; i++)); do
    docker run -l goose_test --rm -d -e POSTGRES_USER=dbuser -e POSTGRES_PASSWORD=password1 -p 5433:5432 postgres:16-alpine

    sleep 3

    export GOOSE_DBSTRING="postgresql://dbuser:password1@localhost:5433/postgres?sslmode=disable"
    export GOOSE_DRIVER=postgres
    export GOOSE_MIGRATION_DIR=./testdata/migrations

    if goose up; then
        ((count++))
    fi

    docker stop -t=0 $(docker ps --filter="label=goose_test" -aq)
    sleep 2
done

echo "Goose up succeeded $count out of $runs times"

[mfridman]

Are you modifying the search_path in the connection string?

Is it possible the user creating the migration table might have different schema access than the user trying to read it?