Description of problem: When running engine-vacuum command it fails with permission denied for schema pg_temp_23 and various other schemas, I reproduced this with pg_temp_19, pg_temp_9 and pg_temp_23. Version-Release number of selected component (if applicable): ovirt-engine-tools-4.4.0-0.33.master.el8ev.noarch How reproducible: always Steps to Reproduce: 1. run engine-vacuum 2. 3. Actual results: vacuumdb: vacuuming database "engine" vacuumdb: error: vacuuming of table "pg_temp_23.tt_vm_pool_group" in database "engine" failed: ERROR: permission denied for schema pg_temp_23 Expected results: should vacuum the DB without errors Additional info:
engine-vacuum actually calls PG's vacuumdb utility I checked running directly vacuumdb with user 'engine' on the 'engine' db /usr/bin/vacuumdb -d engine -U engine vacuumdb: error: vacuuming of table "pg_temp_6.tt_vm_pool_running" in database "engine" failed: ERROR: permission denied for schema pg_temp_6 googling this a bit I found that [1] So, this seems as a PG regression that forces vacuumdb to use postgres admin user to perform vacuum [1] https://github.com/greenplum-db/gpdb/pull/9304
It seems like orphan schemas are left in the database , those schemas are not owned by the 'engine' role and can not be dropped by it engine=> select nspname from pg_namespace where nspname like 'pg_temp%'; nspname ------------ pg_temp_1 pg_temp_11 pg_temp_12 pg_temp_8 pg_temp_6 pg_temp_3 pg_temp_14 pg_temp_13 pg_temp_4 pg_temp_7 (10 rows) The drop statements for those orphan schemas can be extracted to a sql script by; psql -U engine -Atc "select 'drop schema if exists ' || nspname || ' cascade;' from (select nspname from pg_namespace where nspname like 'pg_temp%') as foo" engine > drop_orphan_schemas.sql Then we can run psql to execute this script before running vacuumdb , but still , the sql script must run from psql with the 'postgres' user
This bug report has Keywords: Regression or TestBlocker. Since no regressions or test blockers are allowed between releases, it is also being identified as a blocker for this release. Please resolve ASAP.
The documentation text flag should only be set after 'doc text' field is provided. Please provide the documentation text and set the flag to '?' again.
This paragraph should be added to the release doc: For remote vacuuming if you got errors like 'permission denied for schema pg_temp_XX' please do the following: 1) log in into the remote database machine 2) run psql -U <db-admin-role> -Atc \"select 'drop schema if exists ' || nspname || ' cascade;' from (select distinct nspname from pg_class join pg_namespace on (relnamespace=pg_namespace.oid) where pg_is_other_temp_schema(relnamespace)) as foo\" <engine database name > <temporary file> 3) run psql -U <db-admin-role> -f <temporary file> 4) try to run engine-vacuum again"
(In reply to Eli Mesika from comment #5) > This paragraph should be added to the release doc: > > > For remote vacuuming if you got errors like 'permission denied for schema > pg_temp_XX' please do the following: > 1) log in into the remote database machine > 2) run > psql -U <db-admin-role> -Atc \"select 'drop schema if exists ' > || nspname || ' cascade;' > from (select distinct nspname from pg_class join pg_namespace on > (relnamespace=pg_namespace.oid) > where pg_is_other_temp_schema(relnamespace)) as foo\" <engine > database name > <temporary file> > 3) run > psql -U <db-admin-role> -f <temporary file> > 4) try to run engine-vacuum again" Steven, we should also add this as a step into 4.4 upgrade guide to upgrade remote database chapters. Probably after restoring 4.3 database backup and upgrading this database from 10.6 to 12 and before running engine-setup.
OK. I added this comment to bug 1802650.
Verified on ovirt-engine-4.4.1-0.1.el8ev.noarch
This bugzilla is included in oVirt 4.4.1 release, published on July 8th 2020. Since the problem described in this bug report should be resolved in oVirt 4.4.1 release, it has been closed with a resolution of CURRENT RELEASE. If the solution does not work for you, please open a new bug report.