PostgreSQL Administration - Unable to connect to a specific database

Author

Unable to connect to a specific database

Sally Sally

2006-01-13, 8:24 pm

I had to kill a vacuum in the middle with -9. I shut down and restarted the
postgres server several times after that but I am unable to connect to the
db that I was initially running vacuum on
I'm doing "psql dbname" and it hangs for a while. I'm still waiting. Any
ideas?
Thanks

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

http://archives.postgresql.org

Christopher Browne

2006-01-14, 3:23 am

> I had to kill a vacuum in the middle with -9. I shut down and
> restarted the postgres server several times after that but I am unable
> to connect to the db that I was initially running vacuum on
> I'm doing "psql dbname" and it hangs for a while. I'm still
> waiting. Any ideas?

"Kill -9" is distinctly not recommended.

It is possible that you have corrupted the database by issuing "kill
-9."

You might want to look into what backups you have...
--
wm(X,Y):- write(X),write('@'),
write(Y). wm('cbbrowne','gmail
.com').
http://linuxdatabases.info/info/nonrdbms.html
"Very funny, Scotty. Now beam down my clothes."

Andrew Sullivan

2006-01-16, 1:23 pm

On Fri, Jan 13, 2006 at 10:20:07PM +0000, Sally Sally wrote:
> I had to kill a vacuum in the middle with -9. I shut down and restarted the
> postgres server several times after that but I am unable to connect to the
> db that I was initially running vacuum on
> I'm doing "psql dbname" and it hangs for a while. I'm still waiting. Any
> ideas?

What do your database logs say? I expect that you're in recovery
mode, but it's impossible to tell from this note.

A

--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

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

Andrew Sullivan

2006-01-16, 1:23 pm

On Fri, Jan 13, 2006 at 09:19:30PM -0500, Christopher Browne wrote:
> It is possible that you have corrupted the database by issuing "kill
> -9."

If that's true, then WAL doesn't work. kill -9 shouldn't be any more
harmful than a machine crash. That is to say, it's very bad news,
but it shouldn't actually corrupt the database.

A

--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin

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

Sally Sally

2006-01-16, 1:23 pm

Well after kill -9, it was in recovery mode but I restarted the server
again. It took a while (~ 45min) but it connected again. I have noticed that
after a vacuum it takes a while to connect for the first time. You mentioned
kill -9 is very bad news. Everything seems to be working fine now, should I
be worried? What are the consequences?
Thanks

>From: Andrew Sullivan <ajs@crankycanuck.ca>
>To: pgsql-admin@postgresql.org
>Subject: Re: [ADMIN] Unable to connect to a specific database
>Date: Mon, 16 Jan 2006 12:38:09 -0500
>
>On Fri, Jan 13, 2006 at 09:19:30PM -0500, Christopher Browne wrote:
>
>If that's true, then WAL doesn't work. kill -9 shouldn't be any more
>harmful than a machine crash. That is to say, it's very bad news,
>but it shouldn't actually corrupt the database.
>
>A
>
>--
>Andrew Sullivan | ajs@crankycanuck.ca
>I remember when computers were frustrating because they *did* exactly what
>you told them to. That actually seems sort of quaint now.
> --J.D. Baldwin
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 1: 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

Tom Lane

2006-01-16, 1:23 pm

"Sally Sally" <dedeb17@hotmail.com> writes:
> Well after kill -9, it was in recovery mode but I restarted the server
> again. It took a while (~ 45min) but it connected again. I have noticed that
> after a vacuum it takes a while to connect for the first time. You mentioned
> kill -9 is very bad news. Everything seems to be working fine now, should I
> be worried? What are the consequences?

The problem with kill -9 is mainly if you use it to kill the postmaster;
then the postmaster is unable to release its shared memory segment
before quitting. Depending on how your kernel handles shared memory,
the segment might stick around indefinitely (unless manually cleaned up
or you reboot the whole machine). This will eventually run the system
out of memory and/or prevent launching a new postmaster.

kill -9 on an individual backend isn't so dangerous, but it's still
using a steam hammer to swat a fly. Try less invasive methods first,
like kill -INT (ie, query cancel).

BTW, the unreasonably long restart time suggests that you've set
unreasonably long inter-checkpoint intervals. Might want to think
about backing off the checkpoint parameters a bit.

regards, tom lane

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

Sponsored Links