Re: Log files polluted with permission denied error messages after every 10 seconds

[Michael Paquier]

On Sat, Mar 06, 2021 at 07:53:11PM +0200, Andrus wrote:
> I changed wal_recycle to off. So checkpointer should no more try to rename
> wal files. Iit still tries to rename files. No idea way it does not use this
> setting:

On Windows, RemoveXlogFile() would still rename a given WAL segment
file with a ".deleted" suffix with ou without wal_recycle in the case
where the a recycling of a WAL segment is not necessary, for example
if max_wal_size is already full.  So this has no effect.

> Should chekpointer process terminated to force it to use new setting. Is it
> safe to kill it during database usage.

I don't understand what you mean here.
--
Michael


signature.asc
Description: PGP signature

Re: Log files polluted with permission denied error messages after every 10 seconds

[Andrus]

Hi!

On Windows, RemoveXlogFile() would still rename a given WAL segment

file with a ".deleted" suffix with ou without wal_recycle in the case
where the a recycling of a WAL segment is not necessary, for example
if max_wal_size is already full.  So this has no effect.


Should files with .deleted extension deleted manually to save disk space 
? May of them have dates before today.


Andrus.

Need help with clarification on stored procedure support in PostGreSQL database

[Bysani, Ram]

PostgreSQL version: 11.11

Operating system:   Linux 64 bit

Description:



Greetings



We have found posts on several forums that Stored Procedures are not supported 
in PostGreSQL databasel.

We looked at the documentation but it is not clear when Stored Procedure 
support was added. We are aware that Functions are supported.



Please provide details / clarify if Stored Procedures are available in versions 
11.x, 12.x, 13.x Please also confirm when it was actually added.





https://www.postgresql.org/docs/



Thanks for your time





Regards

Ram Bysani

Re: Need help with clarification on stored procedure support in PostGreSQL database

[David G. Johnston]

On Sun, Mar 7, 2021 at 11:49 AM Bysani, Ram  wrote:

> Please provide details / clarify if Stored Procedures are available in
> versions 11.x, 12.x, 13.x Please also confirm when it was actually added.
>
> https://www.postgresql.org/docs/
>
https://www.postgresql.org/docs/current/sql-createprocedure.html

David J.

Re: Need help with clarification on stored procedure support in PostGreSQL database

[Adrian Klaver]

On 3/7/21 10:48 AM, Bysani, Ram wrote:

PostgreSQL version: 11.11

Operating system:   Linux 64 bit

Description:

Greetings

We have found posts on several forums that Stored Procedures are not 
supported in PostGreSQL databasel.


We looked at the documentation but it is not clear when Stored Procedure 
support was added. We are aware that Functions are supported.


Please provide details / clarify if Stored Procedures are available in 
versions 11.x, 12.x, 13.x Please also confirm when it was actually added.


https://www.postgresql.org/docs/ 

Thanks for your time


Go to page David Johnson posted:

https://www.postgresql.org/docs/current/sql-createprocedure.html

and look at 'Supported Versions:' at top of page.



Regards

Ram Bysani




--
Adrian Klaver
adrian.kla...@aklaver.com

Vacuuming by non owner/super users?

[Paul McGarry]

Is it possible for a non-owner or non super user to be given permission to
vacuum tables in the DB?

My initial thought is no, but the documentation says:

"To vacuum a table, one must ordinarily be the table's owner or a
superuser."

Where the "ordinarily" seems to imply there might be some non-ordinary way
of performing a vacuum?
(If it really is just table owner or super user, then the sentence would be
clearer without the "ordinarily").

I am trying to split up DB maintenance, ie would like to give permission to
vacuum DB/tables without giving them full rights to do other stuff on the
tables.

Thanks for any advice.

Paul

Re: Log files polluted with permission denied error messages after every 10 seconds

[Michael Paquier]

On Sun, Mar 07, 2021 at 11:45:26AM +0200, Andrus wrote:
> Should files with .deleted extension deleted manually to save disk space ?
> May of them have dates before today.

RemoveOldXlogFiles() would discard any of those .deleted files because
they don't match a legal WAL segment name, so checkpoints are not able
to work on them even in the future.  I would avoid meddling with
anything that a backend may finish to touch while running, but that 
should not really matter here as they are just never chosen for
deletion.  Piling up those files is not a good thing, so while you
still need to figure out what's causing those files to remain around
on your side, perhaps we should improve the situation in the backend
itself.
--
Michael


signature.asc
Description: PGP signature

Re: Vacuuming by non owner/super users?

[Tom Lane]

Paul McGarry  writes:
> Is it possible for a non-owner or non super user to be given permission to
> vacuum tables in the DB?
> My initial thought is no, but the documentation says:
> "To vacuum a table, one must ordinarily be the table's owner or a
> superuser."

The full quote is

To vacuum a table, one must ordinarily be the table's owner or a
superuser.  However, database owners are allowed to
vacuum all tables in their databases, except shared catalogs.
(The restriction for shared catalogs means that a true database-wide
VACUUM can only be performed by a superuser.)

and as far as I can see, the permissions check in vacuum.c implements that
exactly.

regards, tom lane

Is it normal to have too many idle processes in postgresql cluster?

[Abdul Qoyyuum]

Hi all, I have a database cluster running Postgresql 9.6 on RHEL 6 servers.
I've noticed a lot of idle processes in the master database as opposed to
the slave database and from the looks of pg_activity, they're all idle
statements like "SELECT 1". Forums and StackOverflow seems to suggest its
nothing to worry about but

[u...@db1.pr ~]$ ps -ef | grep postgres | grep idle | wc -l
213

Seems to get me worried. Should I be worried? Is this normal? If not, what
should I do?
-- 
Abdul Qoyyuum Bin Haji Abdul Kadir
HP No: +673 720 8043

Re: Is it normal to have too many idle processes in postgresql cluster?

[Christophe Pettus]

> On Mar 7, 2021, at 17:34, Abdul Qoyyuum  wrote:
> Seems to get me worried. Should I be worried? Is this normal? If not, what 
> should I do?

It's normal.  Every connected client has an associated backend process, even if 
the client isn't doing anything.  That's what the "idle" state means.  You can 
consider using a pooler such as pgbouncer to reduce the number of connections 
the clients have open, although 200 open connections is nothing concerning.

--
-- Christophe Pettus
   x...@thebuild.com

Re: Is it normal to have too many idle processes in postgresql cluster?

[Abdul Qoyyuum]

Thanks Christophe! I guess I had nothing to be worried about.

On Mon, Mar 8, 2021 at 9:46 AM Christophe Pettus  wrote:

>
>
> > On Mar 7, 2021, at 17:34, Abdul Qoyyuum 
> wrote:
> > Seems to get me worried. Should I be worried? Is this normal? If not,
> what should I do?
>
> It's normal.  Every connected client has an associated backend process,
> even if the client isn't doing anything.  That's what the "idle" state
> means.  You can consider using a pooler such as pgbouncer to reduce the
> number of connections the clients have open, although 200 open connections
> is nothing concerning.
>
> --
> -- Christophe Pettus
>x...@thebuild.com
>
>

-- 
Abdul Qoyyuum Bin Haji Abdul Kadir
HP No: +673 720 8043