Hard pg_cron 1.5+ dependency on pg_catalog breaks restores of dumps created when using older extension versions
- Dominant language
- C
- Stars
- 3.9k
- Forks
- 260
- Avg merge
- 5d 5h
- Merged PRs (30d)
- 1
Description
### Summary
We've noticed that restoring Postgres dumps created when using older versions of pg_cron (namely 1.4.2 in our case) against a database where pg_cron 1.5+ is installed break due to a newly introduced hard, and undocumented, requirement on creating the extension in the `pg_catalog` schema.
### Description
`pg_dump` generates the extension-related section of its database dumps in the following format, explicitly stating the schema where the extension was enabled at dump-time:
```
--
-- Name: pg_cron; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS pg_cron WITH SCHEMA public;
```
This dump was created off a Postgres install running pg_cron 1.4.2, with the extension enabled in the `public` schema by running `CREATE EXTENSION pg_cron;`. As the user's `search_path` was set to the `public` schema, the extension defaulted to it during creation.
When restoring the above dump against a database service running pg_cron 1.5+, the following error is encountered:
```
psql:db_cluster-01-06-2023@06-34-36.backup:203: ERROR: extension "pg_cron" must be installed in schema "pg_catalog"
```
This, in turn has the following effect:
* when allowing pg_restore to continue on error - `pg_cron` is not created, and any previously created cronjob does not exist post-restore
* when using pg_restore in conjunction with `--exit-on-error` - restore fails
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.