Обсуждение: Taking database offline
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
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.
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.
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
--- 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
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
Вложения
--- 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
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
--- 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
--- 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
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
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