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

[stivens13]

About my setup:

[email protected] and [email protected]
(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?

[stivens13]

@mfridman, I think you're right, it was a search_path messed up by a [presumably botched] sql dump. There was a block of Postgres state setup [including setting search_path to ''] and after removing it, this, and a few other problems disappeared.

The issue can be closed but it might be worth it to mention this problem somewhere in docs since it was tricky to track. While all other tools and commands worked properly, goose was failing and it was easy to blame goose when instead it was a setup issue.

Also, kudos to your hard work! Goose is a magnificent tool and your activity is so vibrant!