Re: Multiple databases and shared_buffers - Mailing list pgsql-general
On Thu, Feb 18, 2016 at 5:15 PM, Rakesh Kumar <dcruncher4@aim.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 caseof 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 waysof achieving it (like killing all sessions and revoking grant priv), but if there is an easierway to knock out a db temporarily, it will be great.-----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 sameexact 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
https://www.packtpub.com/big-data-and-business-intelligence/postgresql-9-administration-cookbook-second-edition/?utm_source=PoD&utm_medium=referral&utm_campaign=1849519064
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Attachment
pgsql-general by date: