Re: [GENERAL] Error when building new db using pg_restore

2017-06-22 Thread Adrian Klaver 

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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Error when building new db using pg_restore

2017-06-22 Thread Jim Longwill 

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  
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  :^)


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Error when building new db using pg_restore

2017-06-21 Thread Adrian Klaver 

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  
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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Error when building new db using pg_restore

2017-06-21 Thread Jim Longwill 

On 06/21/2017 01:21 PM, David G. Johnston wrote:

On Wed, Jun 21, 2017 at 1:10 PM, Jim Longwill  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  :^)


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Error when building new db using pg_restore

2017-06-21 Thread David G. Johnston 
On Wed, Jun 21, 2017 at 1:10 PM, Jim Longwill  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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Error when building new db using pg_restore

2017-06-21 Thread David G. Johnston 
On Wed, Jun 21, 2017 at 1:10 PM, Jim Longwill  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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Error when building new db using pg_restore

2017-06-21 Thread Jim Longwill 



On 06/21/2017 11:05 AM, Jerry Sievers wrote:

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

Re: [GENERAL] Error when building new db using pg_restore

2017-06-21 Thread Jerry Sievers 
Jim Longwill  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--

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general