pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedA real puzzler: ANY way to recover?
10+ messages / 6 participants
[nested] [flat]
* A real puzzler: ANY way to recover?
@ 2005-05-05 17:14 David F. Skoll <dfs@roaringpenguin.com>
0 siblings, 4 replies; 10+ messages in thread
From: David F. Skoll @ 2005-05-05 17:14 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: A real puzzler: ANY way to recover?
@ 2005-05-05 17:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: David F. Skoll <dfs@roaringpenguin.com>
3 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2005-05-05 17:34 UTC (permalink / raw)
To: David F. Skoll <dfs@roaringpenguin.com>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: A real puzzler: ANY way to recover?
@ 2005-05-05 17:37 Rosser Schwarz <rosser.schwarz@gmail.com>
parent: David F. Skoll <dfs@roaringpenguin.com>
3 siblings, 1 reply; 10+ messages in thread
From: Rosser Schwarz @ 2005-05-05 17:37 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: A real puzzler: ANY way to recover?
@ 2005-05-05 17:49 Alvaro Herrera <alvherre@dcc.uchile.cl>
parent: David F. Skoll <dfs@roaringpenguin.com>
3 siblings, 1 reply; 10+ messages in thread
From: Alvaro Herrera @ 2005-05-05 17:49 UTC (permalink / raw)
To: David F. Skoll <dfs@roaringpenguin.com>; +Cc: pgsql-admin
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: A real puzzler: ANY way to recover?
@ 2005-05-05 18:06 David F. Skoll <dfs@roaringpenguin.com>
parent: Alvaro Herrera <alvherre@dcc.uchile.cl>
0 siblings, 0 replies; 10+ messages in thread
From: David F. Skoll @ 2005-05-05 18:06 UTC (permalink / raw)
To: pgsql-admin; +Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: A real puzzler: ANY way to recover?
@ 2005-05-05 18:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rosser Schwarz <rosser.schwarz@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2005-05-05 18:14 UTC (permalink / raw)
To: Rosser Schwarz <rosser.schwarz@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: A real puzzler: ANY way to recover?
@ 2005-05-05 18:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: David F. Skoll <dfs@roaringpenguin.com>
3 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2005-05-05 18:19 UTC (permalink / raw)
To: David F. Skoll <dfs@roaringpenguin.com>; +Cc: pgsql-admin; pgsql-hackers@postgresql.org
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] A real puzzler: ANY way to recover?
@ 2005-05-05 19:13 Peter Eisentraut <peter_e@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 10+ messages in thread
From: Peter Eisentraut @ 2005-05-05 19:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David F. Skoll <dfs@roaringpenguin.com>; pgsql-admin; pgsql-hackers@postgresql.org
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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] A real puzzler: ANY way to recover?
@ 2005-05-05 19:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
1 sibling, 0 replies; 10+ messages in thread
From: Tom Lane @ 2005-05-05 19:56 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: David F. Skoll <dfs@roaringpenguin.com>; pgsql-admin; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] A real puzzler: ANY way to recover?
@ 2005-05-06 11:28 Geoffrey <esoteric@3times25.net>
parent: Peter Eisentraut <peter_e@gmx.net>
1 sibling, 0 replies; 10+ messages in thread
From: Geoffrey @ 2005-05-06 11:28 UTC (permalink / raw)
To: pgsql-admin; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2005-05-06 11:28 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-05 17:14 A real puzzler: ANY way to recover? David F. Skoll <dfs@roaringpenguin.com>
2005-05-05 17:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-05 17:37 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2005-05-05 18:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-05 17:49 ` Alvaro Herrera <alvherre@dcc.uchile.cl>
2005-05-05 18:06 ` David F. Skoll <dfs@roaringpenguin.com>
2005-05-05 18:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-05 19:13 ` Peter Eisentraut <peter_e@gmx.net>
2005-05-05 19:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-06 11:28 ` Geoffrey <esoteric@3times25.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox