citusdata / citusdata/pg_cron

pg_cron causes error when backup and restore DB

Open
#113 6 comments 0 reactions 0 assignees View on GitHub
bug
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.