cli: Getting error "relation "pgsodium.key_key_id_seq" does not exist (SQLSTATE 42P01)" when using "db remote commit"

Bug report

Describe the bug

When using the latest CLI to init, start, link, and remote commit; supabase errors with a missing relation.

relation "pgsodium.key_key_id_seq" does not exist (SQLSTATE 42P01)

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

supabase init
supabase start
supabase link --project ...
supabase db remote commit
supabase db reset

PS C:\Users\silen\Documents\GitHub\buildr-monorepo> supabase db reset        
Resetting database...
Initialising schema...
Applying migration 20220824061131_remote_commit.sql...
Error: ERROR: relation "pgsodium.key_key_id_seq" does not exist (SQLSTATE 42P01)

Expected behavior

The database should now reflect the migration.

System information

OS: Windows 11
Version of supabase-cli: 1.1.5
Version of Node.js: 16.13.2

About this issue

Original URL
State: closed
Created 2 years ago
Reactions: 16
Comments: 24 (6 by maintainers)

Most upvoted comments

I’m hitting the same issue as @fbinz on a fresh project after supabase db remote commit. The hosted database seems to use pgsodium version 3.1.5 whereas the CLI (version 1.29.1) sets up version 3.0.4 (according to pg_catalog.pg_extension).

jonathanperret on Jan 6, 2023

Fixed it;

https://github.com/supabase/cli/issues/246

supabase link --project-ref <project-id>
supabase db remote commit
supabase db reset
Cool, now we can see that we’re screwed and we get extension errors like pgsodium.key_key_id_seq does not exist
Great, go into the 20220913230812_remote_commit.sql file - comment everything out, save without formatting
Create a new .sql file over at supabase/extensions.sql and put in whichever extension is failing you CREATE EXTENSION pgsodium SCHEMA extensions; in this case
Great, supabase db reset and it should work
However, we’re still screwed because when we supabase db push to the remote, you’ll see the timestamp is different
Go into your production database (or the remote as I called it), and go to supabase_migrations.schema_migrations - copy the one row in there (if you have multiple, it should be the first)
Paste that over your original timestamp in your migrations folder
supabase db push

And you’re good to go. Verified that this works even when I add new tables and supabase db reset.

Hope this saves someone the absolutely agonizing experience of googling relation "pgsodium.key_key_id_seq" does not exist (SQLSTATE 42P01)

ARMATAV on Sep 13, 2022

No progress. I still don’t understand why this happens. It’s very annoying and I’m completely unable to sync my local to my remote.

trulysinclair on Sep 9, 2022

Had this error locally when undergoing a major postgres upgrade (14 -> 15) and trying to run supabase reset.

Stopping and restarting supabase helped to force supabase cli to upgrade the used docker images

wyozi on Apr 11, 2023