On 06/21/2017 11:05 AM, Jerry Sievers wrote:
Jim Longwill <jlongw...@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
jlongw...@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 :^)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
