PITR on Win32 - Archive and Restore Command Strings
Given that I was doing a bit of testing on win32 anyway, I just couldn't help
myself...
There is a bit of a trap if using '%p' in archive_command - it has seperators
like '/' instead of '\', so does not work for some windows commands (like
'copy' for instance).
I had to put in the complete path for pg_xlog, e.g:
archive_command = 'copy c:\\databases\\pgdata\\pg_xlog\\%f
c:\\databases\\pgarchive\\%f'
and similarly during recovery, e.g:
restore_command = 'copy c:\\databases\\pgarchive\\%f
c:\\databases\\pgdata\\pg_xlog\\%f'
Is there a format that is equivalent to '%p' but outputs a windows style path?
regards
Mark
P.S : PITR itself worked perfectly, rolling forward 106 logs....
markir@coretech.co.nz writes:
There is a bit of a trap if using '%p' in archive_command - it has seperators
like '/' instead of '\', so does not work for some windows commands (like
'copy' for instance).
I was under the impression that forward slashes would work fine, as long
as you used them consistently?
regards, tom lane
hmmm... I was under that impression too, and was a bit surprised when it
*seemed* not to....I will go and check again - just in case some user
error leaked in :-)
Tom Lane wrote:
Show quoted text
markir@coretech.co.nz writes:
There is a bit of a trap if using '%p' in archive_command - it has seperators
like '/' instead of '\', so does not work for some windows commands (like
'copy' for instance).I was under the impression that forward slashes would work fine, as long
as you used them consistently?regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Not if you pass it to the Windows shell via system() or popen() - then
forward slashed paths need to be quoted. It's only the libraries that
understand forward slashes as God intended.
cheers
andrew
Mark Kirkwood wrote:
Show quoted text
hmmm... I was under that impression too, and was a bit surprised when
it *seemed* not to....I will go and check again - just in case some
user error leaked in :-)Tom Lane wrote:
markir@coretech.co.nz writes:
There is a bit of a trap if using '%p' in archive_command - it has
seperators
like '/' instead of '\', so does not work for some windows commands
(like
'copy' for instance).I was under the impression that forward slashes would work fine, as long
as you used them consistently?
I tried out Andrew's suggestion, to no avail - none of the archive_commands
below work:
archive_command = 'copy "%p" "c:/databases/pgarchive/%f"'
archive_command = 'copy \"%p\" \"c:/databases/pgarchive/%f\"'
archive_command = 'copy \\"%p\\" \\"c:/databases/pgarchive/%f\\"' # desperation
...
A bit more investigation reveals that copy is bit selective about when it will
accept quoted paths containing '/'.
This works:
cd c:\databases\pgdata\pg_xlog
copy 00000001000000000000006A "c:/databases/pgarchive/00000001000000000000006A"
This does not (unless your current directory is pg_xlog!):
copy "c:/databases/pgdata/pg_xlog/00000001000000000000006A"
"c:/databases/pgarchive/00000001000000000000006A"
I guess this is not so bad if it is *just* 'copy' with this behaviour. I might
try out winzip and see how I get on...
regards (with some puzzlement)
Mark
Quoting Andrew Dunstan <andrew@dunslane.net>:
Show quoted text
Not if you pass it to the Windows shell via system() or popen() - then
forward slashed paths need to be quoted. It's only the libraries that
understand forward slashes as God intended.
Oh, yes, multiple quotes strings also cause problems :-(. You have no
idea how frustrating this was when I was writing initdb, and how hard it
was to find the problems.
The chdir solution might be best if we can do it, so that we only need
to quote the destination path.
cheers
andrew
markir@coretech.co.nz wrote:
Show quoted text
I tried out Andrew's suggestion, to no avail - none of the archive_commands
below work:archive_command = 'copy "%p" "c:/databases/pgarchive/%f"'
archive_command = 'copy \"%p\" \"c:/databases/pgarchive/%f\"'
archive_command = 'copy \\"%p\\" \\"c:/databases/pgarchive/%f\\"' # desperation
...A bit more investigation reveals that copy is bit selective about when it will
accept quoted paths containing '/'.This works:
cd c:\databases\pgdata\pg_xlog
copy 00000001000000000000006A "c:/databases/pgarchive/00000001000000000000006A"This does not (unless your current directory is pg_xlog!):
copy "c:/databases/pgdata/pg_xlog/00000001000000000000006A"
"c:/databases/pgarchive/00000001000000000000006A"I guess this is not so bad if it is *just* 'copy' with this behaviour. I might
try out winzip and see how I get on...regards (with some puzzlement)
Mark
Quoting Andrew Dunstan <andrew@dunslane.net>:
Not if you pass it to the Windows shell via system() or popen() - then
forward slashed paths need to be quoted. It's only the libraries that
understand forward slashes as God intended.
Andrew Dunstan wrote:
Oh, yes, multiple quotes strings also cause problems :-(. You have no
idea how frustrating this was when I was writing initdb, and how hard it
was to find the problems.The chdir solution might be best if we can do it, so that we only need
to quote the destination path.cheers
andrew
markir@coretech.co.nz wrote:
I tried out Andrew's suggestion, to no avail - none of the archive_commands
below work:archive_command = 'copy "%p" "c:/databases/pgarchive/%f"'
archive_command = 'copy \"%p\" \"c:/databases/pgarchive/%f\"'
archive_command = 'copy \\"%p\\" \\"c:/databases/pgarchive/%f\\"' # desperation
...
As I remember the fix was to use this:
archive_command = '"copy "%p" "c:/databases/pgarchive/%f""'
Yes, that is one extra quote at the start and end of the string. Would
you try that?
FYI, port.h has this:
/*
* Win32 needs double quotes at the beginning and end of system()
* strings. If not, it gets confused with multiple quoted strings.
* It also must use double-quotes around the executable name
* and any files used for redirection. Other args can use single-quotes.
*
* See the "Notes" section about quotes at:
* http://home.earthlink.net/~rlively/MANUALS/COMMANDS/C/CMD.HTM
*/
#ifdef WIN32
#define SYSTEMQUOTE "\""
#else
#define SYSTEMQUOTE ""
#endif
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Using :
archive_command = '"copy "%p" "c:/databases/pgarchive/%f""'
I see this is the log:
LOG: archive command ""copy
"c:/databases/pgdata/pg_xlog/000000010000000000000000"
"c:/databases/pgarchive/000000010000000000000000""" failed: return code 1
The system cannot find the file specified.
Looks like it is confused about what the executable is...
regards
Mark
Quoting Bruce Momjian <pgman@candle.pha.pa.us>:
Show quoted text
Andrew Dunstan wrote:
Oh, yes, multiple quotes strings also cause problems :-(. You have no
idea how frustrating this was when I was writing initdb, and how hard it
was to find the problems.The chdir solution might be best if we can do it, so that we only need
to quote the destination path.cheers
andrew
markir@coretech.co.nz wrote:
I tried out Andrew's suggestion, to no avail - none of the
archive_commands
below work:
archive_command = 'copy "%p" "c:/databases/pgarchive/%f"'
archive_command = 'copy \"%p\" \"c:/databases/pgarchive/%f\"'
archive_command = 'copy \\"%p\\" \\"c:/databases/pgarchive/%f\\"' #desperation
...
As I remember the fix was to use this:
archive_command = '"copy "%p" "c:/databases/pgarchive/%f""'
Yes, that is one extra quote at the start and end of the string. Would
you try that?FYI, port.h has this:
/*
* Win32 needs double quotes at the beginning and end of system()
* strings. If not, it gets confused with multiple quoted strings.
* It also must use double-quotes around the executable name
* and any files used for redirection. Other args can use single-quotes.
*
* See the "Notes" section about quotes at:
* http://home.earthlink.net/~rlively/MANUALS/COMMANDS/C/CMD.HTM
*/
#ifdef WIN32
#define SYSTEMQUOTE "\""
#else
#define SYSTEMQUOTE ""
#endif-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
markir@coretech.co.nz wrote:
Using :
archive_command = '"copy "%p" "c:/databases/pgarchive/%f""'
I see this is the log:
LOG: archive command ""copy
"c:/databases/pgdata/pg_xlog/000000010000000000000000"
"c:/databases/pgarchive/000000010000000000000000""" failed: return code 1
The system cannot find the file specified.Looks like it is confused about what the executable is...
OK, I think I might see the cause. Try this:
archive_command = '""copy" "%p" "c:/databases/pgarchive/%f""'
In other words, quote the 'copy' command too. Crazy --- yes.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
OK, I think I might see the cause. Try this:
archive_command = '""copy" "%p" "c:/databases/pgarchive/%f""'
In other words, quote the 'copy' command too. Crazy --- yes.
Yikes. If it's that hard to get it to work, maybe we should just
knuckle under and make %p convert / to \ under Windows :-(
(Microsoft cultural imperialism wins another round...)
However, just one question --- the Microsofties also like to put spaces
in path names. How much quoting would be needed to make this command
string work in the face of spaces in %p, even if we did the backslash
thing? If the answer is "nearly as much" then I'm not going to be
excited about backslashing.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
OK, I think I might see the cause. Try this:
archive_command = '""copy" "%p" "c:/databases/pgarchive/%f""'
In other words, quote the 'copy' command too. Crazy --- yes.Yikes. If it's that hard to get it to work, maybe we should just
knuckle under and make %p convert / to \ under Windows :-((Microsoft cultural imperialism wins another round...)
However, just one question --- the Microsofties also like to put spaces
in path names. How much quoting would be needed to make this command
string work in the face of spaces in %p, even if we did the backslash
thing? If the answer is "nearly as much" then I'm not going to be
excited about backslashing.
For sure we have to have the quoting working for spaces in directory
names. Lets see how the new suggestion works for him.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073I will try it out later this afternoon.
I am a bit delayed with a hardware problem... suspect bad ram or too hot
cpu, so have been swapping and changing bits all morning (running with
500Mhz + 256M instead of 700Mhz + 512M ... noticeably slower).
regards
Mark
Bruce Momjian wrote:
Show quoted text
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
OK, I think I might see the cause. Try this:
archive_command = '""copy" "%p" "c:/databases/pgarchive/%f""'
In other words, quote the 'copy' command too. Crazy --- yes.Yikes. If it's that hard to get it to work, maybe we should just
knuckle under and make %p convert / to \ under Windows :-((Microsoft cultural imperialism wins another round...)
However, just one question --- the Microsofties also like to put spaces
in path names. How much quoting would be needed to make this command
string work in the face of spaces in %p, even if we did the backslash
thing? If the answer is "nearly as much" then I'm not going to be
excited about backslashing.For sure we have to have the quoting working for spaces in directory
names. Lets see how the new suggestion works for him.
Well, it is really fighting us.....
This is the archive_command:
'""copy.exe" "%p" "c:/databases/pgarchive/%f""'
and this is the log:
LOG: archive command """copy.exe"
"c:/databases/pgdata/pg_xlog/000000010000000000000007"
"c:/databases/pgarchive/000000010000000000000007""" failed: return code 1
'"copy.exe"' is not recognized as an internal or external command,
operable program or batch file.
(tried plain old '"copy"' too)
regards
Mark
Quoting Bruce Momjian <pgman@candle.pha.pa.us>:> Tom Lane wrote:
Show quoted text
Bruce Momjian <pgman@candle.pha.pa.us> writes:
OK, I think I might see the cause. Try this:
archive_command = '""copy" "%p" "c:/databases/pgarchive/%f""'
In other words, quote the 'copy' command too. Crazy --- yes.Yikes. If it's that hard to get it to work, maybe we should just
knuckle under and make %p convert / to \ under Windows :-((Microsoft cultural imperialism wins another round...)
However, just one question --- the Microsofties also like to put spaces
in path names. How much quoting would be needed to make this command
string work in the face of spaces in %p, even if we did the backslash
thing? If the answer is "nearly as much" then I'm not going to be
excited about backslashing.For sure we have to have the quoting working for spaces in directory
names. Lets see how the new suggestion works for him.
"Copy" is an internal command of the command line shell in Windows. There is
no separate executable for it.
Try "cmd.exe /C copy ...." insteads (see "cmd /?" for further options).
Regards,
Christian.
----- Original Message -----
From: <markir@coretech.co.nz>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Andrew Dunstan" <andrew@dunslane.net>;
<pgsql-hackers-win32@postgresql.org>
Sent: Monday, August 09, 2004 7:37 AM
Subject: Re: [pgsql-hackers-win32] PITR on Win32 - Archive and Restore
Well, it is really fighting us.....
This is the archive_command:
'""copy.exe" "%p" "c:/databases/pgarchive/%f""'
and this is the log:
LOG: archive command """copy.exe"
"c:/databases/pgdata/pg_xlog/000000010000000000000007"
"c:/databases/pgarchive/000000010000000000000007""" failed: return code 1
'"copy.exe"' is not recognized as an internal or external command,
operable program or batch file.(tried plain old '"copy"' too)
regards
Mark
Quoting Bruce Momjian <pgman@candle.pha.pa.us>:> Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
OK, I think I might see the cause. Try this:
archive_command = '""copy" "%p" "c:/databases/pgarchive/%f""'
In other words, quote the 'copy' command too. Crazy --- yes.Yikes. If it's that hard to get it to work, maybe we should just
knuckle under and make %p convert / to \ under Windows :-((Microsoft cultural imperialism wins another round...)
However, just one question --- the Microsofties also like to put
spaces
Show quoted text
in path names. How much quoting would be needed to make this command
string work in the face of spaces in %p, even if we did the backslash
thing? If the answer is "nearly as much" then I'm not going to be
excited about backslashing.For sure we have to have the quoting working for spaces in directory
names. Lets see how the new suggestion works for him.---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Well, it is really fighting us.....
This is the archive_command:
'""copy.exe" "%p" "c:/databases/pgarchive/%f""'
and this is the log:
LOG: archive command """copy.exe"
"c:/databases/pgdata/pg_xlog/000000010000000000000007"
"c:/databases/pgarchive/000000010000000000000007""" failed: return code
1 '"copy.exe"' is not recognized as an internal or external command,
operable program or batch file.(tried plain old '"copy"' too)
What happens with this archive command?:
'"copy "%p" "c:/databases/pgarchive/%f""'
cheers
andrew
I *think* I have tried that one, but checked anyway:
The result is :
LOG: archive command ""copy
"c:/databases/pgdata/pg_xlog/000000010000000000000000"
"c:/databases/pgarchive/000000010000000000000000""" failed: return code 1
The system cannot find the file specified.
I think this led us on to more bizzare pastures...
mark
Quoting Andrew Dunstan <andrew@dunslane.net>:
Show quoted text
Well, it is really fighting us.....
This is the archive_command:
'""copy.exe" "%p" "c:/databases/pgarchive/%f""'
and this is the log:
LOG: archive command """copy.exe"
"c:/databases/pgdata/pg_xlog/000000010000000000000007"
"c:/databases/pgarchive/000000010000000000000007""" failed: return code
1 '"copy.exe"' is not recognized as an internal or external command,
operable program or batch file.(tried plain old '"copy"' too)
What happens with this archive command?:
'"copy "%p" "c:/databases/pgarchive/%f""'
cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Question:
It is my understanding that this copy operation is initiated out of some
main program and not in a shell script, right ?
Then why don't you use one of the CopyFile or CopyFileEx API routines on
Win32 ? Sure, it will need some additional #ifdef clauses in the source
codes, but I think performance will be better and you can even include a
progress callback function. Besides, no child process needs to be created
and - best of all ! - this approach removes all file name escaping and
parsing issues because it will take source and destination parameters as
strings.
What do you think ?
Regards,
Christian.
----- Original Message -----
From: "Andrew Dunstan" <andrew@dunslane.net>
To: <markir@coretech.co.nz>
Cc: <pgsql-hackers-win32@postgresql.org>
Sent: Monday, August 09, 2004 9:36 AM
Subject: Re: [pgsql-hackers-win32] PITR on Win32 - Archive and Restore
Show quoted text
Well, it is really fighting us.....
This is the archive_command:
'""copy.exe" "%p" "c:/databases/pgarchive/%f""'
and this is the log:
LOG: archive command """copy.exe"
"c:/databases/pgdata/pg_xlog/000000010000000000000007"
"c:/databases/pgarchive/000000010000000000000007""" failed: return code
1 '"copy.exe"' is not recognized as an internal or external command,
operable program or batch file.(tried plain old '"copy"' too)
What happens with this archive command?:
'"copy "%p" "c:/databases/pgarchive/%f""'
cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Good suggestion, got me thinking-
Trying out stuff on the command line seems to show a general "unwillingness" to
work with forward slashed names at all:
C:\>cmd /c copy "c:/databases/pgdata/
pg_xlog/000000010000000000000000" "c:/databases/pgarchive/000000010000000000000
000"
The system cannot find the file specified.
0 file(s) copied.
C:\>cmd /c "copy" "c:/databases/pgdata/pg_xlog/000000010000000000000000" "c:/da
tabases/pgarchive/000000010000000000000000"
'copy" "c:' is not recognized as an internal or external command,
operable program or batch file.
C:\>cmd /c "copy "c:/databases/pgdata/pg_xlog/000000010000000000000000" "c:/dat
abases/pgarchive/000000010000000000000000""
The system cannot find the file specified.
0 file(s) copied.
C:\>cmd /c ""copy" "c:/databases/pgdata/pg_xlog/000000010000000000000000" "c:/d
atabases/pgarchive/000000010000000000000000""
'"copy"' is not recognized as an internal or external command,
operable program or batch file.
whereas of course :
C:\>cmd /c copy c:\databases\pgdata\pg_xlog\000000010000000000000000 c:\databa
es\pgarchive\000000010000000000000000
1 file(s) copied.
Quoting Christian Klemke <Christian.Klemke@t-online.de>:
Show quoted text
"Copy" is an internal command of the command line shell in Windows. There is
no separate executable for it.
Try "cmd.exe /C copy ...." insteads (see "cmd /?" for further options).Regards,
Christian.
On 9 Aug 2004 at 20:12, markir@coretech.co.nz wrote:
I *think* I have tried that one, but checked anyway:
The result is :
LOG: archive command ""copy
"c:/databases/pgdata/pg_xlog/000000010000000000000000"
"c:/databases/pgarchive/000000010000000000000000""" failed: return code 1
The system cannot find the file specified.I think this led us on to more bizzare pastures...
The problem here is that "Copy" is not an external command (as it
says), but it is built into the "shell" (cmd.exe). To use copy you would
have to run cmd.exe and make the copy command the first parameter
after the /C switch.
It's much better to use "xcopy" anyway, which *is* an external command
and can be run with a string similar to the one use are using now.
Cheers,
Gary.
markir@coretech.co.nz writes:
Trying out stuff on the command line seems to show a general "unwillingness" to
work with forward slashed names at all:
Yeah, I think it may only be the POSIX library routines that are really
forward-slash friendly.
As I said before, I'm willing to #ifdef the code so that the string
substituted for %p converts all / to \ on Windows. What I'd like to
know is whether that's enough to solve the problems, particularly in
face of spaces in the path, or whether we'll still be in quoting hell.
regards, tom lane