CODEWITHSUNDEEP
postgresqlpostgresql-9.2psql
Matt E
Matt E

Reputation: 477

psql seems to timeout with long queries

I am performing a bulk copy into postgres with about 80GB of data. 

\copy my_table FROM '/path/csv_file.csv' csv DELIMITER ','

Before the transaction is committed I get the following error.

Server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

In the PostgreSQL logs:

LOG:server process (PID 21122) was terminated by signal 9: Killed
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.

Upvotes: 3

Views: 9297

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324265

Your backend process receiving a signal 9 (SIGKILL). This can happen if:

  • Somebody sends a kill -9 manually;
  • A cron job is set up to send kill -9 under some circumstances (very unsafe, do not do this); or
  • the Linux out-of-memory (OOM) killer triggers and terminates the process.

In the latter case you will see reports of OOM killer activity in the kernel's dmesg output. I expect this is what you'll see in your case.

PostgreSQL servers should be configured without virtual memory overcommit so that the OOM killer does not run and PostgreSQL can handle out-of-memory conditions its self. See the PostgreSQL documentation on Linux memory overcommit.

The separate question "why is this using so much memory" remains. Answering that requires more knowledge of your setup: how much RAM the server has, how much of it is free, what your work_mem and maintenance_work_mem settings are, etc. It isn't a very interesting problem to look into until you upgrade to the current PostgreSQL 8.4 patch release to make sure the problem isn't one that's already fixed.

Upvotes: 14

Related Questions