Thread: Way to shutdown/freeze/stop an individual database without taking postmaster down?

Greetings,

I was asked this question, and I wasn't sure if it is possible:


do you know of a way to stop just one database (not delete/drop) on
our PostgreSQL 8.1 server?


And, while I know how to shut down postmaster, and/or put in rules to
pg_hba.conf to limit access to a certain database, is there any way to
freeze or stop just one database and not others?  I'm attempting to
RTFM it, but so far I've had no luck.

Thanks,
Will

What do you hope to get out of this that you wouldn't get out of locking
out access?

On Thu, 10 Apr 2008, W S wrote:

> Greetings,
>
> I was asked this question, and I wasn't sure if it is possible:
>
>
> do you know of a way to stop just one database (not delete/drop) on
> our PostgreSQL 8.1 server?
>
>
> And, while I know how to shut down postmaster, and/or put in rules to
> pg_hba.conf to limit access to a certain database, is there any way to
> freeze or stop just one database and not others?  I'm attempting to
> RTFM it, but so far I've had no luck.
>
> Thanks,
> Will
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

W S wrote:
> Greetings,
>
> I was asked this question, and I wasn't sure if it is possible:
>
>
> do you know of a way to stop just one database (not delete/drop) on
> our PostgreSQL 8.1 server?

One possible way:

You can update its pg_database record, setting datallowconn to 'f' to
disable new connections to the DB, then boot off all existing users of
it by killing their backends. A discussion about this appeared here
recently, and a search of the archives for `datallowconn' should find it.

> And, while I know how to shut down postmaster, and/or put in rules to
> pg_hba.conf to limit access to a certain database, is there any way to
> freeze or stop just one database and not others?  I'm attempting to
> RTFM it, but so far I've had no luck.

I haven't checked whether pg_hba.conf rules are reloaded, but `pg_ctl
reload' can re-read some settings without a postmaster restart. So you
might be able to disallow access in pg_hba then reload.

I'd be curious to know if that works and if/how it affects existing
connections - though I can always test it myself.

--
Craig Ringer

On Apr 10, 2008, at 2:37 PM, Craig Ringer wrote:
> I haven't checked whether pg_hba.conf rules are reloaded, but `pg_ctl
> reload' can re-read some settings without a postmaster restart. So you
> might be able to disallow access in pg_hba then reload.
>
> I'd be curious to know if that works and if/how it affects existing
> connections - though I can always test it myself.

Yes, a pg_ctl reload will reload pg_hba.conf and, iirc, it does not
kill any existing connections as the values in that file are checked
at time of connection, not time of config load.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

ws3reg@gmail.com ("W S") writes:
> I was asked this question, and I wasn't sure if it is possible:
>
>
> do you know of a way to stop just one database (not delete/drop) on
> our PostgreSQL 8.1 server?
>
>
> And, while I know how to shut down postmaster, and/or put in rules to
> pg_hba.conf to limit access to a certain database, is there any way to
> freeze or stop just one database and not others?  I'm attempting to
> RTFM it, but so far I've had no luck.

You could put in rules to pg_hba.conf to shut users out of a
particular database, and then run "pg_ctl reload" to signal the
postmaster to start applying the new rules.

That will prevent any new connections from coming into the database in
question.

But does not get rid of existing connections.  You could kill the
backends associated with the existing connections...  You can query
pg_catalog.pg_stat_activity to find the relevant list.
--
let name="cbbrowne" and tld="linuxdatabases.info" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/advocacy.html
Whatever is  contradictory or paradoxical is called  the  back of God.
His face, where all exists in perfect harmony, cannot be seen by man.