Re: invalid data in file backup_label problem on windows

On 3/21/21 10:40 AM, Magnus Hagander wrote:
> On Sat, Mar 20, 2021 at 3:10 AM wangsh(dot)fnst(at)fujitsu(dot)com
> <wangsh(dot)fnst(at)fujitsu(dot)com> wrote:
>>
>> David Steele <david(at)pgmasters(dot)net> wrote:
>>
>>> It's not clear to me what text editors have to do with this? Are you
>>> editing the file manually?
>>
>> When I execute SELECT * FROM pg_stop_backup(false, true) in psql.
>>
>> The results will be shown like:
>> lsn | labelfile | spcmapfile
>> ------------+---------------------------------------------------------------------+------------
>> 0/2000138 | START WAL LOCATION: 0/2000028 (file 000000010000000000000002)+|
>> | CHECKPOINT LOCATION: 0/2000060 +|
>> | BACKUP METHOD: streamed +|
>> | BACKUP FROM: master +
>> ......
>> The results only will be shown on screen and this function will not generate any files. What I do is write
>> the second field(labelfile) to a new file backup_label and write the third field(spcmapfile) to tablespace_map if
>> the third field is not null.
>>
>> Therefore, I choose a text editor to help me write the file.
>> I copy the a line in second field and paste this to text editor and press the 'enter' key, repeat this action util
>> all the line have be pasted to text editor, then save the file.
>>
>> If this is not a good way to create the backup_label file, can you tell me how can I create this file on windows?
>
> These APIs are really not designed to be run manually from a CLI and
> copy/paste the results.
>
> Running them from literally any script or program should make that
> easy, by getting the actual value out and storing it.

You might consider using pg_basebackup, which does all this for you and
is well tested.

>> I think the real problem is this file on windows is opened with binary mode. If I use libpq to get the result and write
>> the result to file directly(the default action on windows is open file in text mode), this problem will be happened.
>> So I consider this is a bug.
>
> No, the problem is you are using copy/paste and in doing so you are
> *changing'* the value that is being returned. You'll either need to
> update your copy/paste procedure to not mess with the newlines, or to
> use a better way to get the data out.
>
> If we need to clarify that in the documentation, I'm fine with that.
> Maybe add an extra sentence to the part about not modifying the output
> to mention that this includes changing newslines and also encoding
> (which would also break it, if you managed to find a non-ascii
> compatible encoding). Maybe even something along the line of "the
> contents have to be written in binary mode"?

Perhaps something like the attached?

Regards,
--
-David
david(at)pgmasters(dot)net