ERROR: can only create extension in database postgres (pg_cron)

What you see

You run a migration or a line in the SQL editor that enables pg_cron, and Postgres refuses:

ERROR:  can only create extension in database postgres

pg_cron may add a DETAIL or HINT line that mentions cron.database_name and postgresql.conf. You'll usually hit this when replaying migrations written for Supabase Cloud or for a local supabase start stack, because those often begin with something like:

create extension if not exists pg_cron with schema pg_catalog;

Why it happens

pg_cron runs one background worker per Postgres server, and that worker reads its job list from a single database. The extension can only be installed in that database.

On SupaHost, one Postgres server holds every project on your instance. Each project is its own database (named p_<project>), and the real pg_cron lives in the server's postgres database. So the create extension call is running in the wrong database, and pg_cron rejects it. if not exists doesn't help: the extension isn't installed in your project's database, so Postgres still tries to create it.

You don't need it anyway. Every project database already has a cron schema that forwards to the real pg_cron. These all work as-is:

• cron.schedule(name, schedule, sql) and cron.schedule(schedule, sql)
• cron.unschedule(name) or cron.unschedule(id)
• the views cron.job and cron.job_run_details

How to fix it

1. Remove the create extension ... pg_cron line from the migration, or skip it outside the postgres database. This version works on a local Supabase stack (where the database is called postgres) and is skipped on SupaHost:

   do $$
   begin
     if current_database() = 'postgres' then
       create extension if not exists pg_cron;
     end if;
   end
   $$;
   

2. Leave grant usage on schema cron to postgres; in place if your migration has it. It's harmless.

3. Don't follow the hint about cron.database_name. You can't edit postgresql.conf on SupaHost, and changing it would break scheduling for your other projects.

4. Schedule your jobs as normal:

   select cron.schedule(
     'expire-unpaid-invoices',
     '0 * * * *',
     $$ update public.invoices set status = 'expired'
        where status = 'unpaid' and due_at < now() - interval '30 days' $$
   );
   

5. Check that the job exists:

   select jobid, jobname, schedule, active from cron.job;
   

On SupaHost

• If you apply migrations through the SupaHost MCP server (execute_sql or apply_migration), it strips create extension pg_cron for you and explains why, so the rest of the migration still runs.
• Only the postgres role can use the cron schema. That's the role behind Studio's SQL editor, the MCP server's write mode and the SSH tunnel.
• Job names must be 1 to 49 characters of lowercase letters, numbers, - or _.
• Jobs also appear in the dashboard: Dashboard → your project → Cron tab.
• cron.alter_job isn't available. To change a job, call cron.schedule again with the same name, or use Edit on the Cron tab.

Related

• Scheduled jobs with pg_cron
• Cron job not running
• SQL migrations
• Postgres extensions