Can I schedule pg_cron jobs to run in multiple databases

1. Single Database for Metadata: By default, `pg_cron` expects its metadata tables to be created in the "postgres" database. You can configure this by setting the `cron.database_name` parameter in `postgresql.conf`.

2. Scheduling Jobs in Other Databases: While `pg_cron` is managed through a single database, you can schedule jobs to run in other databases using the `cron.schedule_in_database()` function introduced in version 1.4. This allows you to specify the database where the job should be executed.

Example:

sql
   SELECT cron.schedule_in_database('my_job', '* * * * *', 'SELECT my_function();', 'other_db');
   

3. Altering Existing Jobs: You can also change the database of an existing job using the `cron.alter_job()` function.

Example:

sql
   SELECT cron.alter_job(jobid, 'database', 'other_db');
   

4. Permissions: Jobs are executed in the database where the `cron.schedule()` or `cron.schedule_in_database()` function is called, with the same permissions as the current user. Users can only see their own jobs in the `cron.job` table.

5. Separate Database for Metadata: As an alternative, you can create a separate database (e.g., `pg_cron`) to store the `pg_cron` metadata tables, and then schedule jobs in other databases like `foo` and `bar`. This can be done by setting `cron.database_name = 'pg_cron'` in `postgresql.conf`.

In summary, you can use `pg_cron` to schedule jobs in multiple databases, but you need to consider the limitations around the metadata database and permissions. The `cron.schedule_in_database()` and `cron.alter_job()` functions introduced in version 1.4 provide more flexibility in this regard.