From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: antoniok.spb@gmail.com
Subject: BUG #18107: "policy ... for table ... already exists" errors when restore postgres database from dump
Date: Thu, 14 Sep 2023 07:28:44 +0000
Message-ID: <18107-2668460d8c2804d2@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18107
Logged by: AntonioK
Email address: antoniok.spb@gmail.com
PostgreSQL version: 15.4
Operating system: Debian GNU/Linux 12 (bookworm)
Description:
I'm restoring postgresql database from dump on a 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 two 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 never created those POLICY by hands on "source" database. The guess is
(@see https://dba.stackexchange.com/a/331198/82463) that 'CREATE EXTENSION
... pg_cron ...' statement in the dump file is implicitly executes POLICY
creation, so 'CREATE POLICY ... ON cron...' statements in the dump file are
redundant and unnecessary.
Should we consider this as pg_dump bug?
Source server:
# grep PRETTY_NAME /etc/*rel*
PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"
# psql -V
psql (PostgreSQL) 15.3 (Debian 15.3-1.pgdg120+1)
# dpkg -l | grep postgresql
ii postgresql-15 15.3-1.pgdg120+1 amd64
ii postgresql-15-cron 1.5.2-1.pgdg120+1 amd64
ii postgresql-client-10 10.23-1.pgdg120+1+b2 amd64
ii postgresql-client-11 11.20-1.pgdg120+1 amd64
ii postgresql-client-12 12.15-1.pgdg120+1 amd64
ii postgresql-client-13 13.11-1.pgdg120+1 amd64
ii postgresql-client-14 14.8-1.pgdg120+1 amd64
ii postgresql-client-15 15.3-1.pgdg120+1 amd64
ii postgresql-client-common 250.pgdg120+1 all
ii postgresql-common 250.pgdg120+1 all
ii postgresql-plpython3-15 15.3-1.pgdg120+1 amd64
ii postgresql-server-dev-10 10.23-1.pgdg120+1+b2 amd64
ii postgresql-server-dev-11 11.20-1.pgdg120+1 amd64
ii postgresql-server-dev-12 12.15-1.pgdg120+1 amd64
ii postgresql-server-dev-13 13.11-1.pgdg120+1 amd64
ii postgresql-server-dev-14 14.8-1.pgdg120+1 amd64
ii postgresql-server-dev-15 15.3-1.pgdg120+1 amd64
ii postgresql-server-dev-all:amd64 250.pgdg120+1 amd64
Target server:
# grep PRETTY_NAME /etc/*rel*
PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"
# psql -V
psql (PostgreSQL) 15.4 (Debian 15.4-1.pgdg120+1)
# dpkg -l | grep postgresql
ii postgresql-15 15.4-1.pgdg120+1 amd64
ii postgresql-15-cron 1.5.2-2.pgdg120+2 amd64
ii postgresql-client-15 15.4-1.pgdg120+1 amd64
ii postgresql-client-common 253.pgdg120+1 all
ii postgresql-common 253.pgdg120+1 all
ii postgresql-plpython3-15 15.4-1.pgdg120+1 amd64
ii postgresql-server-dev-15 15.4-1.pgdg120+1 amd64
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, antoniok.spb@gmail.com
Subject: Re: BUG #18107: "policy ... for table ... already exists" errors when restore postgres database from dump
In-Reply-To: <18107-2668460d8c2804d2@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox