3

I'm having a big problem with a PostreSQL database. Whenever I do something with a huge table (over 9 million rows) the whole postgres process crashes with the following entries in the log file:

PANIC:  could not write to log file 000000010000000000000022 at offset 11673600, length 909312: Bad file descriptor
LOG:  WAL writer process (PID 17056) exited with exit code 3
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
CONTEXT:  COPY fund_records, line 2088885: "..."
LOG:  PID 31820 in cancel request did not match any process
LOG:  all server processes terminated; reinitializing
FATAL:  pre-existing shared memory block is still in use
HINT:  Check if there are any old server processes still running, and terminate them.

Verbose log information for the "could not write to log file" error from version 8.4.22:

XLogWrite, .\src\backend\access\transam\xlog.c:1658

The CONTEXT is of course differnt depending on what I did. The above COPY command was logged when restoring a dump with the big table with pg_restore. Other occurrences were VACUUM (when I run vacuum on either the single big table or the whole database) or even SELECT (when I run a simple 1 row result select joining the big table).

I can reproduce this crash 100% by either running vacuum on the table or trying to restore the table data from a dump. Sometimes even simple things like selecting from the table or inserting into it cause the crash. It's always the could not write to log file x at offset y, length z: Bad file descriptor error. I've already tried disabling autovacuum during pg_resotre but it still fails.

This problem seems to be specific to one server, because the same table/dump works fine when I import it on my local machine. The affected server is a virtual (on Parallels Virtuozzo base) Windows Server 2008 SP2 x64 with 2 GB RAM. There is only a single HDD partition and nearly 40 GB of free space on it. Nothing other than PostgreSQL is running on the server, also no anti virus software. But PostgreSQL is the only application on this server that shows problems, all other operations I've tried work fine, so it seems like it's not a general problem with this machine.

I've tried PostgreSQL server versions 8.4.4 (this was running for years until it first showed the error), 8.4.22 and 9.4.4, all show the same error. I've tried both upgrading from 8.4.4 to 8.4.22 and fresh installs of 8.4.22 and 9.4.4 (there I've also completely deleted the data folder) multiple times. For the upgrade to 9.4.4 I've tried both pg_restore (crashes as already said above) and pg_upgrade. pg_upgrade works, but after that vacuum on the table in 9.4.4 still crashes.

Can someone please explain what's wrong here?

Update:

I've changed some values in postgresql.conf away from their default values and now the crash happens much rarer. Simple SQL and DML statements don't crash as often any more, but VACUUM still crashes 100%. I'm on version 8.4.22 right now. Here are the values I changed:

shared_buffers = 128MB
work_mem = 16MB
maintenance_work_mem = 64MB
wal_buffers = 4MB
  • 1
    Just to make sure I understand, you've actually done a completely fresh PostgreSQL install (with all databases deleted, etc) and restored a dump, then when you try to access this huge table in a freshly restored database you get this error? And you've actually tried server versions 8.4 and 9.4? How - copy and pg_upgrade? Dump and restore? – Craig Ringer Aug 5 '15 at 4:53
  • 1
    @cremor Dumps don't contain the index data, only an instruction to create them. Also pg_dump usually does a sequential scan of the table which will not access the index. – Craig Ringer Aug 5 '15 at 7:41
  • 1
    @Kassandry The server is run by a hosting company. I only have access to my virtual instance. Also, I'm on Windows. – cremor Aug 14 '15 at 6:21
  • 1
    It sounds like you should bring this up with your hosting company then. – Erwin Brandstetter Aug 18 '15 at 11:24
  • 1
    @cremor: Maybe they have an idea anyway. Bad file descriptor sound rather severe to me. Since you have tried both 8.4.22 and 9.4.4, a problem with the underlying OS / file system seems more likely. Aside: I would consider Linux / Unix as OS to run Postgres on. – Erwin Brandstetter Aug 18 '15 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.