Обсуждение: Using copy for WAL archiving on Windows

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

* Harald Armin Massa wrote:

>     Should PostgreSQL maybe provide its own file-copy utility for
>     Windows that meets the requirements for safe WAL archiving?
>
> Microsoft does provide an enterprise-ready  webscale copy program ... it
> is called robocopy and part of the Windows Server Resource Kit Tools.
> Would you mind trying that utility for copying WAL-files?

I'm not sure I trust _that_:

    ### archive_wal.bat ###

    robocopy pg_xlog c:\wal %1 /r:0 /w:0 /xc
    if errorlevel 4 exit 1
    if errorlevel 1 exit 0
    if errorlevel 0 exit 1

    ### end ###

    archive_command = "archive_wal.bat %f"

robocopy's exit codes make good reading, if you are into horror stories.

Thanks for the tip; I had previously discounted robocopy because I
thought it could not copy individual files, only whole directories (with
exclusion patterns, but not inclusion patterns). It turns out I will not
use it for entirely different reasons.

The perfect archiving utility for Windows is something like this:

### archive_wal.c ###

#define UNICODE 1
#define _UNICODE 1
#include <windows.h>

int wmain(int argc, WCHAR *argv[])
{
    return ((argc == 3
         && CopyFile(argv[1], argv[2], TRUE) != 0) ? 0 : 1);
}

### end ###

archive_command = "archive_wal %p c:\wal\%f"

--
Christian