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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
