Thread: Multiple databases and shared_buffers

We will be creating multiple databases in a cluster (instance). Is there any way to separate shared_buffers for each database? Looks like not since PG does not allow user created shared buffers.

-- 
john r pierce, recycling bits in santa cruz

NOTE: It is very important you provide the version of PostgreSQL and O/S when addressing this mail list. 

Since it is a new project, we are going with:

PG: 9.5.1

OS: RHEL 6.4

-- 
john r pierce, recycling bits in santa cruz

On 02/18/2016 02:15 PM, Rakesh Kumar wrote:
> aha ok it is clear now.
>
> The splitting of buffers for each db is not for the reasons you stated
> below, but for better management of RAM.
> In our current RDBMS we allocate BPs for each database based on its
> usage/size.  With that being said, in case
> of PG. having no control on BP is not a big deal.
>
> Also, just curious , is it possible to shut down only one database in an
> instance,
> in case a rogue session connected to it is causing havoc. I know there
> are other ways
> of achieving it (like killing all sessions and revoking grant priv), but
> if there is an easier
> way to knock out a db temporarily, it will be great.

In 9.5:

http://www.postgresql.org/docs/9.5/interactive/sql-alterdatabase.html

ALTER DATABASE name [ [ WITH ] option [ ... ] ]

where option can be:

     ALLOW_CONNECTIONS allowconn

So:

postgres=# alter database test allow_connections false;

postgres=# \c test
FATAL:  database "test" is not currently accepting connections
FATAL:  database "test" is not currently accepting connections
Previous connection kept


It will not shutdown existing sessions, but you can use the functions
below to do that:

http://www.postgresql.org/docs/9.5/interactive/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL

>
> -----Original Message-----
> From: Melvin Davidson <melvin6925@gmail.com>
> To: Rakesh Kumar <dcruncher4@aim.com>; pgsql-general
> <pgsql-general@postgresql.org>
> Sent: Thu, Feb 18, 2016 4:12 pm
> Subject: Re: [GENERAL] Multiple databases and shared_buffers
>
>  >What do you mean that the data is actually not stored in the shared
> buffer.
>From the link you provided :
>>"The “shared_buffers” configuration parameter determines how much memory
>>is dedicated to PostgreSQL to use for caching data."
>
> Again, you misunderstand. Cached data (and queries) is for sharing only
> to the same database.
> So if user A in Database abc does a "SELECT some_column FROM table1
> WHERE col2 = 4" Then the results/data from that query are available to
> any other user (who has permission) in database abc and does the same
> exact query. However, users from database xyz CANNOT see data and/or
> results from database abc unless they specifically connect to it.
> Further to the point, Beginning with 9.4, PostgreSQL also makes better
> use of O/S memory for shared_buffers. But the bottom line is, you do not
> need to split shared_buffers up among different databases. PostgreSQL
> just uses it to make queries more efficient. There is no security
> problem because users in one database cannot request buffer information
> about another.
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.


--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Feb 18, 2016 at 05:38:45PM -0500, Melvin Davidson wrote:

> From your questions, it appears you are a little weak on PostgreSQL
> Database Administration. I respectfully suggest you obtain a copy of the
> following book to get a clearer understandings of how things work.

The PostgreSQL documentation actually is a great read, too.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346