Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

[Christoph Berg]

Re: Devrim Gündüz 2018-08-03 
<1cdedaf455c4f326f31b103ab805d48da9914cb7.ca...@gunduz.org>
> > What about 3rd party libraries like plv8 - Who and How (based on which
> > criteria, which versions) build RPM and upload them there?
> 
> Latest versions of PL/v8 does not build on RHEL/Fedora anymore, at least from
> the package build point of view. RPMs are not supposed to download extra
> dependencies from elsewhere.

Fwiw, I stopped maintaining plv8 in Debian for that reason. The build
process is roughly equivalent to downloading all of chromium's
dependencies, and build v8 from that. v8 is no longer a shared
library, unfortunately.

Christoph


signature.asc
Description: PGP signature

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

[Pavel Raiskup]

On Friday, August 3, 2018 8:08:55 AM CEST Devrim Gündüz wrote:
> On Thu, 2018-08-02 at 16:26 +0300, Alexandru Lazarev wrote:
> > What about 3rd party libraries like plv8 - Who and How (based on which
> > criteria, which versions) build RPM and upload them there?
>
> Latest versions of PL/v8 does not build on RHEL/Fedora anymore, at least from
> the package build point of view.

Yes, packaging of plv8 is pretty complicated.  If one decided to ship RPM
package with plv8, it would mean maintenance of whole v8 language - which
is incredibly complicated (incompatible changes all the time, backporting
security fixes, etc.).

That's the reason why plv8 (and even v8 runtime) becomes dropped from Linux
distributions.

[1] https://github.com/plv8/plv8/issues/281

Pavel

how to install pgcrypto

[Ze Victor Harry]

 hello again I have a small problem here can someone tell me briefly how to
do it? I am getting this error When I give ant fresh_install it gives error
PostgreSQL 'pgcrypto' extension installed /up to date? False (not
installed) Create extension pgcrypto

.
I have tried to look up for solutions and they all say I have to run a
command
# Login to your "dspace" database as a superuser
psql --username=postgres dspace
# Enable the pgcrypto extension on this database
CREATE EXTENSION pgcrypto;
 but where I got confused is from where do I run these commands.is it from
cmd? or in pgadmin query tool? I need clearly articulated  steps

RE: how to install pgcrypto

[Charles Clavadetscher]

Hi

 

From: Ze Victor Harry [mailto:assayab...@gmail.com] 
Sent: Freitag, 3. August 2018 10:54
To: pgsql-gene...@postgresql.org
Subject: how to install pgcrypto

 

hello again I have a small problem here can someone tell me briefly how to do 
it? I am getting this error 


 

 When I give ant fresh_install it gives error PostgreSQL 'pgcrypto' extension 
installed /up to date? False (not installed) Create extension pgcrypto.


I have tried to look up for solutions and they all say I have to run a command 

# Login to your "dspace" database as a superuser

psql --username=postgres dspace

 

With this command on the linux shell your run psql, which is the PostgreSQL 
client that always is shipped with the server.

In the psql shell you can then run the create extension command as below.

You may do this in PgAdmin (a graphical UI for Windows), too.

 

The whole point is that you must first log into the database as a superuser 
before you install the extension.

 

Hope this helps.

Bye

Charles

 

# Enable the pgcrypto extension on this database

CREATE EXTENSION pgcrypto;

 but where I got confused is from where do I run these   
commands.is it from cmd? or in pgadmin query tool? I need clearly articulated  
steps

 

WAL-dir filling up with wal_level = logical

[Andreas Joseph Krogh]

Version: PG-10.4
 
I don't (yet) have any replication-slots configured, and hence no standbys are 
subscribed, but I have wal_level = logical configured to be able to add 
subscribers later. I'm seeing that WAL-dir is filling up with WAL-files (now 
17GB and not declining), is this expected behaviour?
 
Thanks.
 
--
 Andreas Joseph Krogh

Sv: WAL-dir filling up with wal_level = logical

[Andreas Joseph Krogh]

På fredag 03. august 2018 kl. 12:12:33, skrev Andreas Joseph Krogh <
andr...@visena.com >:
Version: PG-10.4
 
I don't (yet) have any replication-slots configured, and hence no standbys are 
subscribed, but I have wal_level = logical configured to be able to add 
subscribers later. I'm seeing that WAL-dir is filling up with WAL-files (now 
17GB and not declining), is this expected behaviour?
 
Thanks.
 
Hm, a co-worker noticed that we have max_wal_size = 16GB, that might be the 
reason:-)
 
--
 Andreas Joseph Krogh

Re: WAL-dir filling up with wal_level = logical

[Andreas Kretschmer]

On 3 August 2018 12:12:33 CEST, Andreas Joseph Krogh  wrote:
>Version: PG-10.4
> 
>I don't (yet) have any replication-slots configured, and hence no
>standbys are 
>subscribed, but I have wal_level = logical configured to be able to add
>
>subscribers later. I'm seeing that WAL-dir is filling up with WAL-files
>(now 
>17GB and not declining), is this expected behaviour?
> 

No. Maybe a not-working archive_command?


Regards, Andreas


-- 
2ndQuadrant - The PostgreSQL Support Company

Re: Sv: WAL-dir filling up with wal_level = logical

[Andreas Kretschmer]

On 3 August 2018 12:33:26 CEST, Andreas Joseph Krogh  wrote:
>På fredag 03. august 2018 kl. 12:12:33, skrev Andreas Joseph Krogh <
>andr...@visena.com >:
>Version: PG-10.4
> 
>I don't (yet) have any replication-slots configured, and hence no
>standbys are 
>subscribed, but I have wal_level = logical configured to be able to add
>
>subscribers later. I'm seeing that WAL-dir is filling up with WAL-files
>(now 
>17GB and not declining), is this expected behaviour?
> 
>Thanks.
> 
>Hm, a co-worker noticed that we have max_wal_size = 16GB, that might be
>the 
>reason:-)
> 

Yes ;-)


Regards, Andreas


-- 
2ndQuadrant - The PostgreSQL Support Company

SQL Programming Assistant for PostgreSQL

[Dmitry Igrishin]

Hello all,

I've released the first version of a simple tool, - pgspa, that can
help one to develop in SQL for PostgreSQL.
The primary goal is to provide the functional for creating the
database objects from the SQL files of
arbitrary directory hierarchy without worrying about both the
existence of these objects in the database
and about the order of SQL statements in the source files. Also by
using the extension for PostgreSQL it is
possible to remove the bunch of objects from the database schemas in
non-casade mode.

pgspa is written in C++ and absolutely free. Any feedback are welcome. Thanks.

Please, see the repository on Github for more details:
https://github.com/dmitigr/pgspa

List of objects owned by a schema/owner

[Tiffany Thang]

Hi,
Does anyone have a query that will list all the objects (composite types,
sequences, tables, triggers, functions, indices, etc) owned by a schema or
owner? I find fragments of information here and there that query on
pg_tables, pg_views, etc.

Thanks.

Re: List user who have access to schema

[pinker]

I think this one will give you report you need:

select schema_name, 
roleid::regrole, 
string_agg(member::regrole::text,',' order by member::regrole::text) users
from information_schema.schemata s, pg_user u
JOIN pg_auth_members a ON u.usename::text=a.roleid::regrole::text
WHERE s.schema_name not like 'pg_%' AND has_schema_privilege(usename,
s.schema_name, 'usage')
GROUP BY s.schema_name, roleid::regrole, u.usename
order by 1;



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: List of objects owned by a schema/owner

[Tom Lane]

Tiffany Thang  writes:
> Does anyone have a query that will list all the objects (composite types,
> sequences, tables, triggers, functions, indices, etc) owned by a schema or
> owner? I find fragments of information here and there that query on
> pg_tables, pg_views, etc.

Well, you could attack it the hard way:

  select relname from pg_class where relowner = [oid of role of interest]
  union all
  ... similar select from every other catalog that has an owner column ...

I don't know if anyone's built such a query before, but a little quality
time with the system catalog documentation would get you there:
https://www.postgresql.org/docs/current/static/catalogs.html

Another idea is to rely on owner dependencies recorded in pg_shdepend,
along the lines of

select pg_describe_object(classid,objid,objsubid)
from pg_shdepend where deptype = 'o' and
  refobjid = [oid of role of interest] and
  dbid = [oid of current database];

That won't work for objects owned by the bootstrap superuser, and
I think there are some other omissions --- for instance, it looks
like we only record an owner dependency for a table, not for its
indexes.

Or, if you're feeling truly lazy, you can do

begin;
drop user joe;
-- read the error message whining about what joe owns
rollback;

That's largely a hacky way to get the same info as the pg_shdepend
query I sketched before, since the error message is derived from
exactly that info.

You have the same three options for schemas, though the details of each
are a bit different (in particular, schema dependencies would be found
in pg_depend not pg_shdepend).

regards, tom lane

RE: how to install pgcrypto

[Charles Clavadetscher]

CCing list.

 

From: Charles Clavadetscher [mailto:clavadetsc...@swisspug.org] 
Sent: Freitag, 3. August 2018 17:20
To: 'Ze Victor Harry' 
Subject: RE: how to install pgcrypto

 

Hi

 

From: Ze Victor Harry [mailto:assayab...@gmail.com] 
Sent: Freitag, 3. August 2018 17:11
To: Charles Clavadetscher mailto:clavadetsc...@swisspug.org> >
Subject: Re: how to install pgcrypto

 

hello again  Charles Clavadetscher 

how many hours does it take to complete ant fresh_install

it has been 4 hrs since i have started it but it is still not finished.I am 
using fast broadband internet(25 MB/s).is something wrong or is usually like 
that?



 

 

Well, to install PostgreSQL itself goes quickly. I can’t tell you exactly how 
long, but it is a matter of minutes.

In the screenshot that you sent, it looks like you are downloading a file, 
probably with data used by the appication installed in c:\dspace\webapps.

I assume that this is what is taking time, depending on how big the data is.

 

You may ask the developers of the web application on that. This is hardly a 
database question.

 

BTW: do not top post when responding on the list and, since you are starting a 
new topic (the pgcrypto installation of the subject is solved), you should 
start a new thread, so that other people may look at it.

 

Regards

Charles

 

On Fri, Aug 3, 2018 at 2:19 PM, Charles Clavadetscher 
mailto:clavadetsc...@swisspug.org> > wrote:

Hi

 

From: Ze Victor Harry [mailto:assayab...@gmail.com 
 ] 
Sent: Freitag, 3. August 2018 12:53
To: Charles Clavadetscher mailto:clavadetsc...@swisspug.org> >
Subject: Re: how to install pgcrypto

 

 




Thanx  Charles Clavadetsche


 I am installing dspace in windows 10 and I created the extension by going to 
OBJECT->CREATE->EXTENSION and created pgcrypto successfully that is what I have 
been asking and it is working fine. am I welcome to ask further questions on 
the installation though i am new to this kind of installation?

 

Of course. That’s the reason of existence of this mailing list.

 

On Fri, Aug 3, 2018 at 12:21 PM, Charles Clavadetscher 
mailto:clavadetsc...@swisspug.org> > wrote:

Hi

 

From: Ze Victor Harry [mailto:assayab...@gmail.com 
 ] 
Sent: Freitag, 3. August 2018 10:54
To: pgsql-gene...@postgresql.org  
Subject: how to install pgcrypto

 

hello again I have a small problem here can someone tell me briefly how to do 
it? I am getting this error 


 

 When I give ant fresh_install it gives error PostgreSQL 'pgcrypto' extension 
installed /up to date? False (not installed) Create extension pgcrypto.


I have tried to look up for solutions and they all say I have to run a command 

# Login to your "dspace" database as a superuser

psql --username=postgres dspace

 

With this command on the linux shell your run psql, which is the PostgreSQL 
client that always is shipped with the server.

In the psql shell you can then run the create extension command as below.

You may do this in PgAdmin (a graphical UI for Windows), too.

 

The whole point is that you must first log into the database as a superuser 
before you install the extension.

 

Hope this helps.

Bye

Charles

 

# Enable the pgcrypto extension on this database

CREATE EXTENSION pgcrypto;

 but where I got confused is from where do I run these   
commands.is it from cmd? or in pgadmin query tool? I need clearly articulated  
steps

 

 

 

Re: how to install pgcrypto

[Tim Clarke]

The psql command is a client - an "interactive terminal". It provides a
command-line version of pgAdmin.

See here for other client applications:

https://www.postgresql.org/docs/10/static/reference-client.html

The "create extension..." command is one that you give to either psql or
pgAdmin; they execute it against the current database.

Tim Clarke


On 03/08/18 09:53, Ze Victor Harry wrote:
> hello again I have a small problem here can someone tell me briefly
> how to do it? I am getting this error 
>
>
>   When I give ant fresh_install it gives error PostgreSQL 'pgcrypto'
>   extension installed /up to date? False (not installed) Create
>   extension pgcrypto
>   
> .
>
> I have tried to look up for solutions and they all say I have to run a
> command 
> |# Login to your ||"dspace"| |database as a superuser|
> |psql --username=postgres dspace|
> |# Enable the pgcrypto extension on ||this| |database|
> |CREATE EXTENSION pgcrypto;|
>  but where I got confused is from where do I run these commands.is
>  it from cmd? or in pgadmin query tool? I need
> clearly articulated  steps
>

Re: List of objects owned by a schema/owner

[Tiffany Thang]

Thanks Tom for giving me the lead. I’ll take a look.


On Fri, Aug 3, 2018 at 10:52 AM Tom Lane  wrote:

> Tiffany Thang  writes:
> > Does anyone have a query that will list all the objects (composite types,
> > sequences, tables, triggers, functions, indices, etc) owned by a schema
> or
> > owner? I find fragments of information here and there that query on
> > pg_tables, pg_views, etc.
>
> Well, you could attack it the hard way:
>
>   select relname from pg_class where relowner = [oid of role of interest]
>   union all
>   ... similar select from every other catalog that has an owner column ...
>
> I don't know if anyone's built such a query before, but a little quality
> time with the system catalog documentation would get you there:
> https://www.postgresql.org/docs/current/static/catalogs.html
>
> Another idea is to rely on owner dependencies recorded in pg_shdepend,
> along the lines of
>
> select pg_describe_object(classid,objid,objsubid)
> from pg_shdepend where deptype = 'o' and
>   refobjid = [oid of role of interest] and
>   dbid = [oid of current database];
>
> That won't work for objects owned by the bootstrap superuser, and
> I think there are some other omissions --- for instance, it looks
> like we only record an owner dependency for a table, not for its
> indexes.
>
> Or, if you're feeling truly lazy, you can do
>
> begin;
> drop user joe;
> -- read the error message whining about what joe owns
> rollback;
>
> That's largely a hacky way to get the same info as the pg_shdepend
> query I sketched before, since the error message is derived from
> exactly that info.
>
> You have the same three options for schemas, though the details of each
> are a bit different (in particular, schema dependencies would be found
> in pg_depend not pg_shdepend).
>
> regards, tom lane
>

Eror while dropping a user

[Ertan Küçükoğlu]

Hello,

Using PostgreSQL 9.6.9 on Linux amd64 platform.

I have two users that I cannot drop from the system. I revoked all
permissions from tables, sequences, functions. They do not own any objects.
My log lines are as following:
2018-08-03 23:24:03.897There are 2 users that needs to be removed from
system.
2018-08-03 23:24:03.897Removing user previliges on tables: pars.test
2018-08-03 23:24:03.899Removing user previliges on sequences: pars.test
2018-08-03 23:24:03.900Removing user previliges on functions: pars.test
2018-08-03 23:24:03.900Dropping user itself: pars.test
2018-08-03 23:24:03.901Drop user failed: SQL Error: ERROR:  role
"pars.test" cannot be dropped because some objects depend on it
AYRINTI:  1 object in database postgres
2018-08-03 23:24:03.901Removing user previliges on tables: pars.test2
2018-08-03 23:24:03.902Removing user previliges on sequences: pars.test2
2018-08-03 23:24:03.903Removing user previliges on functions: pars.test2
2018-08-03 23:24:03.903Dropping user itself: pars.test2
2018-08-03 23:24:03.904Drop user failed: SQL Error: ERROR:  role
"pars.test2" cannot be dropped because some objects depend on it
AYRINTI:  1 object in database postgres

I could not find any reference on postgres database for these users. I am
not very good on database administration.

Any help is appreciated.

Thanks & regards,
Ertan

RE: How to prevent "no wait lock" after a connection drop

[Ertan Küçükoğlu]

> -Original Message-
> From: Tom Lane  
> Sent: Wednesday, August 1, 2018 1:34 AM
> To: Ertan Küçükoğlu 
> Cc: pgsql-gene...@postgresql.org
> Subject: Re: How to prevent "no wait lock" after a connection drop
>
> =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= 
writes:
> > What I observe during my tests is that if I intentionally drop 
> > internet connection during any stage of data transfer (that is mostly 
> > while inserting to tables) application gives error and stop. For next 
> > sync operation (which runs every 5 mins) gets "no wait lock" error and 
> > exit without doing anything. That lock stage roughly stays for 1-2 hours
or more.
>
> This probably corresponds to the TCP timeout needed for the server's
kernel to decide the connection is lost; until then, the > backend session
will just sit there waiting for more data, and it'll be holding whatever
locks it had too.
>
> You could adjust the server's tcp_keepalives_xxx settings to make it
notice the connection drop more quickly.
>
>   regards, tom lane

Hello,

Server TCP timeout parameters set to identify a dropped connection in 3.5
minutes max. I am waiting for test results.

Thanks & regards,
Ertan

Re: Pg_rewind cannot load history wal

[Michael Paquier]

On Wed, Aug 01, 2018 at 09:09:30PM +, Richard Schmidt wrote:
> Our procedure that runs on machine A and B is as follows:
> 
>   1.  Build new databases on A and B, and configure A as Primary and B
>   as Standby databases. 
>   2.  Make some changes to the A (the primary) and check that they are
>   replicated to the B (the standby) 
>   3.  Promote B to be the new primary
>   4.  Switch of the A (the original primary)
>   5.  Add the replication slot to B (the new primary) for A (soon to
>   be standby)
>   6.  Add a recovery.conf to A (soon to be standby). File contains
>   recovery_target_timeline = 'latest' and restore_command = 'cp
>   /ice-dev/wal_archive/%f "%p" 
>   7.  Run pg_rewind on A - this appears to work as it returns the
>   message 'source and target cluster are on the same timeline no
>   rewind required'; 
>   8.  Start up server A (now a slave)

Step 7 is incorrect here, after promotion of B you should see pg_rewind
actually do its work.  The problem is that you are missing a piece in
your flow in the shape of a checkpoint on the promoted standby to run
after 3 and before step 7.  This makes the promoted standby update its
timeline number in the on-disk control file, which is used by pg_rewind
to check if a rewind needs to happen or not.

We see too many reports of such mistakes, I am going to propose a patch
on the -hackers mailing list to mention that in the documentation...
--
Michael


signature.asc
Description: PGP signature

Re: Eror while dropping a user

[Alessandro Aste]

You can run this query to itendify the relations owned by the users you're
not allowed to drop, just replace ('',
'username2' ..'userN' ) with the your role names . Then, once you have
identified the tables/objecst   change the owner like this:

ALTER TABLE  OWNER TO  ;

and try to drop the user again.



SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v'
THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN
'f' THEN 'foreign table' END as "Type",
pg_catalog.array_to_string(c.relacl, E'\n') AS
"Access privileges",
pg_catalog.array_to_string(ARRAY(
SELECT attname ||
E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')
FROM
pg_catalog.pg_attribute a
WHERE attrelid =
c.oid AND NOT attisdropped AND attacl IS NOT NULL
), E'\n') AS
"Column privileges",
  pg_catalog.array_to_string(ARRAY(
SELECT polname
|| CASE WHEN polcmd != '*' THEN
   E' (' || polcmd || E'):'
   ELSE E':'
   END
|| CASE WHEN polqual IS NOT NULL THEN
   E'\n  (u): ' ||
pg_catalog.pg_get_expr(polqual, polrelid)
   ELSE E''
   END
|| CASE WHEN polwithcheck IS NOT NULL THEN
   E'\n  (c): ' ||
pg_catalog.pg_get_expr(polwithcheck, polrelid)
   ELSE E''
   END|| CASE WHEN polroles <> '{0}'
THEN
   E'\n  to: ' ||
pg_catalog.array_to_string(
   ARRAY(
   SELECT rolname
   FROM pg_catalog.pg_roles
   WHERE oid = ANY (polroles)
   ORDER BY 1
   ), E', ')
   ELSE E''
   END
FROM pg_catalog.pg_policy pol
WHERE polrelid = c.oid), E'\n')
AS "Policies", ower_user.usename as "Object
Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace
LEFT JOIN pg_catalog.pg_user ower_user on
(c.relowner = ower_user.usesysid)
WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')
  AND n.nspname !~ '^pg_' AND
pg_catalog.pg_table_is_visible(c.oid)
  AND ower_user.usename in ('', 'username2');

On Fri, Aug 3, 2018 at 10:41 PM, Ertan Küçükoğlu <
ertan.kucuko...@1nar.com.tr> wrote:

> Hello,
>
> Using PostgreSQL 9.6.9 on Linux amd64 platform.
>
> I have two users that I cannot drop from the system. I revoked all
> permissions from tables, sequences, functions. They do not own any objects.
> My log lines are as following:
> 2018-08-03 23:24:03.897There are 2 users that needs to be removed from
> system.
> 2018-08-03 23:24:03.897Removing user previliges on tables: pars.test
> 2018-08-03 23:24:03.899Removing user previliges on sequences: pars.test
> 2018-08-03 23:24:03.900Removing user previliges on functions: pars.test
> 2018-08-03 23:24:03.900Dropping user itself: pars.test
> 2018-08-03 23:24:03.901Drop user failed: SQL Error: ERROR:  role
> "pars.test" cannot be dropped because some objects depend on it
> AYRINTI:  1 object in database postgres
> 2018-08-03 23:24:03.901Removing user previliges on tables: pars.test2
> 2018-08-03 23:24:03.902Removing user previliges on sequences:
> pars.test2
> 2018-08-03 23:24:03.903Removing user previliges on functions:
> pars.test2
> 2018-08-03 23:24:03.903Dropping user itself: pars.test2
> 2018-08-03 23:24:03.904Drop user failed: SQL Error: ERROR:  role
> "pars.test2" cannot be dropped because some objects depend on it
> AYRINTI:  1 object in database postgres
>
> I could not find any reference on postgres database for these users. I am
> not very good on database administration.
>
> Any help is appreciated.
>
> Thanks & regards,
> Ertan
>
>
>
>
>
>

Re: Sv: WAL-dir filling up with wal_level = logical

[Michael Paquier]

On Fri, Aug 03, 2018 at 12:40:16PM +0200, Andreas Kretschmer wrote:
> On 3 August 2018 12:33:26 CEST, Andreas Joseph Krogh  
> wrote:
>>Hm, a co-worker noticed that we have max_wal_size = 16GB, that might be
>>the 
>>reason:-)
> 
> Yes ;-)

Worth mentioning that this is a soft size, and not a hard size, hence
depending on your worload you may see more WAL segments than what is set
in 16GB.  The docs mention that, so no need to be surprised.
--
Michael


signature.asc
Description: PGP signature

RE: Eror while dropping a user

[Ertan Küçükoğlu]

> From: Alessandro Aste  
> Sent: Saturday, August 4, 2018 12:02 AM
> To: Ertan Küçükoğlu 
> Cc: pgsql-gene...@postgresql.org
> Subject: Re: Eror while dropping a user
>
> You can run this query to itendify the relations owned by the users you're 
> not allowed to drop, 
> just replace ('', 'username2' ..'userN' ) with 
> the your role names . 
> Then, once you have identified the tables/objecst   change the owner like 
> this: 

I tried to run it on "postgres" database and "pars" database, schemas on pars 
database, etc. All return empty results.

Thanks anyway.

Regards,
Ertan

RE: Eror while dropping a user

[Ertan Küçükoğlu]

> -Original Message-
> From: Ertan Küçükoğlu  
> Sent: Friday, August 3, 2018 11:42 PM
> To: pgsql-gene...@postgresql.org
> Subject: Eror while dropping a user
>
> Hello,
>
> Using PostgreSQL 9.6.9 on Linux amd64 platform.
>
> I have two users that I cannot drop from the system. I revoked all
permissions from tables, sequences, functions. They do not own any objects.

I found a table named "data" in postres database's public schema. Owner of
that table is postgres user. That table do not have any relation with these
users that I failed to drop. It is probably a mistakenly created test table.
After I drop that table, these users dropped without any error.

That is something weird to me. I am almost 100% sure that I did not give any
GRANT for these users out of "pars" database. Alessandro's provided scipt
did not return that table when I run it on postgres database and
postgres.public schema.

Anyway, I could clean up my users in the end.

Thanks & regards,
Ertan

Re: Eror while dropping a user

[Tom Lane]

=?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?=  writes:
> Using PostgreSQL 9.6.9 on Linux amd64 platform.

> 2018-08-03 23:24:03.901Drop user failed: SQL Error: ERROR:  role
> "pars.test" cannot be dropped because some objects depend on it
> AYRINTI:  1 object in database postgres

I see you've resolved your problem, but for future reference, note that
you could have gotten a more detailed error message if you'd issued the
DROP USER while connected to the postgres database.  When the command
is issued in database A, we can't see the details of what the user owns
in database B, only that there is something over there :-(

FWIW, I'm guessing that the issue was not object ownership per se,
but permissions granted on some object owned by someone else.  Those
have to be revoked as well before a DROP USER will succeed.

You might also care to read up on DROP OWNED BY.

regards, tom lane

Re: Pg_rewind cannot load history wal

[Simon Riggs]

On 3 August 2018 at 21:59, Michael Paquier  wrote:
> On Wed, Aug 01, 2018 at 09:09:30PM +, Richard Schmidt wrote:
>> Our procedure that runs on machine A and B is as follows:
>>
>>   1.  Build new databases on A and B, and configure A as Primary and B
>>   as Standby databases.
>>   2.  Make some changes to the A (the primary) and check that they are
>>   replicated to the B (the standby)
>>   3.  Promote B to be the new primary
>>   4.  Switch of the A (the original primary)
>>   5.  Add the replication slot to B (the new primary) for A (soon to
>>   be standby)
>>   6.  Add a recovery.conf to A (soon to be standby). File contains
>>   recovery_target_timeline = 'latest' and restore_command = 'cp
>>   /ice-dev/wal_archive/%f "%p"
>>   7.  Run pg_rewind on A - this appears to work as it returns the
>>   message 'source and target cluster are on the same timeline no
>>   rewind required';
>>   8.  Start up server A (now a slave)
>
> Step 7 is incorrect here, after promotion of B you should see pg_rewind
> actually do its work.  The problem is that you are missing a piece in
> your flow in the shape of a checkpoint on the promoted standby to run
> after 3 and before step 7.  This makes the promoted standby update its
> timeline number in the on-disk control file, which is used by pg_rewind
> to check if a rewind needs to happen or not.
>
> We see too many reports of such mistakes, I am going to propose a patch
> on the -hackers mailing list to mention that in the documentation...

I think the problem is that writing the online checkpoint is deferred
after promotion, so this is a timing issue that probably doesn't show
in our regression tests.

Sounds like we should write a pending timeline change to the control
file and have pg_rewind check that instead.

I'd call this a timing bug, not a doc issue.

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Pg_rewind cannot load history wal

[Michael Paquier]

On Sat, Aug 04, 2018 at 07:44:59AM +0100, Simon Riggs wrote:
> I think the problem is that writing the online checkpoint is deferred
> after promotion, so this is a timing issue that probably doesn't show
> in our regression tests.

Somewhat.  It is a performance improvement of 9.3 to let the startup
request a checkpoint to the checkpointer process instead of doing it
itself.

> Sounds like we should write a pending timeline change to the control
> file and have pg_rewind check that instead.
> 
> I'd call this a timing bug, not a doc issue.

Well, having pg_rewind enforce a checkpoint on the promoted standby
could cause a performance hit as well if we do it mandatorily as if
there is delay between the promotion and the rewind triggerring a
checkpoint could have already happen.  So it is for me a documentation
bug first regarding the failover workflow, and potentially a patch for a
new feature which makes pg_rewind trigger directly a checkpoint.
--
Michael


signature.asc
Description: PGP signature