Sample Header Ad - 728x90

pg_cron: "policy ... for table ... already exists" error while restore postgres database from dump

3 votes
2 answers
678 views
I'm trying to restore postgresql database from dump on fresh new server. Source database uses pgcron extension, target server have pgcron extension installed. This is how I create dumps on "source" server: pg_dumpall -h hostname -p 5435 -U myuser --roles-only | bzip2 -c -z > dump-role.sql.bz2 pg_dump -C -h hostname -p 5435 -U myuser mydatabase | bzip2 -c -z > dump-data.sql.bz2 This is how I restore dumps on empty "target" server: grep -Eiv '(CREATE ROLE postgres|ALTER ROLE postgres .*PASSWORD)' dump-role.sql | psql -Upostgres > /dev/null psql -Upostgres -f dump-data.sql > /dev/null *grep on the first command is used to left "postgres" superuser untouched on a target server.* When restoring from dump, I got 2 errors: psql:/path/to/dump-data.sql:18831791: ERROR: policy "cron_job_policy" for table "job" already exists psql:/path/to/dump-data.sql:18831798: ERROR: policy "cron_job_run_details_policy" for table "job_run_details" already exists They refers to the following lines of dump: -- Name: job cron_job_policy; Type: POLICY; Schema: cron; Owner: some_user CREATE POLICY cron_job_policy ON cron.job USING ((username = CURRENT_USER)); -- Name: job_run_details cron_job_run_details_policy; Type: POLICY; Schema: cron; Owner: some_user CREATE POLICY cron_job_run_details_policy ON cron.job_run_details USING ((username = CURRENT_USER)); I guess this is some pg_cron "self-made" structure, I never created those POLICY by hands on "source" database. Is there a way to fix this errors? Or maybe should I just ignore them?
Asked by AntonioK (133 rep)
Sep 13, 2023, 10:13 AM
Last activity: Feb 19, 2024, 09:06 PM