On Thu, Feb 18, 2016 at 5:15 PM, Rakesh Kumar <dcrunch...@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 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 <melvin6...@gmail.com> > To: Rakesh Kumar <dcrunch...@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. > >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.
flip_database_connect.sh
Description: Bourne shell script
cancel_all_queries.sh
Description: Bourne shell script
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
