Re: Multiple databases and shared_buffers

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