citusdata / citusdata/pg_cron

Hard pg_cron 1.5+ dependency on pg_catalog breaks restores of dumps created when using older extension versions

Open
#274 17 comments 12 reactions 0 assignees View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.