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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@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? :-(

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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
>
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match