Jim Longwill <JLongwill@xxxxxxxxx> 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@xxxxxxxxx > --o--o--o--o--o--o--o--o--o--o--o--o-- -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@xxxxxxxxxxx p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
