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

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--

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800