Unable to stop pg_cron scheduler

[serpent7776]

I'm unable to stop pg_cron scheduler, even after I drop the extension. I also tried to restart the cluster and whole postgres service, but the scheduler is still running.

This is an issue, because it maintains connection to the database. In the case I install the extension to database other than postgres, it effectively prevents me from dropping/renaming the database.

It happened both with background workers and libpq connection.

I'd expect that dropping the extension should stop the scheduler.

I'm running postgresql-16-cron 1.6.4-1.pgdg22.04+1

postgres=# create extension pg_cron;

postgres=# SELECT pid,datname,application_name FROM pg_stat_activity WHERE datname = 'postgres';
  pid  | datname  | application_name  
-------+----------+-------------------
 23807 | postgres | pg_cron scheduler
 26168 | postgres | psql

postgres=# drop extension pg_cron;

postgres=# SELECT pid,datname,application_name FROM pg_stat_activity WHERE datname = 'postgres';
  pid  | datname  | application_name  
-------+----------+-------------------
 23807 | postgres | pg_cron scheduler
 26168 | postgres | psql

[TheOtherBrian1]

Can you try killing the process?

Get process id

SELECT
  pid as process_id,
  usename as database_user,
  application_name,
  backend_start as when_process_began,
  wait_event_type,
  state,
  query,
  backend_type
FROM pg_stat_activity where application_name ilike 'pg_cron scheduler';

kill process

SELECT pg_terminate_backend(<pid of the process>)

[serpent7776]

IIRC I tried to kill the backend postgres process, but it was then auto restarted, but I think I used a different query. I'm not able to check it now, but I will try to check it in few days.

[serpent7776]

Sorry, I forgot about this. I can successfully kill the cron process, but then a new one is spawned.

postgres> SELECT
   pid as process_id,
   usename as database_user,
   application_name,
   backend_start as when_process_began,
   wait_event_type,
   state,
   query,
   backend_type
 FROM pg_stat_activity where application_name ilike 'pg_cron scheduler';
+------------+---------------+-------------------+-------------------------------+-----------------+--------+-------+------------------+
| process_id | database_user | application_name  | when_process_began            | wait_event_type | state  | query | backend_type     |
|------------+---------------+-------------------+-------------------------------+-----------------+--------+-------+------------------|
| 104195     | postgres      | pg_cron scheduler | 2024-11-26 16:25:15.464989+00 | Extension       | <null> |       | pg_cron launcher |
+------------+---------------+-------------------+-------------------------------+-----------------+--------+-------+------------------+
SELECT 1
Time: 0.007s

postgres> SELECT pg_terminate_backend(104195)
+----------------------+
| pg_terminate_backend |
|----------------------|
| True                 |
+----------------------+
SELECT 1
Time: 0.005s

postgres> SELECT
   pid as process_id,
   usename as database_user,
   application_name,
   backend_start as when_process_began,
   wait_event_type,
   state,
   query,
   backend_type
 FROM pg_stat_activity where application_name ilike 'pg_cron scheduler';
+------------+---------------+-------------------+------------------------------+-----------------+--------+-------+------------------+
| process_id | database_user | application_name  | when_process_began           | wait_event_type | state  | query | backend_type     |
|------------+---------------+-------------------+------------------------------+-----------------+--------+-------+------------------|
| 104412     | postgres      | pg_cron scheduler | 2024-11-26 16:30:04.98081+00 | Extension       | <null> |       | pg_cron launcher |
+------------+---------------+-------------------+------------------------------+-----------------+--------+-------+------------------+
SELECT 1
Time: 0.005s

[lmugnano4537]

I've encountered the same issue, is there a workaround for this yet? I was testing this on Greenplum 7.3 where this extension was introduced into the product. Now, I can't shutdown the database without specifying -M fast or immediate because this connection is always present. I tried all the same things mentioned on this ticket. Thanks

[bjorndarri]

I'm running into the same issue, can't drop a local development database instance (running in docker) due to the pg_cron scheduler session being active, even after dropping the extension and restarting the database.

The session restarts automatically when terminated.

The nuclear option would be to drop the the container and recreate it, but I'm regularly recreating dev-databases, so that's not really a feasible workaround.

I'm running the latest PostgreSQL version: PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1)

[rauanmayemir]

I'm facing a similar issue after switching to cron.use_background_workers = on. Restarting my postgres cluster will take a while and after failing to stop postgres gracefully within timeout pod is killed.

But this isn't likely about using backround workers, but more about having very high-frequency ('1 second') schedules.

[vvitad]

it is not okay at all. I've encountered the same issue. I needed to drop database, so after dropping extension, trying pg_terminate.. and kill -9, what helped is just trying to do both pg_terminate.. and drop database at the same type thanks to two terminals

[thibault-tiro]

We have the same problem. I wanted to drop a dev database with pg_cron jobs running. What ultimately worked for me was dropping the database with force

psql -h localhost -d postgres -U $DB_USER -c "DROP DATABASE IF EXISTS "$DB_NAME" WITH (FORCE);"

https://www.postgresql.org/docs/17/sql-dropdatabase.html

[bjorndarri]

What has been working for me recently is to run both commands together, with auto-commit enabled (I'm doing this via IntelliJ).

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE application_name ILIKE 'pg_cron scheduler';

DROP DATABASE devdb;