Обсуждение: A real puzzler: ANY way to recover?
Hi, Supposing someone stupidly did this: UPDATE pg_database SET datallowconn = false; and then closed all the connections to the server. Is there any way to recover short of nuking everything and restoring from a backup dump? :-( Regards, David.
"David F. Skoll" <dfs@roaringpenguin.com> writes: > Supposing someone stupidly did this: > UPDATE pg_database SET datallowconn = false; > and then closed all the connections to the server. > Is there any way to recover short of nuking everything and > restoring from a backup dump? :-( That would be a bad move. I wonder whether we should allow a standalone backend (postgres) to connect regardless of datallowconn. The tradeoff is that you could break your template0 slightly more easily, but you can break template0 anyway if you are determined. If you have actually done that :-(, my recommendation would be to build a modified backend with the check in src/backend/utils/init/postinit.c diked out ... regards, tom lane
while you weren't looking, David F. Skoll wrote: > Is there any way to recover short of nuking everything and > restoring from a backup dump? :-( I don't have any ability to test this and see if it actually works, but a priori, I'd suggest trying, as your postgres user: $ createdb oopswow $ psql oopswow oopswow=# update pg_database set datallowconn = true; -- with optional WHERE clause oopswow=# \q That should work. Unless the new database takes its datallowconn attribute from the template database, which will have been set to false by the errant UPDATE -- in which case, I've no idea. /rls -- :wq
On Thu, May 05, 2005 at 01:14:26PM -0400, David F. Skoll wrote: > Hi, > > Supposing someone stupidly did this: > > UPDATE pg_database SET datallowconn = false; > > and then closed all the connections to the server. > > Is there any way to recover short of nuking everything and > restoring from a backup dump? :-( Connect in standalone mode? -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Ellos andaban todos desnudos como su madre los parió, y también las mujeres, aunque no vi más que una, harto moza, y todos los que yo vi eran todos mancebos, que ninguno vi de edad de más de XXX años" (Cristóbal Colón)
Alvaro Herrera wrote: > Connect in standalone mode? Does not work. Tom Lane replied with the only thing that does work, which we independently discovered about 30 seconds before hearing from Tom. :-) The solution is to modify the PostgreSQL source code to skip the check, and run the modified binary in standalone mode just to reset the datallowconn flag to true. Once we did that, we quit and started the normal server to complete the maintenance work. I recommend having a postgres single-user-mode command-line option to disable the check, with a suitably stern warning in the man page not to use it. :-) Regards, David.
Rosser Schwarz <rosser.schwarz@gmail.com> writes: > but a priori, I'd suggest trying, as your postgres user: > $ createdb oopswow Doesn't work because createdb has to connect to something (typically template1). If he's closed off *all* his databases, he's in deep doo-doo. This is reminiscent of the problem pointed out some time ago "what do you do if you deleted all your superusers"? We fixed that by allowing a standalone backend to connect (and to believe itself to be a superuser) no matter what is in ... or not in ... pg_shadow. I'm inclined to think the same answer should hold for this one. regards, tom lane
"David F. Skoll" <dfs@roaringpenguin.com> writes: > Supposing someone stupidly did this: > UPDATE pg_database SET datallowconn = false; > and then closed all the connections to the server. > Is there any way to recover short of nuking everything and > restoring from a backup dump? :-( BTW, am I right in guessing that you got into this fix by sloppy application of the directions for fixing template0 for the recent security issues? Although I dislike last-minute fixes, I am strongly tempted to put in the ignore-datallowconn-if-standalone change in all the releases we are about to make. It'd be a one-liner that is very unlikely to break anything, and I have this nasty feeling that you may not be the only guys to make this mistake. regards, tom lane
Tom Lane wrote: > Although I dislike last-minute fixes, I am strongly tempted to put in > the ignore-datallowconn-if-standalone change in all the releases we > are about to make. That sounds very reasonable. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes: > Tom Lane wrote: >> Although I dislike last-minute fixes, I am strongly tempted to put in >> the ignore-datallowconn-if-standalone change in all the releases we >> are about to make. > That sounds very reasonable. Done. regards, tom lane
Peter Eisentraut wrote: > Tom Lane wrote: > >>Although I dislike last-minute fixes, I am strongly tempted to put in >>the ignore-datallowconn-if-standalone change in all the releases we >>are about to make. I'm curious as to what the purpose of such a setting might be (datallowconn = false)? Any functional use? (other than a tough learning experience?) -- Until later, Geoffrey
David F. Skoll wrote: >Alvaro Herrera wrote: > > > >>Connect in standalone mode? >> >> > >Does not work. Tom Lane replied with the only thing that does work, >which we independently discovered about 30 seconds before hearing from >Tom. :-) > >The solution is to modify the PostgreSQL source code to skip the check, >and run the modified binary in standalone mode just to reset the >datallowconn flag to true. Once we did that, we quit and started >the normal server to complete the maintenance work. > >I recommend having a postgres single-user-mode command-line option >to disable the check, with a suitably stern warning in the man >page not to use it. :-) > >Regards, > >David. > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend > > > >