Re: PITR on Win32 - Archive and Restore

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(at)coretech(dot)co(dot)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:

>markir(at)coretech(dot)co(dot)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:

> 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(at)coretech(dot)co(dot)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(at)dunslane(dot)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.

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(at)coretech(dot)co(dot)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
>...
>
>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(at)dunslane(dot)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(at)coretech(dot)co(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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...

regards

Mark

Quoting Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>:

> 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(at)coretech(dot)co(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>

markir(at)coretech(dot)co(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

I 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:

>Tom Lane wrote:
>
>
>>Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us>:> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)coretech(dot)co(dot)nz>
To: "Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>; "Andrew Dunstan" <andrew(at)dunslane(dot)net>;
<pgsql-hackers-win32(at)postgresql(dot)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(at)candle(dot)pha(dot)pa(dot)us>:> Tom Lane wrote:
> > > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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.
>
>
>
>
>
> ---------------------------(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(at)dunslane(dot)net>:

> > 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?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

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(at)dunslane(dot)net>
To: <markir(at)coretech(dot)co(dot)nz>
Cc: <pgsql-hackers-win32(at)postgresql(dot)org>
Sent: Monday, August 09, 2004 9:36 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)
> >
>
> 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?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

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(dot)Klemke(at)t-online(dot)de>:

> "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(at)coretech(dot)co(dot)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(at)coretech(dot)co(dot)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

Tom Lane wrote:

>markir(at)coretech(dot)co(dot)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.
>
>
>

Names with spaces will need to be quoted :-(

The good news is that we don't seem to need as many quotes as Bruce
suggested. See my earlier email.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Tom Lane wrote:
>> 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.

> Names with spaces will need to be quoted :-(

But does that have to be done in the code, or will
archive_command = 'copy "%p" ...'
work?

Strictly speaking, people probably ought to use something like
archive_command = 'cp "%p" ...'
on Unix too, so if this is what it takes on Windows I'm satisfied.
Just wondering about what other gotchas Redmond has prepared for us...

regards, tom lane

Tom Lane wrote:

>Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
>
>>Tom Lane wrote:
>>
>>
>>>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.
>>>
>>>
>
>
>
>>Names with spaces will need to be quoted :-(
>>
>>
>
>But does that have to be done in the code, or will
> archive_command = 'copy "%p" ...'
>work?
>
>

That should work just fine (or with xcopy).

>Strictly speaking, people probably ought to use something like
> archive_command = 'cp "%p" ...'
>on Unix too, so if this is what it takes on Windows I'm satisfied.
>Just wondering about what other gotchas Redmond has prepared for us...
>
>
>
>

Yes, it's just that Unix people (or at least those of us who are
professionals) usually have more sense that to put spaces in file names ...

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Tom Lane wrote:
>> But does that have to be done in the code, or will
>> archive_command = 'copy "%p" ...'
>> work?

> That should work just fine (or with xcopy).

Okay, I'll change %p to emit \ on Windows, and we'll see where that
takes us.

regards, tom lane

> Okay, I'll change %p to emit \ on Windows, and we'll see where that
> takes us.

I've applied the attached patch, in case anyone is in a big hurry to try
it.

regards, tom lane

Index: src/backend/access/transam/xlog.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/access/transam/xlog.c,v
retrieving revision 1.157
diff -c -r1.157 xlog.c
*** src/backend/access/transam/xlog.c 8 Aug 2004 03:22:08 -0000 1.157
--- src/backend/access/transam/xlog.c 9 Aug 2004 16:23:51 -0000
***************
*** 1962,1968 ****
--- 1962,1978 ----
/* %p: full path of target file */
sp++;
StrNCpy(dp, xlogpath, endp-dp);
+ #ifndef WIN32
dp += strlen(dp);
+ #else
+ /* On Windows, change / to \ in the substituted path */
+ while (*dp)
+ {
+ if (*dp == '/')
+ *dp = '\\';
+ dp++;
+ }
+ #endif
break;
case 'f':
/* %f: filename of desired file */
Index: src/backend/postmaster/pgarch.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/postmaster/pgarch.c,v
retrieving revision 1.5
diff -c -r1.5 pgarch.c
*** src/backend/postmaster/pgarch.c 5 Aug 2004 23:32:10 -0000 1.5
--- src/backend/postmaster/pgarch.c 9 Aug 2004 16:23:51 -0000
***************
*** 436,442 ****
--- 436,452 ----
/* %p: full path of source file */
sp++;
StrNCpy(dp, pathname, endp-dp);
+ #ifndef WIN32
dp += strlen(dp);
+ #else
+ /* On Windows, change / to \ in the substituted path */
+ while (*dp)
+ {
+ if (*dp == '/')
+ *dp = '\\';
+ dp++;
+ }
+ #endif
break;
case 'f':
/* %f: filename of source file */

The only down side is now we have 2 classes of parameters in
postgresql.conf:

- ones that accept forward slash names (log_directory)
- ones that only work with backslashes (archive_command)

The other option was to consider making the archiver change working
directory to pg_xlog, then the
forward slashed names will work (going by experiments with copy).

regards

Mark

Tom Lane wrote:

>Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
>
>>Tom Lane wrote:
>>
>>
>>>But does that have to be done in the code, or will
>>>archive_command = 'copy "%p" ...'
>>>work?
>>>
>>>
>
>
>
>>That should work just fine (or with xcopy).
>>
>>
>
>Okay, I'll change %p to emit \ on Windows, and we'll see where that
>takes us.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

Mark Kirkwood said:
> The only down side is now we have 2 classes of parameters in
> postgresql.conf:
>
> - ones that accept forward slash names (log_directory)
> - ones that only work with backslashes (archive_command)
>

Without looking at it closely, ISTM we should force canonicalisation of the
logdir name, so that on Windows it would accept backslahes. After all,
initdb and pg_ctl (to name but two I have knowledge of) accept backslashes.

cheers

andrew

I have gotten confused by this. Does COPY work with quoted paths only
if we use forward slashes, or was this fix just for the slash issue and
not spaces?

---------------------------------------------------------------------------

Tom Lane wrote:
> > Okay, I'll change %p to emit \ on Windows, and we'll see where that
> > takes us.
>
> I've applied the attached patch, in case anyone is in a big hurry to try
> it.
>
> regards, tom lane
>
>
> Index: src/backend/access/transam/xlog.c
> ===================================================================
> RCS file: /cvsroot/pgsql-server/src/backend/access/transam/xlog.c,v
> retrieving revision 1.157
> diff -c -r1.157 xlog.c
> *** src/backend/access/transam/xlog.c 8 Aug 2004 03:22:08 -0000 1.157
> --- src/backend/access/transam/xlog.c 9 Aug 2004 16:23:51 -0000
> ***************
> *** 1962,1968 ****
> --- 1962,1978 ----
> /* %p: full path of target file */
> sp++;
> StrNCpy(dp, xlogpath, endp-dp);
> + #ifndef WIN32
> dp += strlen(dp);
> + #else
> + /* On Windows, change / to \ in the substituted path */
> + while (*dp)
> + {
> + if (*dp == '/')
> + *dp = '\\';
> + dp++;
> + }
> + #endif
> break;
> case 'f':
> /* %f: filename of desired file */
> Index: src/backend/postmaster/pgarch.c
> ===================================================================
> RCS file: /cvsroot/pgsql-server/src/backend/postmaster/pgarch.c,v
> retrieving revision 1.5
> diff -c -r1.5 pgarch.c
> *** src/backend/postmaster/pgarch.c 5 Aug 2004 23:32:10 -0000 1.5
> --- src/backend/postmaster/pgarch.c 9 Aug 2004 16:23:51 -0000
> ***************
> *** 436,442 ****
> --- 436,452 ----
> /* %p: full path of source file */
> sp++;
> StrNCpy(dp, pathname, endp-dp);
> + #ifndef WIN32
> dp += strlen(dp);
> + #else
> + /* On Windows, change / to \ in the substituted path */
> + while (*dp)
> + {
> + if (*dp == '/')
> + *dp = '\\';
> + dp++;
> + }
> + #endif
> break;
> case 'f':
> /* %f: filename of source file */
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I have gotten confused by this. Does COPY work with quoted paths only
> if we use forward slashes, or was this fix just for the slash issue and
> not spaces?

This only fixes the slash issue. If your database path includes spaces
you'll still need to put quotes in the archive_command, but it shouldn't
be any worse than
archive_command = 'copy "%p" "c:\someplace\%f"'

I'd appreciate confirmation though from some win32 users that the above
indeed works.

regards, tom lane

"Andrew Dunstan" <andrew(at)dunslane(dot)net> writes:
> I think this is the right solution -
> the user should put the quotes in, not postgres.

Yeah, I'd prefer it that way if at all possible. I think that having %p
generate the quotes would be making too many assumptions about what the
command string is going to do with the path.

regards, tom lane

Tom Lane said:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>> I have gotten confused by this. Does COPY work with quoted paths only
>> if we use forward slashes, or was this fix just for the slash issue
>> and not spaces?
>
> This only fixes the slash issue. If your database path includes spaces
> you'll still need to put quotes in the archive_command, but it
> shouldn't be any worse than
> archive_command = 'copy "%p" "c:\someplace\%f"'
>
> I'd appreciate confirmation though from some win32 users that the above
> indeed works.
>

That's what my experimentation showed, although I worked with a tiny test.c
file rather than the archive command. I think this is the right solution -
the user should put the quotes in, not postgres.

cheers

andrew

I agree, the approach seems quite nice.

I gave the lastest cvs a go - which is probably cvs as of about 2 hours ago, as
a mingw build takes 55 minutes on this machine (compare to 6 minutes when
booted into Freebsd... ah well..)

Initially tried :

archive_command = 'copy "%p" "c:\databases\pgarchive\%f"'

But saw errors in the log about "c:databasespgarchive00000..."

Looks like '\' needs to be escaped, confirmed this with :

archive_command = 'copy "%p" "c:\\databases\\pgarchive\\%f"'

Which worked fine. However, it would be nice if the '\' inside the '"' were
'auto-escaped' somehow, as I suspect Mr 'used-windows-all-my-life' will find
the current behaviour counter-intuitive.

Otherwise looks good

Mark

Quoting Andrew Dunstan <andrew(at)dunslane(dot)net>:

> Tom Lane said:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> >> I have gotten confused by this. Does COPY work with quoted paths only
> >> if we use forward slashes, or was this fix just for the slash issue
> >> and not spaces?
> >
> > This only fixes the slash issue. If your database path includes spaces
> > you'll still need to put quotes in the archive_command, but it
> > shouldn't be any worse than
> > archive_command = 'copy "%p" "c:\someplace\%f"'
> >
> > I'd appreciate confirmation though from some win32 users that the above
> > indeed works.
> >
>
> That's what my experimentation showed, although I worked with a tiny test.c
> file rather than the archive command. I think this is the right solution -
> the user should put the quotes in, not postgres.
>
> cheers
>
> andrew
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

Funny thing is, now that the "%p" is silently using '\', the "target" path can
use '/':

e.g :

archive_command = 'copy "%p" "c:/databases/pgarchive/%f"'

*now* works fine. The next interesting question will be "can I use '/' in the
restore_command too?". I will have a muddle around with it.

regards

Mark

Quoting markir(at)coretech(dot)co(dot)nz:

> I agree, the approach seems quite nice.
>
> I gave the lastest cvs a go - which is probably cvs as of about 2 hours ago,
> as
> a mingw build takes 55 minutes on this machine (compare to 6 minutes when
> booted into Freebsd... ah well..)
>
>
> Initially tried :
>
> archive_command = 'copy "%p" "c:\databases\pgarchive\%f"'
>
> But saw errors in the log about "c:databasespgarchive00000..."
>
> Looks like '\' needs to be escaped, confirmed this with :
>
>
> archive_command = 'copy "%p" "c:\\databases\\pgarchive\\%f"'
>
> Which worked fine. However, it would be nice if the '\' inside the '"' were
> 'auto-escaped' somehow, as I suspect Mr 'used-windows-all-my-life' will find
> the current behaviour counter-intuitive.
>
> Otherwise looks good
>
> Mark
>
>
>
> Quoting Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
> > Tom Lane said:
> > > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > >> I have gotten confused by this. Does COPY work with quoted paths only
> > >> if we use forward slashes, or was this fix just for the slash issue
> > >> and not spaces?
> > >
> > > This only fixes the slash issue. If your database path includes spaces
> > > you'll still need to put quotes in the archive_command, but it
> > > shouldn't be any worse than
> > > archive_command = 'copy "%p" "c:\someplace\%f"'
> > >
> > > I'd appreciate confirmation though from some win32 users that the above
> > > indeed works.
> > >
> >
> > That's what my experimentation showed, although I worked with a tiny test.c
> > file rather than the archive command. I think this is the right solution -
> > the user should put the quotes in, not postgres.
> >
> > cheers
> >
> > andrew
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >
>
>
>
>

OK, I would like to get a 100% solution here. The change of / to \
really only handles part of our problem, and doesn't deal with
directories with spaces like "Program Files".

We need a 100% solution for Win32 for this. I know we have been around
with the quote test but we need to keep going. initdb and pg_ctl had
similar problems but we got it working 100% after some effort, and we
need to do the same here.

OK, as a data point, why does this work on XP using a CMD.EXE window:

C:\Program Files>echo > "a b"

C:\Program Files>copy "a b" "c d"
1 file(s) copied.

C:\Program Files>copy "c:/program files/c d" "e f"
1 file(s) copied.

C:\Program Files>copy "c:/program files/e f" "c:/program files/g h"
1 file(s) copied.

C:\Program Files>cmd /c copy "c:/program files/e f" "c:/program files/g h"
1 file(s) copied.

I don't think we actually need the backslash conversion. I do think we will end
up with a different GUC value for WIN32 PITR logging.

---------------------------------------------------------------------------

markir(at)coretech(dot)co(dot)nz wrote:
> Funny thing is, now that the "%p" is silently using '\', the "target" path can
> use '/':
>
> e.g :
>
> archive_command = 'copy "%p" "c:/databases/pgarchive/%f"'
>
> *now* works fine. The next interesting question will be "can I use '/' in the
> restore_command too?". I will have a muddle around with it.
>
> regards
>
> Mark
>
> Quoting markir(at)coretech(dot)co(dot)nz:
>
> > I agree, the approach seems quite nice.
> >
> > I gave the lastest cvs a go - which is probably cvs as of about 2 hours ago,
> > as
> > a mingw build takes 55 minutes on this machine (compare to 6 minutes when
> > booted into Freebsd... ah well..)
> >
> >
> > Initially tried :
> >
> > archive_command = 'copy "%p" "c:\databases\pgarchive\%f"'
> >
> > But saw errors in the log about "c:databasespgarchive00000..."
> >
> > Looks like '\' needs to be escaped, confirmed this with :
> >
> >
> > archive_command = 'copy "%p" "c:\\databases\\pgarchive\\%f"'
> >
> > Which worked fine. However, it would be nice if the '\' inside the '"' were
> > 'auto-escaped' somehow, as I suspect Mr 'used-windows-all-my-life' will find
> > the current behaviour counter-intuitive.
> >
> > Otherwise looks good
> >
> > Mark
> >
> >
> >
> > Quoting Andrew Dunstan <andrew(at)dunslane(dot)net>:
> >
> > > Tom Lane said:
> > > > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > >> I have gotten confused by this. Does COPY work with quoted paths only
> > > >> if we use forward slashes, or was this fix just for the slash issue
> > > >> and not spaces?
> > > >
> > > > This only fixes the slash issue. If your database path includes spaces
> > > > you'll still need to put quotes in the archive_command, but it
> > > > shouldn't be any worse than
> > > > archive_command = 'copy "%p" "c:\someplace\%f"'
> > > >
> > > > I'd appreciate confirmation though from some win32 users that the above
> > > > indeed works.
> > > >
> > >
> > > That's what my experimentation showed, although I worked with a tiny test.c
> > > file rather than the archive command. I think this is the right solution -
> > > the user should put the quotes in, not postgres.
> > >
> > > cheers
> > >
> > > andrew
> > >
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> > >
> >
> >
> >
> >
>
>
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

OK, next question. Why does this work when compiled in MinGW and run
from CMD.EXE:

# cat x.c
#include <stdio.h>

main()
{
system("copy \"c:/msys/1.0/home/bruce momjian/a b\" \"c:/msys/1.0/home/bruce momjian/c d\"");
}
# gcc -o x x.c
# ./x
1 file(s) copied.

C:\msys\1.0\home\Bruce Momjian>x
1 file(s) copied.

The path has forward slashes and spaces.

---------------------------------------------------------------------------

markir(at)coretech(dot)co(dot)nz wrote:
> Funny thing is, now that the "%p" is silently using '\', the "target" path can
> use '/':
>
> e.g :
>
> archive_command = 'copy "%p" "c:/databases/pgarchive/%f"'
>
> *now* works fine. The next interesting question will be "can I use '/' in the
> restore_command too?". I will have a muddle around with it.
>
> regards
>
> Mark
>
> Quoting markir(at)coretech(dot)co(dot)nz:
>
> > I agree, the approach seems quite nice.
> >
> > I gave the lastest cvs a go - which is probably cvs as of about 2 hours ago,
> > as
> > a mingw build takes 55 minutes on this machine (compare to 6 minutes when
> > booted into Freebsd... ah well..)
> >
> >
> > Initially tried :
> >
> > archive_command = 'copy "%p" "c:\databases\pgarchive\%f"'
> >
> > But saw errors in the log about "c:databasespgarchive00000..."
> >
> > Looks like '\' needs to be escaped, confirmed this with :
> >
> >
> > archive_command = 'copy "%p" "c:\\databases\\pgarchive\\%f"'
> >
> > Which worked fine. However, it would be nice if the '\' inside the '"' were
> > 'auto-escaped' somehow, as I suspect Mr 'used-windows-all-my-life' will find
> > the current behaviour counter-intuitive.
> >
> > Otherwise looks good
> >
> > Mark
> >
> >
> >
> > Quoting Andrew Dunstan <andrew(at)dunslane(dot)net>:
> >
> > > Tom Lane said:
> > > > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > >> I have gotten confused by this. Does COPY work with quoted paths only
> > > >> if we use forward slashes, or was this fix just for the slash issue
> > > >> and not spaces?
> > > >
> > > > This only fixes the slash issue. If your database path includes spaces
> > > > you'll still need to put quotes in the archive_command, but it
> > > > shouldn't be any worse than
> > > > archive_command = 'copy "%p" "c:\someplace\%f"'
> > > >
> > > > I'd appreciate confirmation though from some win32 users that the above
> > > > indeed works.
> > > >
> > >
> > > That's what my experimentation showed, although I worked with a tiny test.c
> > > file rather than the archive command. I think this is the right solution -
> > > the user should put the quotes in, not postgres.
> > >
> > > cheers
> > >
> > > andrew
> > >
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> > >
> >
> >
> >
> >
>
>
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

OK, I have a solution. It turns out that COPY and COPY.EXE behave
differently. :-O

# cat x.c
#include <stdio.h>

main()
{
system("copy.exe \"c:/msys/1.0/home/bruce momjian/a b\"
\"c:/msys/1.0/home/bruce momjian/c d\"");
}
# gcc -o x x.c
# ./x
The system cannot find the file specified.

I just changed copy to copy.exe, and bingo, failure.

I suggest we back out the backslash patch and provide a Win32 sample
archive line in postgresql.conf that quotes both args, and that we add
quotes to the Unix example too.

---------------------------------------------------------------------------

markir(at)coretech(dot)co(dot)nz wrote:
> Funny thing is, now that the "%p" is silently using '\', the "target" path can
> use '/':
>
> e.g :
>
> archive_command = 'copy "%p" "c:/databases/pgarchive/%f"'
>
> *now* works fine. The next interesting question will be "can I use '/' in the
> restore_command too?". I will have a muddle around with it.
>
> regards
>
> Mark
>
> Quoting markir(at)coretech(dot)co(dot)nz:
>
> > I agree, the approach seems quite nice.
> >
> > I gave the lastest cvs a go - which is probably cvs as of about 2 hours ago,
> > as
> > a mingw build takes 55 minutes on this machine (compare to 6 minutes when
> > booted into Freebsd... ah well..)
> >
> >
> > Initially tried :
> >
> > archive_command = 'copy "%p" "c:\databases\pgarchive\%f"'
> >
> > But saw errors in the log about "c:databasespgarchive00000..."
> >
> > Looks like '\' needs to be escaped, confirmed this with :
> >
> >
> > archive_command = 'copy "%p" "c:\\databases\\pgarchive\\%f"'
> >
> > Which worked fine. However, it would be nice if the '\' inside the '"' were
> > 'auto-escaped' somehow, as I suspect Mr 'used-windows-all-my-life' will find
> > the current behaviour counter-intuitive.
> >
> > Otherwise looks good
> >
> > Mark
> >
> >
> >
> > Quoting Andrew Dunstan <andrew(at)dunslane(dot)net>:
> >
> > > Tom Lane said:
> > > > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > >> I have gotten confused by this. Does COPY work with quoted paths only
> > > >> if we use forward slashes, or was this fix just for the slash issue
> > > >> and not spaces?
> > > >
> > > > This only fixes the slash issue. If your database path includes spaces
> > > > you'll still need to put quotes in the archive_command, but it
> > > > shouldn't be any worse than
> > > > archive_command = 'copy "%p" "c:\someplace\%f"'
> > > >
> > > > I'd appreciate confirmation though from some win32 users that the above
> > > > indeed works.
> > > >
> > >
> > > That's what my experimentation showed, although I worked with a tiny test.c
> > > file rather than the archive command. I think this is the right solution -
> > > the user should put the quotes in, not postgres.
> > >
> > > cheers
> > >
> > > andrew
> > >
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> > >
> >
> >
> >
> >
>
>
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Quoting Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>:
They work because your working directory is c:\Program Files\
Try the last 3 tests again, but cd to c:\ (for example) first (they will fail
then).

>
> OK, as a data point, why does this work on XP using a CMD.EXE window:
>
> C:\Program Files>echo > "a b"
>
> C:\Program Files>copy "a b" "c d"
> 1 file(s) copied.
>
> C:\Program Files>copy "c:/program files/c d" "e f"
> 1 file(s) copied.
>
> C:\Program Files>copy "c:/program files/e f" "c:/program files/g h"
> 1 file(s) copied.
>
> C:\Program Files>cmd /c copy "c:/program files/e f" "c:/program files/g h"
> 1 file(s) copied.
>
> I don't think we actually need the backslash conversion. I do think we will
>

Bruce Momjian said:
>
> OK, next question. Why does this work when compiled in MinGW and run
> from CMD.EXE:
>
> # cat x.c
> #include <stdio.h>
>
> main()
> {
> system("copy \"c:/msys/1.0/home/bruce momjian/a b\"
> \"c:/msys/1.0/home/bruce momjian/c d\"");
> }
> # gcc -o x x.c
> # ./x
> 1 file(s) copied.
>
> C:\msys\1.0\home\Bruce Momjian>x
> 1 file(s) copied.
>
> The path has forward slashes and spaces.
>
>

on XP Pro a similar test gives me:

C:\msys\1.0\home\adunstan>copytry
copy "c:/tmp/a b" "c:/tmp/c d"
The system cannot find the file specified.
0 file(s) copied.

C:\msys\1.0\home\adunstan>dir c:\tmp
Volume in drive C has no label.
Volume Serial Number is D899-679E

Directory of c:\tmp

08/10/2004 10:36 PM <DIR> .
08/10/2004 10:36 PM <DIR> ..
06/14/2004 05:33 PM 9,244 a b
06/14/2004 05:33 PM 9,244 INSTALL.LOG
2 File(s) 18,488 bytes
2 Dir(s) 51,230,498,816 bytes free

but with backslashes, I get:

C:\msys\1.0\home\adunstan>copytry
copy "c:\tmp\a b" "c:\tmp\c d"
1 file(s) copied.

and with backslashes in the source only I get:

C:\msys\1.0\home\adunstan>copytry
copy "c:\tmp\a b" "c:/tmp/c d"
1 file(s) copied.

go figure ...

cheers

andrew

Bruce Momjian said:
>
> OK, I have a solution. It turns out that COPY and COPY.EXE behave
> differently. :-O
>
> # cat x.c
> #include <stdio.h>
>
> main()
> {
> system("copy.exe \"c:/msys/1.0/home/bruce momjian/a b\"
> \"c:/msys/1.0/home/bruce momjian/c d\"");
> }
> # gcc -o x x.c
> # ./x
> The system cannot find the file specified.
>
> I just changed copy to copy.exe, and bingo, failure.
>
> I suggest we back out the backslash patch and provide a Win32 sample
> archive line in postgresql.conf that quotes both args, and that we add
> quotes to the Unix example too.

There is no copy.exe - it's a command builtin to cmd.exe, at least on my
system.
Before jumping to a conclusion that we have an answer we need to make sure
it works portably across all the Windows versions we are working with.

My testing and Mark's experience suggest that the source path needs to be
backslashed but the destination path maybe doesn't - which would actually
work quite well for us *with* the backslash patch.

cheers

andrew

markir(at)coretech(dot)co(dot)nz wrote:
> Quoting Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>:
> They work because your working directory is c:\Program Files\
> Try the last 3 tests again, but cd to c:\ (for example) first (they will fail
> then).

Wow, that is strange. Let me test more tomorrow.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

That problem is because there is no copy.exe, it's part of cmd. I am afraid I
introduced that confusion while trying out all sorts of things in
archive_command....

I suspect that your example may be working because of your current directory is
where your test files happen to be (the business of quoted forward slash paths
working *if* they point to your current working directory is very odd).

Consistency is generally lacking commands as well - e.g:

C:\Databases>cd "c:/program files"
The system cannot find the path specified.

C:\Databases>dir "c:/program files"
Volume in drive C has no label.
Volume Serial Number is F444-3F9F

Directory of c:\program files

11/08/2004 15:02 <DIR> .
11/08/2004 15:02 <DIR> ..
24/08/2003 03:13 <DIR> Accessories
23/08/2003 18:20 <DIR> ATI Technologies
08/09/2003 17:22 <DIR> CheckPoint
07/08/2004 14:18 <DIR> Common Files

Mark
Quoting Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>:

>
> OK, I have a solution. It turns out that COPY and COPY.EXE behave
> differently. :-O
>
> # cat x.c
> #include <stdio.h>
>
> main()
> {
> system("copy.exe \"c:/msys/1.0/home/bruce momjian/a b\"
> \"c:/msys/1.0/home/bruce momjian/c d\"");
> }
> # gcc -o x x.c
> # ./x
> The system cannot find the file specified.
>
> I just changed copy to copy.exe, and bingo, failure.
>
> I suggest we back out the backslash patch and provide a Win32 sample
> archive line in postgresql.conf that quotes both args, and that we add
> quotes to the Unix example too.
>
> ---------------------------------------------------------------------------
>
> markir(at)coretech(dot)co(dot)nz wrote:
> > Funny thing is, now that the "%p" is silently using '\', the "target" path
> can
> > use '/':
> >
> > e.g :
> >
> > archive_command = 'copy "%p" "c:/databases/pgarchive/%f"'
> >
> > *now* works fine. The next interesting question will be "can I use '/' in
> the
> > restore_command too?". I will have a muddle around with it.
> >
> > regards
> >
> > Mark
> >
> > Quoting markir(at)coretech(dot)co(dot)nz:
> >
> > > I agree, the approach seems quite nice.
> > >
> > > I gave the lastest cvs a go - which is probably cvs as of about 2 hours
> ago,
> > > as
> > > a mingw build takes 55 minutes on this machine (compare to 6 minutes when
> > > booted into Freebsd... ah well..)
> > >
> > >
> > > Initially tried :
> > >
> > > archive_command = 'copy "%p" "c:\databases\pgarchive\%f"'
> > >
> > > But saw errors in the log about "c:databasespgarchive00000..."
> > >
> > > Looks like '\' needs to be escaped, confirmed this with :
> > >
> > >
> > > archive_command = 'copy "%p" "c:\\databases\\pgarchive\\%f"'
> > >
> > > Which worked fine. However, it would be nice if the '\' inside the '"'
> were
> > > 'auto-escaped' somehow, as I suspect Mr 'used-windows-all-my-life' will
> find
> > > the current behaviour counter-intuitive.
> > >
> > > Otherwise looks good
> > >
> > > Mark
> > >
> > >
> > >
> > > Quoting Andrew Dunstan <andrew(at)dunslane(dot)net>:
> > >
> > > > Tom Lane said:
> > > > > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > > >> I have gotten confused by this. Does COPY work with quoted paths
> only
> > > > >> if we use forward slashes, or was this fix just for the slash issue
> > > > >> and not spaces?
> > > > >
> > > > > This only fixes the slash issue. If your database path includes
> spaces
> > > > > you'll still need to put quotes in the archive_command, but it
> > > > > shouldn't be any worse than
> > > > > archive_command = 'copy "%p" "c:\someplace\%f"'
> > > > >
> > > > > I'd appreciate confirmation though from some win32 users that the
> above
> > > > > indeed works.
> > > > >
> > > >
> > > > That's what my experimentation showed, although I worked with a tiny
> test.c
> > > > file rather than the archive command. I think this is the right
> solution -
> > > > the user should put the quotes in, not postgres.
> > > >
> > > > cheers
> > > >
> > > > andrew
> > > >
> > > >
> > > >
> > > >
> > > > ---------------------------(end of
> broadcast)---------------------------
> > > > TIP 1: subscribe and unsubscribe commands go to
> majordomo(at)postgresql(dot)org
> > > >
> > >
> > >
> > >
> > >
> >
> >
> >
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>

Andrew Dunstan wrote:
> Bruce Momjian said:
> >
> > OK, I have a solution. It turns out that COPY and COPY.EXE behave
> > differently. :-O
> >
> > # cat x.c
> > #include <stdio.h>
> >
> > main()
> > {
> > system("copy.exe \"c:/msys/1.0/home/bruce momjian/a b\"
> > \"c:/msys/1.0/home/bruce momjian/c d\"");
> > }
> > # gcc -o x x.c
> > # ./x
> > The system cannot find the file specified.
> >
> > I just changed copy to copy.exe, and bingo, failure.
> >
> > I suggest we back out the backslash patch and provide a Win32 sample
> > archive line in postgresql.conf that quotes both args, and that we add
> > quotes to the Unix example too.
>
> There is no copy.exe - it's a command builtin to cmd.exe, at least on my
> system.
> Before jumping to a conclusion that we have an answer we need to make sure
> it works portably across all the Windows versions we are working with.
>
> My testing and Mark's experience suggest that the source path needs to be
> backslashed but the destination path maybe doesn't - which would actually
> work quite well for us *with* the backslash patch.
>

I saw some tests using copy.exe and thought they all did. I see now
that was just one test. Now that I have the setup I will try some more
combinations tomorrow.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

markir(at)coretech(dot)co(dot)nz wrote:
> That problem is because there is no copy.exe, it's part of cmd. I am afraid I
> introduced that confusion while trying out all sorts of things in
> archive_command....

Yea.

> I suspect that your example may be working because of your current directory is
> where your test files happen to be (the business of quoted forward slash paths
> working *if* they point to your current working directory is very odd).

Wow, strange. OK, at least I know how to test now.

> Consistency is generally lacking commands as well - e.g:
>
> C:\Databases>cd "c:/program files"
> The system cannot find the path specified.
>
> C:\Databases>dir "c:/program files"
> Volume in drive C has no label.
> Volume Serial Number is F444-3F9F
>
> Directory of c:\program files
>
> 11/08/2004 15:02 <DIR> .
> 11/08/2004 15:02 <DIR> ..
> 24/08/2003 03:13 <DIR> Accessories
> 23/08/2003 18:20 <DIR> ATI Technologies
> 08/09/2003 17:22 <DIR> CheckPoint
> 07/08/2004 14:18 <DIR> Common Files

Wow, that is amazing. I saw that with DIR that I could do 'DIR "C:/"'.

Even if I don't change anything I need to document this in the code.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

>OK, I would like to get a 100% solution here. The change of / to \
>really only handles part of our problem, and doesn't deal with
>directories with spaces like "Program Files".
>
>We need a 100% solution for Win32 for this. I know we have been around
>with the quote test but we need to keep going. initdb and pg_ctl had
>similar problems but we got it working 100% after some effort, and we
>need to do the same here.
>
>
>

Actually, it appears to me that we have a 100% solution. The user can
build quotes into the command quite easily, so that handles the spaces
issue, and we do know that backslashes always work, so expanding %p with
backslashes handles that part of it.

(Side note about the patch that was applied: the system-dependent path
representation probably belongs in path.c, so we could avoid those
#ifdef WIN32 bits that Tom hates so much ... )

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Actually, it appears to me that we have a 100% solution.

Yeah --- "friends don't let friends use Windows" ...

Is there any part of this discussion that does not amount to documenting
Redmond's bugs?

I'm perfectly happy to back out the slash-to-backslash conversion I
committed a bit ago, but I'm worried about what else is coming down
the pike.

regards, tom lane

On 10 Aug 2004 at 22:59, Andrew Dunstan wrote:

>
> C:\msys\1.0\home\adunstan>copytry
> copy "c:\tmp\a b" "c:\tmp\c d"
> 1 file(s) copied.
>
> and with backslashes in the source only I get:
>
> C:\msys\1.0\home\adunstan>copytry
> copy "c:\tmp\a b" "c:/tmp/c d"
> 1 file(s) copied.
>
> go figure ...

According to MSDN: "forward slashes work fine in most cases", the
usual non-commital stuff from MS

One thing is for certain, if you want to use forward slashes (/) then you
will *have* to quote them. The (/) on windows (DOS!) is also a switch
delimiter. If you don't quote paths containing (/) then confusion will be
abundant.

Also if you want to use backslash (\) then you will need to escape it if it
is a "C" string.

Sadly I'm not convinced there is a single solution fits all if you want to
use shell (system()) commands under Windows. It just contains so
much historical baggage (DOS) that you can't be 100% sure that it will
work in all cases. Just because a small set of tests using the copy
command work, I'm not sure other commands will!

If you need stuff that is critical to the operation of Postgres that you
need to work whatever, then I say some small utilities (copy.exe?) that
work in a predicatable way need to be written to ensure this.

For non-critcal stuff documentation is needed to tell the user that they
may need to experiment with command formats.

Cheers,
Gary.

Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > Actually, it appears to me that we have a 100% solution.
>
> Yeah --- "friends don't let friends use Windows" ...
>
> Is there any part of this discussion that does not amount to documenting
> Redmond's bugs?
>
> I'm perfectly happy to back out the slash-to-backslash conversion I
> committed a bit ago, but I'm worried about what else is coming down
> the pike.

I did enough tests to realize that we have done the best we can with
Win32 COPY. It is OK with forward slashes in the second argument but
not the first.

The attached applied patch cleans up our implementation and documents
its purpose. I also added some examples for 'archive_command'.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> The attached applied patch cleans up our implementation and documents
> its purpose. I also added some examples for 'archive_command'.

If you're going to do that, do it in both places ... you missed
pgarch.c.

Also, I would say that the large block comment belongs with
make_native_path, not with its caller.

> ! #
> # If archive_command is '' then archiving is disabled. Otherwise, set it
> ! # to a command to copy a file to the proper place. Any %p in the string
> ! # is replaced by the absolute path of the file to archive, while any %f is
> ! # replaced by the file name only. NOTE: it is important for the command to
> ! # return zero exit status only if it succeeds.
> ! #
> ! # Examples:
> ! # archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
> ! # archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Win32
> !

This text should probably be removed from postgresql.conf.sample
altogether; we are not in the habit of providing more than one-line
documentation there. I stuck it in there as a quick and dirty thing
because we didn't have archive_command in the SGML docs at the time.
But now we do, and I'd say this info should be moved to runtime.sgml.

regards, tom lane

OK, patch attached and applied.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > The attached applied patch cleans up our implementation and documents
> > its purpose. I also added some examples for 'archive_command'.
>
> If you're going to do that, do it in both places ... you missed
> pgarch.c.
>
> Also, I would say that the large block comment belongs with
> make_native_path, not with its caller.
>
> > ! #
> > # If archive_command is '' then archiving is disabled. Otherwise, set it
> > ! # to a command to copy a file to the proper place. Any %p in the string
> > ! # is replaced by the absolute path of the file to archive, while any %f is
> > ! # replaced by the file name only. NOTE: it is important for the command to
> > ! # return zero exit status only if it succeeds.
> > ! #
> > ! # Examples:
> > ! # archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
> > ! # archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Win32
> > !
>
> This text should probably be removed from postgresql.conf.sample
> altogether; we are not in the habit of providing more than one-line
> documentation there. I stuck it in there as a quick and dirty thing
> because we didn't have archive_command in the SGML docs at the time.
> But now we do, and I'd say this info should be moved to runtime.sgml.
>
> regards, tom lane
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073