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@xxxxxxxxx>
To: Rakesh Kumar <dcruncher4@xxxxxxx>; pgsql-general
<pgsql-general@xxxxxxxxxxxxxx>
Sent: Thu, Feb 18, 2016 4:12 pm
Subject: Re: 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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
