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
serverprocess
> DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because
anotherserver 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.