0

I have an RDS postgres database with pg_cron installed. Dev ops is claiming that pg_cron can only be installed for the default "postgres" database.

I would like to be able to schedule jobs in pg_cron, but any attempt to do so from my database receives the well-known

ERROR:  cross-database references are not implemented: "postgres.cron.job"

I would like to be able to run the select statement (to create the job) and the update statement (to set it to the correct database) from TypeORM migration scripts. 99% of these scripts would be run while connected to my own database, and only these select/update statements to schedule jobs would be connected to "postgres". The scripts will not be ordered, so that I can wait until the end, switch to another data source, and then continue running the remainder. They will occur in some semi-random order as myself and other developers recognize the need to add, remove, or modify scheduled jobs.

Is there (in Postgres) syntax that in one or more statements, allows you to specify that you want to run a specific statement as if it were with a new connection to another database running on the Postgres server?

If I were using psql, I might just do \c postgres and then select cron.schedule(blah blah);. I'm not using psql. I'm not attempting any joins. I'd just like to switch databases, run the statement (or two), and switch back. It'd be ok if this were inside of an anonymous do $$ begin end $$; block.

I cannot figure out what to even google for that doesn't get a thousand false hits, or doesn't immediately lead to C embedded SQL's CONNECT TO statement which doesn't seem to have a pgplsql equivalent.

John O
  • 4,863
  • 8
  • 45
  • 78
  • 1
    `pg_cron` has docs [pg_cron](https://github.com/citusdata/pg_cron) where you can find: *pg_cron may only be installed to one database in a cluster. If you need to run jobs in multiple databases, use cron.schedule_in_database().* FYI, it is not restricted to being installing in the `postgres` database that is just the default. – Adrian Klaver Mar 28 '23 at 18:40
  • @AdrianKlaver The schedule_in_database() function is interesting, however, I would still have the trouble of not being able to call it before connecting to postgres. I will investigate whether pg_cron can be installed for the other database, but if dev ops is saying that it can't then it might not matter whether that's true or not. Thanks much, either of those would have been acceptable as an answer for me, having both is more than I could hope for. – John O Mar 28 '23 at 20:49
  • Use [dblink](https://www.postgresql.org/docs/current/dblink.html) or [postgres_fdw](https://www.postgresql.org/docs/current/postgres-fdw.html)? – Adrian Klaver Mar 28 '23 at 21:17
  • @AdrianKlaver It's my understanding that fdw can only hit remove servers and the databases within them, and not a database residing on the same server. – John O Mar 29 '23 at 05:20
  • That is not true. Remote database for a FDW is any database that is not the one you are connecting from. It can be in the same cluster e.g. `create server production_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS(dbname 'production', host 'localhost', port '5432');` where the database I'm doing this is from is also at `localhost` and `5432`. – Adrian Klaver Mar 29 '23 at 15:28

0 Answers0