I am running PostgreSQL 8.2.4 on Windows 2003 Server.
For past year or so I have been archiving logs without any problems.
Recently I noticed a problem that appeared at random since I haven't
changed the configuration.
In the pg_log I noticed archiving works fine and then it gets stuck on
a particular log file and cannot archive it. The error I get is:
[2008-03-13 10:21:35 2152] LOG: archive command "COPY "pg_xlog
\000000010000014F000000E7" "F:/recovery/000000010000014F000000E7""
failed: return code 1
[2008-03-13 10:21:35 2152] WARNING: transaction log file
"000000010000014F000000E7" could not be archived: too many failures
The recovery location has enough disk space and proper permissions. So
I delete the problematic log .ready file
(000000010000014F000000E7.ready) from pg_xlog/archive_status, and the
archiving of other logs continues. The problematic .ready file will
keep appearing for a while and I keep deleting it to continue
archiving. Eventually the problematic log file gets archived. However,
then another log starts getting stuck again.
Using processExplorer I noticed that some postgres sessions hold an
open handle to these problematic log files. Most often it's an idling
service user used by pgAgent, but sometimes other users that are
executing inserts hold the file handle. I am guessing that this causes
the archiver not to move the file.
Why is this happening and how could I fix it? I don't seem to have any
issues in three other databases that implement the same archiving
technique, and I haven't had an issue with this one until recently. I
am not aware of any changes that would cause this.
