pg_cron causes error when backup and restore DB
- Dominant language
- C
- Stars
- 3.9k
- Forks
- 260
- Avg merge
- 5d 5h
- Merged PRs (30d)
- 1
Description
Hi
When creating a backup for a DB with pg_cron extension installed
'CREATE POLICY cron_job_policy ON cron.job USING ((username = (CURRENT_USER)::text));'
seems to appear twice.
This results with an error when restoring the DB from the backup file:
pg_restore: creating POLICY "cron.job cron_job_policy"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3915; 3256 306262 POLICY job cron_job_policy
pg_restore: error: could not execute query: ERROR: policy "cron_job_policy" for table "job" already exists
Command was: CREATE POLICY cron_job_policy ON cron.job USING ((username = (CURRENT_USER)::text));
Please advise
Thank you
Contributor guide
No contributing guide indexed for this repository
Research direction
Start by reproducing the backup and restore flow for a PostgreSQL database with pg_cron installed, and inspect the generated backup for duplicate CREATE POLICY statements. Confirm why cron_job_policy is emitted twice and define done as a restore that completes without the existing-policy error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- c, postgresql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 20/100