Recovery from crashed DB seems to occur progressively

From: Bruno Harbulot <Bruno(dot)Harbulot(at)manchester(dot)ac(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Recovery from crashed DB seems to occur progressively
Date: 2009-08-04 13:46:04
Message-ID: h59e2t$sig$1@ger.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I'm using version 8.3.5. The partition in which the 'pgsql' directory
was became full. This database is normally used via an Hibernate/JDBC
layer and one of the most common request is "SELECT * FROM MYTABLE LIMIT
10", from a webpage.
I tried to delete some rows manually from psql ("delete from MYTABLE
where id <= ...") and this caused PostgreSQL server to crash .

> PANIC: could not write to file "pg_xlog/xlogtemp.29275": No space left on device
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: WARNING: terminating connection because of crash of another server process
> DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
> HINT: In a moment you should be able to reconnect to the database and repeat your command.
> Failed.
> !> \q

I moved the entire directory to a larger partition and created a symlink
to it.
I used "pg_resetxlog -f" on the data directory (I had to force as it
wouldn't work otherwise). This seems to have restored the database, and
I'm now able to start the server again. However, the rows seem to come
back "progressively".

- I do a "SELECT count(ID) FROM MYTABLE" from psql and get 2 rows.
- I reload the webpage (which uses Hibernate) and I get the 10 entries
(as expected).
- I do a "SELECT count(ID) FROM MYTABLE" from psql and get 23 rows.
- I reload the webpage and I get another 10 entries (as expected).
- I do a "SELECT count(ID) FROM MYTABLE" from psql and get 49 rows.
... and so on...
More rows seem to be recovered when I reload from the Hibernate layer,
which doesn't seem to do anything more than just a SELECT.

Does anyone have any idea what may cause this? Is there a way I could
make it restore all the rows in one step? I've tried using REINDEX, but
it didn't make any difference.

Best wishes,

Bruno.

Browse pgsql-general by date

  From Date Subject
Next Message Paul Taylor 2009-08-04 13:48:39 Re: Does derby have an embedded Mode like Derby ?
Previous Message Sam Mason 2009-08-04 13:14:19 Re: Does derby have an embedded Mode like Derby ?