Re: [GENERAL] Using copy for WAL archiving on Windows
Christian, 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? http://www.microsoft.com/downloadS/en/details.aspx?familyid=9d467a69-57ff-4ae7-96ee-b18c4790cffddisplaylang=en (Microsoft URLs are like a leaf in the wind of change, so better google for robocopy site:microsoft.com) best wishes, Harald -- Harald Armin Massa www.2ndQuadrant.com PostgreSQL Training, Services and Support 2ndQuadrant Deutschland GmbH i.Gr. GF: Harald Armin Massa
Re: [GENERAL] Using copy for WAL archiving on Windows
* 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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:\Datenecho. t C:\Datencopy t s 1 Datei(en) kopiert. C:\Datencopy t s s überschreiben? (Ja/Nein/Alle): n 0 Datei(en) kopiert. C:\Datenecho %ERRORLEVEL% 0 (It's in German, but I think the problem is obvious.) Next attempt, this time with no interactive prompt: C:\Datencopy t s nul s überschreiben? (Ja/Nein/Alle): 0 Datei(en) kopiert. C:\Datenecho %ERRORLEVEL% 0 xcopy, on the other hand, works: C:\Datenxcopy t s nul C:\Daten\s überschreiben (Ja/Nein/Alle)? ﳐ C:\Daten\s überschreiben (Ja/Nein/Alle)? C:\Datenecho %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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
