I am running PostgreSQL 8.2.4 on Windows 2003 Server.
I have a setup where WAL files are archived to another drive on the
same machine. The command looks like this:
archive_command = 'COPY "%p" "F:/recovery/%f"'
This works well, but I often get the following error in the log:
[2009-04-17 12:07:34 508] LOG: archive command "COPY "pg_xlog
\000000010000035600000006" "F:/recovery/000000010000035600000006""
failed: return code 1
[2009-04-17 12:07:34 508] WARNING: transaction log file
"000000010000035600000006" could not be archived: too many failures
I checked and noticed that the handle to that file is being held by a
postgres.exe process that corresponds to a particular user in the
database who's been logged in since 2009-03-25 (a little less than
month) and is in <IDLE> status.
If I restart the database usually the connections disappear and the
WAL gets archived correctly. However, I am not able to restart this
database as it is a production database and I am wondering why these
idle connections hold an open handle that seems to prevent archiving?
Once this file is unable to be archived, no other files get archived
(unless I remove its .ready file manually). I have another server with
the same postgresql.conf parameters that does not exhibit this
behavior.
Any help is appreciated. Thanks.
Nikola