Bug 1828931

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.