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

Question

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?

Answer 1

This is caused by a shortcoming in PostgreSQL.

Normally, all objects created by an extension automatically become members of the extension. The effect is that such extension members of an extension don't get dumped separately by pg_dump, as these objects are considered part of the extension, and the statement

CREATE EXTENSION whatever;

in the dump will re-create these objects anyway.

Now this works for all kinds of objects, but not for row-level security policies. There are no provisions in PostgreSQL to mark such policies as extension members, and pg_dump will always include CREATE POLICY statements for them.

There are two things you can do:

1. Ignore the error. It is caused by the extra CREATE POLICY statement that pg_dump generates, since it does not recognize the policy as part of the extension.

2. Report it as a bug or an enhancement request to PostgreSQL. I guess nobody thought that an extension might create a row-level security policy.

Answer 2

I ended up using the pg_dump --extension flag to exclude pg_cron from the dump and creating it manually before (since you might have objects depending on it) restoring:

create extension if not exists pg_cron;