Обсуждение: problem with dead 'drop table' process
hi,
today i wanted to backup my entire database with a 'pg_dumpall'. during this i got an error message that the copy of a certain table to stderr is not possible. i don't know the exact error message right know it was something like ...
"failure: copy strside ( [columns] ) to stderr"
and the dump was aborted.
then i tried to copy the content of the faulty table to another table...
# create table strside2 (like strside);
# insert into strside2 select * from strside;
during the insert process the connection to the postgres db was lost. the logfile showed following error message ...
LOG: server process (PID 5540) was terminated by signal 11
LOG: terminating any other active server processes
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.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2006-01-20 07:56:30 PST
.. following recovery start.
to save the database on a filesystem basis i stopped the postgres server and tried to tar the entire pgdata directory. this process stopped with a "segmentation fault" during the tar process.
after that i wanted to drop the faulty table, because i got another copy of it. however the "drop table strside" process did not stop. i tried a ctrl-c to abort, but it did not work.
the server is running, and i can connect with another psql client, but the drop table process is still present. ps aux shows
postgres 5548 0.0 0.8 16420 4548 ? D 07:58 0:00 postgres: postgres wlan [local] DROP TABLE
i cannot kill it with kill -9, and also i'm not sure if i should. maybe the database is not running after it at all?
anyone got any hints?
thanx,
chris
--
Christian Sengstock - Tel: 06221 / 650 5889, Mob: 0178 / 38 79 671
today i wanted to backup my entire database with a 'pg_dumpall'. during this i got an error message that the copy of a certain table to stderr is not possible. i don't know the exact error message right know it was something like ...
"failure: copy strside ( [columns] ) to stderr"
and the dump was aborted.
then i tried to copy the content of the faulty table to another table...
# create table strside2 (like strside);
# insert into strside2 select * from strside;
during the insert process the connection to the postgres db was lost. the logfile showed following error message ...
LOG: server process (PID 5540) was terminated by signal 11
LOG: terminating any other active server processes
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.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2006-01-20 07:56:30 PST
.. following recovery start.
to save the database on a filesystem basis i stopped the postgres server and tried to tar the entire pgdata directory. this process stopped with a "segmentation fault" during the tar process.
after that i wanted to drop the faulty table, because i got another copy of it. however the "drop table strside" process did not stop. i tried a ctrl-c to abort, but it did not work.
the server is running, and i can connect with another psql client, but the drop table process is still present. ps aux shows
postgres 5548 0.0 0.8 16420 4548 ? D 07:58 0:00 postgres: postgres wlan [local] DROP TABLE
i cannot kill it with kill -9, and also i'm not sure if i should. maybe the database is not running after it at all?
anyone got any hints?
thanx,
chris
--
Christian Sengstock - Tel: 06221 / 650 5889, Mob: 0178 / 38 79 671
Christian Sengstock <csengstock@gmail.com> writes:
> to save the database on a filesystem basis i stopped the postgres server and
> tried to tar the entire pgdata directory. this process stopped with a
> "segmentation fault" during the tar process.
So there's something wrong beyond just postgres ...
> the server is running, and i can connect with another psql client, but the
> drop table process is still present. ps aux shows
> postgres 5548 0.0 0.8 16420 4548 ? D 07:58 0:00 postgres: postgres
> wlan [local] DROP TABLE
> i cannot kill it with kill -9, and also i'm not sure if i should. maybe the
> database is not running after it at all?
A process stuck in an unkillable disk wait is a pretty common symptom of
low-level problems. There might be a disk hardware issue --- have you
looked into the kernel message log on the machine?
regards, tom lane