Search code examples
postgresqlgoogle-cloud-sqlliquibase

Liquibase ERROR: relation "databasechangeloglock" already exists

While running Liquibase migration, the docker container is returning the following error:

Unexpected error running Liquibase: ERROR: relation 
"databasechangeloglock" already exists [Failed SQL: 
(0) CREATE TABLE public.databasechangeloglock (ID 
INTEGER NOT NULL, LOCKED BOOLEAN NOT NULL, 
LOCKGRANTED TIMESTAMP WITHOUT TIME ZONE, LOCKEDBY 
VARCHAR(255), CONSTRAINT databasechangeloglock_pkey 
PRIMARY KEY (ID))]

The IAM account which the liquibase is using has the cloudsqlsuperuser role. Also, I queried for this table and it doesn't exist in the database.

Any thoughts on what this user could be missing? When I see the logs in gcp, I do see a permission error:

ERROR: permission denied for table databasechangeloglock

However, this user seems to have permissions on all tables & sequences.

Solution

  • (Copied comment with the answer)

    "It turned out to be a scenario where migration was initially run with a different user & when we added a new migration-specific user in Postgres, the new user wouldn't have permission on the databsechangeloglock table. I ended up creating a new database but the answer tagged by @CatherineO would have solved also solved it."

    REFERENCES: