Logto Database Migration

Create database and role BEFORE db restoration

docker exec -i docker-logto-db-1 psql -U postgres <<EOF
-- Create the database
CREATE DATABASE logto;

-- Create the missing role
CREATE ROLE logto_tenant_logto WITH LOGIN PASSWORD 'your_secure_password';

-- Grant connection privilege
GRANT CONNECT ON DATABASE logto TO logto_tenant_logto;

-- Connect to logto database for schema permissions
\c logto

-- Grant schema permissions
GRANT USAGE ON SCHEMA public TO logto_tenant_logto;
GRANT CREATE ON SCHEMA public TO logto_tenant_logto;

-- Grant default privileges for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO logto_tenant_logto;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO logto_tenant_logto;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO logto_tenant_logto;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TYPES TO logto_tenant_logto;
EOF

Then restore the backup

gunzip < logto.sql.gz | docker exec -i docker-logto-db-1 psql -d logto -U postgres

This can help you avoid ERROR: role "logto_tenant_logto" does not exist errors