Is this normal? and is pg_cron in use or not or installed?

Question

Hi,

I am running the SQL below to check on pg_stat_activity.

```
select pid as process_id,
       usename as username,
       datname as database_name,
       client_addr as client_address,
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity
order by username asc
;
```
Output is below. I can see an application named pg_cron scheduler.

```
Checking further

=> SELECT * from cron.job ;
ERROR:  relation "cron.job" does not exist
LINE 1: SELECT * from cron.job ;
                      ^

```

Is this normal? Is this something internal to AWS RDS? Does that mean I have to install pg_cron if I want to use for other existing schema?

Yann Allandit · Answer

The pg_cron extension is supported on Aurora PostgreSQL engine versions 12.6 and higher version.
It is available but not installed by default.
First you need to modify your cluster parameter group and add pg_cron to the shared_preload_libraries parameter. A DB reboot is requested as this is a static parameter.
Then you can create the extension using "CREATE EXTENSION pg_cron;" with a rds_superuser priviledged user.

You can get more details in https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_pg_cron.html#PostgreSQL_pg_cron.enable

re:Post Agent · Answer

Based on the information provided, it appears that pg_cron is not fully installed or configured in your RDS PostgreSQL instance, despite seeing a "pg_cron scheduler" entry in pg_stat_activity.

The presence of the "pg_cron scheduler" in pg_stat_activity suggests that pg_cron might be partially set up or in use by AWS RDS for internal purposes. However, this doesn't necessarily mean it's available for your use in your database schemas.

The fact that you're unable to query the cron.job table and receive an error stating "relation "cron.job" does not exist" indicates that the pg_cron extension is not properly installed or enabled for your use.

Additionally, when you list the installed extensions using \dx, pg_cron is not present in the list, which further confirms that it's not fully installed for your use.

If you want to use pg_cron for your own scheduling needs in existing schemas, you would need to properly install and configure the extension. This typically involves:

1. Modifying the custom parameter group to add pg_cron to shared_preload_libraries.
2. Restarting the RDS instance.
3. Creating the extension in your database using CREATE EXTENSION pg_cron;

After these steps, you should be able to use pg_cron in your schemas. Remember to grant necessary permissions to database users who need to create and manage cron jobs.

The current state you're observing is not unusual for an RDS PostgreSQL instance where pg_cron hasn't been explicitly set up for use, but AWS might be using it internally for some management tasks.  
**Sources**  
[Scheduling maintenance with the PostgreSQL pg_cron extension - Amazon Relational Database Service](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html)  
[Scheduling maintenance with the PostgreSQL pg_cron extension - Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_pg_cron.html)  
[pg_cron not running scheduled tasks | AWS re:Post](https://repost.aws/questions/QUb0Hthg54QZiRKDYj453e_w/pg-cron-not-running-scheduled-tasks)

Is this normal? and is pg_cron in use or not or installed?

Add your answer

Relevant content