Hello all,
the PostgreSQL manual, section 24.3.1, has an example archive_command
for Windows. It is
copy "%p" "C:\\server\\archivedir\\%f"
. The next sentence disclaims this as "an example, not a
recommendation". I just had occasion to do some tests with that, and it
appears to me that using copy for this is a really bad idea. As it turns
out, copy (at least on Windows 7) does not return a non-zero exit code
if the copy failed because the destination file already existed.
C:\Daten>echo. > t
C:\Daten>copy t s
1 Datei(en) kopiert.
C:\Daten>copy t s
s überschreiben? (Ja/Nein/Alle): n
0 Datei(en) kopiert.
C:\Daten>echo %ERRORLEVEL%
0
(It's in German, but I think the problem is obvious.) Next attempt, this
time with no interactive prompt:
C:\Daten>copy t s < nul
s überschreiben? (Ja/Nein/Alle):
0 Datei(en) kopiert.
C:\Daten>echo %ERRORLEVEL%
0
xcopy, on the other hand, works:
C:\Daten>xcopy t s < nul
C:\Daten\s überschreiben (Ja/Nein/Alle)? ﳐ
C:\Daten\s überschreiben (Ja/Nein/Alle)?
C:\Daten>echo %ERRORLEVEL%
2
I'm not sure what that thing is it printed after the prompt, but at
least the exit code is good.
copy produces good exit codes for other errors, such as when it does not
have permission to write to the target directory. The only situation
where it "fails to fail" is when you have an identically named file in
the target directory already. Unfortunately, that is also the easiest
mistake to make -- you copy the configuration from one server to another
and forget to make that little change. Now two servers archive to the
same shared directory, and neither notices.
Should PostgreSQL maybe provide its own file-copy utility for Windows
that meets the requirements for safe WAL archiving?
--
Christian
