Thread: [GENERAL] Error when building new db using pg_restore
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';
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--
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
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 :^)
On Wed, Jun 21, 2017 at 1:10 PM, Jim Longwill <JLongwill@psmfc.org> wrote: > 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). I believe the easiest solution, if you can at least get access to superuser once, is to drop the comment from the source database altogether. That way it won't be dumped in the first place. I'm not sure the exact command you need to issue though - just recalling what I've previously read on the topic. David J.
On Wed, Jun 21, 2017 at 1:10 PM, Jim Longwill <JLongwill@psmfc.org> wrote: > rdev1=# \dd > Object descriptions > Schema | Name | Object | Description > --------+------+--------+------------- > (0 rows) Nothing user created has comments by default. \dd only shows a very limited subset of user created objects by default. That you didn't explicit add comments to any of those types objects is unsurprising. See the docs for exactly what "\dd" and "\ddS" will consider. David J.
On 06/21/2017 01:21 PM, David G. Johnston wrote: > On Wed, Jun 21, 2017 at 1:10 PM, Jim Longwill <JLongwill@psmfc.org> wrote: > >> rdev1=# \dd >> Object descriptions >> Schema | Name | Object | Description >> --------+------+--------+------------- >> (0 rows) > Nothing user created has comments by default. \dd only shows a very > limited subset of user created objects by default. That you didn't > explicit add comments to any of those types objects is unsurprising. > See the docs for exactly what "\dd" and "\ddS" will consider. > > David J. Ok.. I've tried again using psql \dd.. type commands to locate this COMMENT object, including as 'postgres' and in the source database of the tar file but it does not show up. Yes.. we have never put in any object comments, so it must have come in from the postgres installation. It'll be tough to delete this thing if I can't find it, but oh well! I won't worry about this much more, but if the system identifies it as an error then I thought it was at least worth making the inquiry. Thanks! --Jim :^)
On 06/21/2017 05:06 PM, Jim Longwill wrote:
> On 06/21/2017 01:21 PM, David G. Johnston wrote:
>> On Wed, Jun 21, 2017 at 1:10 PM, Jim Longwill <JLongwill@psmfc.org>
>> wrote:
>>
>>> rdev1=# \dd
>>> Object descriptions
>>> Schema | Name | Object | Description
>>> --------+------+--------+-------------
>>> (0 rows)
>> Nothing user created has comments by default. \dd only shows a very
>> limited subset of user created objects by default. That you didn't
>> explicit add comments to any of those types objects is unsurprising.
>> See the docs for exactly what "\dd" and "\ddS" will consider.
>>
>> David J.
> Ok.. I've tried again using psql \dd.. type commands to locate this
> COMMENT object,
> including as 'postgres' and in the source database of the tar file but
> it does not show up.
> Yes.. we have never put in any object comments, so it must have come in
> from the postgres
> installation. It'll be tough to delete this thing if I can't find it,
> but oh well!
Did you try \dL?:
test=> \dL
List of languages
Name | Owner | Trusted | Description
-----------+----------+---------+------------------------------------------
plpgsql | postgres | t | PL/pgSQL procedural language
plpythonu | postgres | f | PL/PythonU untrusted procedural
language
>
> I won't worry about this much more, but if the system identifies it as
> an error then
> I thought it was at least worth making the inquiry. Thanks!
> --Jim :^)
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 06/21/2017 05:30 PM, Adrian Klaver wrote: > On 06/21/2017 05:06 PM, Jim Longwill wrote: >> On 06/21/2017 01:21 PM, David G. Johnston wrote: >>> On Wed, Jun 21, 2017 at 1:10 PM, Jim Longwill <JLongwill@psmfc.org> >>> wrote: >>> >>>> rdev1=# \dd >>>> Object descriptions >>>> Schema | Name | Object | Description >>>> --------+------+--------+------------- >>>> (0 rows) >>> Nothing user created has comments by default. \dd only shows a very >>> limited subset of user created objects by default. That you didn't >>> explicit add comments to any of those types objects is unsurprising. >>> See the docs for exactly what "\dd" and "\ddS" will consider. >>> >>> David J. >> Ok.. I've tried again using psql \dd.. type commands to locate this >> COMMENT object, >> including as 'postgres' and in the source database of the tar file >> but it does not show up. >> Yes.. we have never put in any object comments, so it must have come >> in from the postgres >> installation. It'll be tough to delete this thing if I can't find >> it, but oh well! > > Did you try \dL?: > > test=> \dL > > List of languages > > Name | Owner | Trusted | Description > > -----------+----------+---------+------------------------------------------ > > > plpgsql | postgres | t | PL/pgSQL procedural language > > plpythonu | postgres | f | PL/PythonU untrusted procedural > language > >> >> I won't worry about this much more, but if the system identifies it >> as an error then >> I thought it was at least worth making the inquiry. Thanks! >> --Jim :^) >> >> > > Ok. Thank you for the input. I do see the 'Description' of plpgsql there. So, if I can determine the source table(s) of this information perhaps I could delete that particular value. I tried the following as per documentation: COMMENT ON EXTENSION plpgsql IS NULL; But it did not appear to remove the comment/description. will investigate further.. --Jim :^)
On 06/22/2017 10:05 AM, Jim Longwill wrote: >> >> > Ok. Thank you for the input. I do see the 'Description' of plpgsql > there. So, if I can determine the source table(s) of this information > perhaps I could delete that particular value. I tried the following > as per documentation: > COMMENT ON EXTENSION plpgsql IS NULL; > But it did not appear to remove the comment/description. will > investigate further.. Probably because languages are installed as extensions now and have an extension script: plpgsql--1.0.sql /* src/pl/plpgsql/src/plpgsql--1.0.sql */ /* * Currently, all the interesting stuff is done by CREATE LANGUAGE. * Later we will probably "dumb down" that command and put more of the * knowledge into this script. */ CREATE PROCEDURAL LANGUAGE plpgsql; COMMENT ON PROCEDURAL LANGUAGE plpgsql IS 'PL/pgSQL procedural language'; > --Jim :^) -- Adrian Klaver adrian.klaver@aklaver.com