Re: Multiple databases and shared_buffers

On Thu, Feb 18, 2016 at 5:15 PM, Rakesh Kumar <dcruncher4(at)aim(dot)com> 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.
>
> -----Original Message-----
> From: Melvin Davidson <melvin6925(at)gmail(dot)com>
> To: Rakesh Kumar <dcruncher4(at)aim(dot)com>; pgsql-general <
> pgsql-general(at)postgresql(dot)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.
>

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

You are getting off topic, but no, you cannot "shutdown" just one database.
However, you can use the attached flip_database_connect.sh to temporarily
prevent connections to a specific database.
and then reallow.
You can also use cancel_all_queries.sh to cancel ALL current queries, but
that is for all users except the superuser running it.

FYI, revoking a grant will not cancel current queries, only prevent future
access.

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.

https://www.packtpub.com/big-data-and-business-intelligence/postgresql-9-administration-cookbook-second-edition/?utm_source=PoD&utm_medium=referral&utm_campaign=1849519064

You will also find many other useful books below

http://www.postgresql.org/docs/books/
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.