Re: [GENERAL] Error when building new db using pg_restore - Mailing list pgsql-general

On 06/21/2017 11:05 AM, Jerry Sievers wrote:
> Jim Longwill <JLongwill@psmfc.org> writes:
>
>> We have a (Linux CentOS) server, and one Postgres installation (v9.5).
>>
>> We have long been experiencing an error when doing a 'pg_restore'
>> database build from a tar
>> file.  Our procedure is as follows (err. is just after start of
>> restoring our schema 'rradmin'):
>>
>> First, an export is done to a .tar file from the maindb.  Second, the
>> rdev1 db is created with this command:
>>    CREATE DATABASE rdev1 TEMPLATE template0 OWNER rradmin;
>>
>> Then, we load in the tar file from the other db as follows:
>> pg_restore -U rradmin -d rdev1 -v $PG_EXPORT/maindb-cron.tar >
>> $PG_VAR/log/cron-rdev1-build-via-tar.log
>>
>> The build goes just fine; however, there is an 'error' in the above
>> log file. The error seems
>> of little consequence, but I'm curious as to the implications and how
>> it can be fixed.
>>
>> The log entries start in typical fashion, as follows:
>> pg_restore: connecting to database for restore
>> pg_restore: creating SCHEMA "public"
>> pg_restore: creating COMMENT "SCHEMA public"
>> pg_restore: creating SCHEMA "rradmin"
>> pg_restore: creating EXTENSION "plpgsql"
>> pg_restore: creating COMMENT "EXTENSION plpgsql"
>>
>> ..however, we then get the error corresponding roughly to the
>> following lines:
>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>> pg_restore: [archiver (db)] Error from TOC entry 4100; 0 0 COMMENT
>> EXTENSION plpgsql
>> pg_restore: [archiver (db)] could not execute query: ERROR:  must be
>> owner of extension plpgsql
>>      Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural
>> language';
> Your extension plpgsql is probably being created as result of having
> been a part of template0.
>
> If so, then it's not owned by your DB owner role but very likely
> 'postgres' which assuming your DB owner is *not* a superuser role, fails
> on the create comment statement.
>
> The create extension command that the pg_restore probably ran included
> the IF NOT EXISTS clause and was a no-op.
>
> Your logs may include a message at level NOTICE to indicate same.
>
> HTH
>
>> pg_restore: creating FUNCTION "rradmin.rarsr_check_upd_4days()"
>> pg_restore: creating FUNCTION ...
>> ..
>>
>> which continues w/ remainder of our objects.  Then.. it mentions the 1
>> error only at the end:
>> WARNING: errors ignored on restore: 1
>>
>> Now, during all this, the pg log file: ../pg_log/postgresql-Fri.log
>> has these entries:
>> < 2017-06-16 21:21:27.694 PDT >ERROR:  must be owner of extension plpgsql
>> < 2017-06-16 21:21:27.694 PDT >STATEMENT:  COMMENT ON EXTENSION
>> plpgsql IS 'PL/pgSQL procedural
>> language';
>>
>> < 2017-06-16 21:22:39.719 PDT >ERROR:  canceling autovacuum task
>> < 2017-06-16 21:22:39.720 PDT >CONTEXT:  automatic analyze of table
>> "rdev1.rradmin.rar_criteria_release"
>> < 2017-06-16 21:22:52.997 PDT >ERROR:  canceling autovacuum task
>> < 2017-06-16 21:22:52.997 PDT >CONTEXT:  automatic analyze of table
>> "rdev1.rradmin.recoveries_041"
>> < 2017-06-16 21:26:01.625 PDT >WARNING:  no privileges could be
>> revoked for "public"
>> < 2017-06-16 21:26:01.625 PDT >WARNING:  no privileges could be
>> revoked for "public"
>> < 2017-06-16 21:26:01.625 PDT >WARNING:  no privileges were granted
>> for "public"
>> < 2017-06-16 21:26:01.625 PDT >WARNING:  no privileges were granted
>> for "public"
>> ..
>>
>> So, these errors don't appear to cause problems on the target database
>> (rdev1), but it might be
>> an issue if we tried to do more things with PL/pgSQL.  Is it
>> cancelling the autovacuum task on a
>> long term basis?  This same result also occurs when creating using
>> 'template1' db.
>>
>> Any thoughts on this?
>>
>> --
>> --o--o--o--o--o--o--o--o--o--o--o--o--
>> Jim Longwill
>> PSMFC Regional Mark Processing Center
>> Ph:503-595-3146; FAX:503-595-3446
>> JLongwill@psmfc.org
>> --o--o--o--o--o--o--o--o--o--o--o--o--
Thank you Jerry S.  Ok.. Indeed, our 'rradmin' user does not have
SUPERUSER role.
So.. as I understand this..
   * This fails on creating the COMMENT about object: extension plpgsql,
not on creation of
     the object itself?
   * It is a no-op because extension plpgsql already exists in the
target db rdev1 when this
     error occurs.. yes?  (i.e. so there was NO problem w/ creation of
plpgsql)?

Given this, I wonder how we can avoid this 'error' situation .. short of
giving SUPERUSER
     role to rradmin?  I tried just removing the '-v' flag from
pg_restore, but the error is
     still put in the log file (but nothing else is).

A related (rookie!) question:  How do I easily look at comment entries
for objects?
I tried this in psql as either postgres or rradmin .. on several of our
databases.. but got
0 rows:

rdev1=# \dd
          Object descriptions
  Schema | Name | Object | Description
--------+------+--------+-------------
(0 rows)

So.. our databases have no COMMENT entries?  Just wondering.
--Jim  :^)