Unable to archive WAL files

From: Nikola <XLPizza(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Unable to archive WAL files
Date: 2009-04-17 16:36:03
Message-ID: feb2a2d2-9e0d-4235-9df9-ca000f9fbcaf@y7g2000yqa.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message David Fetter 2009-04-17 16:51:20 Re: Migration/Upgrade Problems
Previous Message Irene Barg 2009-04-17 16:27:09 number of relations reported by vacuumdb -av