Thread: Taking database offline

Taking database offline

From
ow
Date:
Hi,

Is there a way to take the database offline to keep users out?
Is there a way to take the database offline but still allowing pg_restore to
run against it?

Thanks






__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: Taking database offline

From
Bruno Wolff III
Date:
On Wed, Nov 12, 2003 at 12:07:23 -0800,
  ow <oneway_111@yahoo.com> wrote:
> Hi,
>
> Is there a way to take the database offline to keep users out?
> Is there a way to take the database offline but still allowing pg_restore to
> run against it?

You could shut the database down, change pg_hba.conf to only let in
the postgres user, bring the system back up. Once you finish your restore
you can repeat the procedure, this time change pg_hba.conf to let everyone
back in.

Re: Taking database offline

From
"scott.marlowe"
Date:
On Wed, 12 Nov 2003, ow wrote:

> Hi,
>
> Is there a way to take the database offline to keep users out?
> Is there a way to take the database offline but still allowing pg_restore to
> run against it?

Yes, as of 7.3 you can create entries in pg_hba.conf that control who can
connect to what database.  Just make a backup of the original pg_hba.conf,
and set up your copy to have only the postgres (or other) superuser be
allowed to connect.  Then, swap the two hba files and restart the server.




Re: Taking database offline

From
Bruce Momjian
Date:
scott.marlowe wrote:
> On Wed, 12 Nov 2003, ow wrote:
>
> > Hi,
> >
> > Is there a way to take the database offline to keep users out?
> > Is there a way to take the database offline but still allowing pg_restore to
> > run against it?
>
> Yes, as of 7.3 you can create entries in pg_hba.conf that control who can
> connect to what database.  Just make a backup of the original pg_hba.conf,
> and set up your copy to have only the postgres (or other) superuser be
> allowed to connect.  Then, swap the two hba files and restart the server.

Rather than restart the server (kicking everyone out), does 'reload'
help to keep new users out and let the old ones leave on their own?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Taking database offline

From
ow
Date:
--- Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>
> Rather than restart the server (kicking everyone out), does 'reload'
> help to keep new users out and let the old ones leave on their own?
>

Not really ... users (old or new) should not work with the db unless it's
completely loaded. Restarting the server will affect activity in other dbs in
that cluster which is not desirable :(  IMHO, there should be a way to do this
without restarting the server.

Thanks





__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: Taking database offline

From
Oli Sennhauser
Date:
Hello

>Not really ... users (old or new) should not work with the db unless it's
>completely loaded. Restarting the server will affect activity in other dbs in
>that cluster which is not desirable :(  IMHO, there should be a way to do this
>without restarting the server.
>
>
What about having two database clusters on a server for separating HA
from "not so HA" databases? You can take down one db cluster. And people
can still work on the other one (DB Cluster does nothing has to do with
hardware or os cluster).

I know it is not so simple. I worked out a concept for this and need
some alpha-Testers?

Regards Oli

-------------------------------------------------------

Oli Sennhauser
Database-Engineer (Oracle & PostgreSQL)
Rebenweg 6
CH - 8610 Uster / Switzerland

Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14
e-Mail oli.sennhauser@bluewin.ch
Website http://mypage.bluewin.ch/shinguz/PostgreSQL/

Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch

Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import


Attachment

Re: Taking database offline

From
ow
Date:
--- Oli Sennhauser <oli.sennhauser@bluewin.ch> wrote:
> What about having two database clusters on a server for separating HA
> from "not so HA" databases? You can take down one db cluster. And people
> can still work on the other one (DB Cluster does nothing has to do with
> hardware or os cluster).

There's got to be a simpler solution than this. What about the file space? E.g.
a large db with 000s of GBs. How would one even keep those db clusters in sync?
Maybe I'm missing something ...

Thanks






__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: Taking database offline

From
Bruce Momjian
Date:
ow wrote:
>
> --- Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> >
> > Rather than restart the server (kicking everyone out), does 'reload'
> > help to keep new users out and let the old ones leave on their own?
> >
>
> Not really ... users (old or new) should not work with the db unless it's
> completely loaded. Restarting the server will affect activity in other dbs in
> that cluster which is not desirable :(  IMHO, there should be a way to do this
> without restarting the server.

You can restrict pg_hba.conf to not allow connections just to one
database if you want.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Taking database offline

From
ow
Date:
--- Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> You can restrict pg_hba.conf to not allow connections just to one
> database if you want.
>

True, but that still would not prevent users who are already connected from
accessing the db, would it?

Thanks






__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: Taking database offline

From
ow
Date:
--- Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> No, I guess that is the problem.  You can use restart, which will kick
> everyone out, or you can use reload, but that doesn't kick people out of
> the single database you want.
>

Restarting the db server triggers the whole chain of events: other dbs are
affected, app servers will also need to restarted, and that has to be done
twice (change pg_hba.conf and then reverse). There's got to be a better way.

Thanks






__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: Taking database offline

From
Bruce Momjian
Date:
ow wrote:
>
> --- Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> > No, I guess that is the problem.  You can use restart, which will kick
> > everyone out, or you can use reload, but that doesn't kick people out of
> > the single database you want.
> >
>
> Restarting the db server triggers the whole chain of events: other dbs are
> affected, app servers will also need to restarted, and that has to be done
> twice (change pg_hba.conf and then reverse). There's got to be a better way.

OK, use reload, then use 'ps' to see the people connected to the
database and send a signal to the backend to for it to exit --- this is
how the postmaster does it.

Signals to use from the 7.4 postgres manual:

  <para>
   The <command>postmaster</command> uses <literal>SIGTERM</literal>
   to tell a <command>postgres</command> process to quit normally and
   <literal>SIGQUIT</literal> to terminate without the normal cleanup.
   These signals <emphasis>should not</emphasis> be used by users.  It is also
   unwise to send <literal>SIGKILL</literal> to a <command>postgres</command>
   process --- the <command>postmaster</command> will interpret this as
   a crash in <command>postgres</command>, and will force all the sibling
   <command>postgres</command> processes to quit as part of its standard
   crash-recovery procedure.
  </para>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Taking database offline

From
Bruce Momjian
Date:
ow wrote:
>
> --- Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> > You can restrict pg_hba.conf to not allow connections just to one
> > database if you want.
> >
>
> True, but that still would not prevent users who are already connected from
> accessing the db, would it?

No, I guess that is the problem.  You can use restart, which will kick
everyone out, or you can use reload, but that doesn't kick people out of
the single database you want.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073