执行supabase start命令报错：relation "realtime.schema_migrations"不存在

问题背景 #

使用--column-inserts参数导出Supabase生产数据库后，执行启动命令：

SUPABASE_SCANNER_BUFFER_SIZE=200MB supabase start

触发如下错误：

ERROR: relation "realtime.schema_migrations" does not exist (SQLSTATE 42P01)

相关日志片段：

2023/03/20 01:17:35 PG Recv: {"Type":"ReadyForQuery","TxStatus":"I"}
2023/03/20 01:17:35 PG Send: {"Type":"Parse","Name":"","Query":"INSERT INTO realtime.schema_migrations (version, inserted_at) VALUES (20230228184745, '2023-03-04  18:48:30')","ParameterOIDs":null}
2023/03/20 01:17:35 PG Send: {"Type":"Describe","ObjectType":"S","Name":""}
2023/03/20 01:17:35 PG Send: {"Type":"Sync"}
2023/03/20 01:17:35 PG Recv:   {"Type":"ErrorResponse","Severity":"ERROR","SeverityUnlocalized":"ERROR","Code":"42P01","Message":"relation \"realtime.schema_migrations\" does not exist","Detail":"","Hint":"","Position":13,"InternalPosition":0,"InternalQuery":"","Where":"","SchemaName":"","TableName":"","ColumnName":"","DataTypeName":"","ConstraintName":"","File":"parse_relation.c","Line":1371,"Routine":"parserOpenTable","UnknownFields":null}
2023/03/20 01:17:35 PG Recv: {"Type":"ReadyForQuery","TxStatus":"I"}
2023/03/20 01:17:35 PG Send: {"Type":"Terminate"}
Error: ERROR: relation "realtime.schema_migrations" does not exist (SQLSTATE 42P01)

已在seed.sql中定义该表：

CREATE TABLE realtime.schema_migrations (
 version bigint NOT NULL,
 inserted_at timestamp(0) without time zone
);

解决步骤 #

1. 确认realtime schema存在 #

进入本地Supabase数据库：

supabase db psql

执行查询检查schema：

SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'realtime';

如果无返回结果，手动创建schema：

CREATE SCHEMA IF NOT EXISTS realtime;

退出psql：\q

2. 修正seed.sql的执行顺序 #

在seed.sql的表创建语句前添加schema创建逻辑，确保依赖存在：

CREATE SCHEMA IF NOT EXISTS realtime;

CREATE TABLE realtime.schema_migrations (
 version bigint NOT NULL,
 inserted_at timestamp(0) without time zone
);

3. 重置本地数据库并重新初始化 #

执行重置命令，让系统重新应用seed.sql和导出的数据库结构：

supabase db reset

重置完成后再启动服务：

SUPABASE_SCANNER_BUFFER_SIZE=200MB supabase start

4. 排查导出的SQL文件 #

打开导出的SQL文件，搜索是否存在删除或修改realtime schema的语句（如DROP SCHEMA realtime），如果有，注释或删除该类语句后重新导入。

5. 简化启动命令测试 #

先去掉缓冲参数尝试启动，排除参数影响：

supabase start

如果启动成功，再重新添加缓冲参数执行。

内容的提问来源于stack exchange，提问作者Richi