Content-Type: multipart/alternative;
 boundary="------------5daeRJKvL8XqUU9BRmNXaDkV"
Message-ID: <5169f6d5-240e-49ec-9265-ddafa6e863bd@dunslane.net>
Date: Tue, 9 Jul 2024 12:01:17 -0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pg_rewind fails on Windows where tablespaces are used
To: Michael Paquier <michael@paquier.xyz>,
 Chris Travers <chris.travers@stormatics.tech>
Cc: Farooq Rashed <farooq.rashed@desc.gov.ae>, pgsql-bugs@lists.postgresql.org
References: 
 <CA+pA0kLc5VxOaO4WfLjmh7W0V+quVvVtT5CaRVRAZMuh0zft4Q@mail.gmail.com>
 <Zl6dsK5IcNGTL0qD@paquier.xyz>
From: Andrew Dunstan <andrew@dunslane.net>
Content-Language: en-US
Autocrypt: addr=andrew@dunslane.net; keydata=
 xsBNBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAHNJEFuZHJldyBEdW5zdGFuIDxhbmRyZXdAZHVuc2xhbmUubmV0PsLAmwQTAQgARQIbAwIX
 gAIZAQULCQgHAgMiAgEGFQoJCAsCBBYCAwECHgcWIQTkPlhGHfx8v0RpFaWZ+n/LWfw7gQUC
 ZFlxxwUJGVGAbgAKCRCZ+n/LWfw7gXikB/9ZdcUy6CTBFIIuL/bVsc1eLEW/gJBjJBF6HxNY
 xgEkAgXAp4Lg4A5U+QB9GouFr7+GYxF0BU4hzoGhNPUWltxnHdMWP8nC/38LAqgMi8L/bbsm
 HW5YPBdWYaAZAPJQVfOAgjTbRUb26KSprpyrrJKW0ZmrZfjhNPcQ72jpWzoPLQqx2X6B0fru
 1jq+cBh8lb6r1mJTim1T3JIn+F/v5VpdQS+EL8xqsHkfzKjIPsW3CIXpkypSk6saA55Rkkbl
 26AW8ftPVB0Q6Lnn6FLt9CP0MGNixBQ55yq8r1K+nCBvCCjvQjM8RDm0UUum0WNl+ifQgTLO
 E8TWEnwVtkBf+3QWzsBNBE7KWFkBCADRnOM0FCzsYW6jtncg+dWIagjUZpvaClmqn/sJluLa
 Q3v1VXMQJzYs3eC1gh386W+XBwLRpDj3jzH81lX+p73Re3d3oJW7X+ffsxuzu5ZVdMUkqBYo
 nkAbKxr6gyJ12F/+JkUVzLcoTN+d/7YsQvUVi7NaKH8mJgjz112O4fUe3p9wfAaFa0RXHc5S
 GPzRTYRRlv/XZBIho4J2tkZOnteZJZ+GbxQVlINt6fd8P6al3MWOvpP/ExJPguEfjOsO6Njy
 xjo3WfpD4lHMOR/Oc3/8mScEF84rF2jXbsFgelWnbPWAvXY+pD0dXOFRkagGmC/viwBDqq5b
 5tk76kKmUbZxABEBAAHCwHwEGAEIACYCGwwWIQTkPlhGHfx8v0RpFaWZ+n/LWfw7gQUCZFlx
 5wUJGVGAjgAKCRCZ+n/LWfw7gf+iB/4g8CPY5jihf5r/8EsoIGe2H+dpVmpPF8YGBzTIvCz/
 fQoOq8AX/pE76QEuFnFZWfjw+wgBXgCVmkox2Eflkk6z4ND3pcwGZ6CfCxTQCDk/dij+2DQ4
 6bmDCy/sBgcbz9mTpoLC11HLoPae6YN9nBNQRZDcEFEu54OaVOqlIdbA6m+POIBCXZdHOFc0
 WoDTgxHRzC1jgQNidyd6tKqcsVJs0dzF0oKTmFFmUAqTdJO12LBuNA1rlqrR3EtpYk8B/wtS
 5dIMD7Q8hwQpL+4C6GNpb6ZKnPkLi47pDOLhz2qBrqN+rqUEsT3YnExYpzj5yOBi+FlmV1Hw
 49QYe1sn2ZPs
In-Reply-To: <Zl6dsK5IcNGTL0qD@paquier.xyz>
Archived-At: 
 <https://www.postgresql.org/message-id/5169f6d5-240e-49ec-9265-ddafa6e863bd%40dunslane.net>
Precedence: bulk

This is a multi-part message in MIME format.
--------------5daeRJKvL8XqUU9BRmNXaDkV
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit


On 2024-06-04 Tu 12:53 AM, Michael Paquier wrote:
> On Wed, May 08, 2024 at 03:02:21PM +0700, Chris Travers wrote:
>> Setup is PostgreSQL on Windows with a tablespace on a separate drive.  When
>> I go to run pg_rewind it consistently fails with the following error:
> (Chris has poked me regarding this issue last week in Vancouver.)
>
>> pg_rewind: servers diverged at WAL location 39B/7EC6F60 on timeline 2
>> pg_rewind: rewinding from last common checkpoint at 39B/7E8E3F8 on timeline
>> 2
>> pg_rewind: error: file "pg_tblspc/34244696" is of different type in source
>> and target
>>
>> The file is confirmed to be a JUNCTION to the correct location on both the
>> source and target.  So the error looks like a problem interacting with
>> Windows and detecting JUNCTION types in this case.


An EDB customer has encountered the same issue. They are not using 
in-place tablespaces.

I have reproduced this problem on release 15, not using an in-place 
tablespace.

The solution I came up with was to backpatch commits c5cb8f3b, 387803d8 
and 5fc88c5d53.

I don't think we need to do anything relating to in-place tablespaces. 
These are documented as a developer only option and not for production.

The only question in my mind is whether those patches should be 
backpatched. It's a couple of hundred lines, and I think it's safe, but 
I'd welcome other opinions. If we are going to backpatch them we should 
also look at adding to adding tests for use of tablespaces with 
pg_rewind on the back branches. Ideally we'd get this done in time for 
the next maintenance release.


cheers


andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com

--------------5daeRJKvL8XqUU9BRmNXaDkV
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 2024-06-04 Tu 12:53 AM, Michael
      Paquier wrote:<br>
    </div>
    <blockquote type="cite" cite="mid:Zl6dsK5IcNGTL0qD@paquier.xyz">
      <pre class="moz-quote-pre" wrap="">On Wed, May 08, 2024 at 03:02:21PM +0700, Chris Travers wrote:
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">Setup is PostgreSQL on Windows with a tablespace on a separate drive.  When
I go to run pg_rewind it consistently fails with the following error:
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
(Chris has poked me regarding this issue last week in Vancouver.)

</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">pg_rewind: servers diverged at WAL location 39B/7EC6F60 on timeline 2
pg_rewind: rewinding from last common checkpoint at 39B/7E8E3F8 on timeline
2
pg_rewind: error: file "pg_tblspc/34244696" is of different type in source
and target

The file is confirmed to be a JUNCTION to the correct location on both the
source and target.  So the error looks like a problem interacting with
Windows and detecting JUNCTION types in this case.
</pre>
      </blockquote>
    </blockquote>
    <p><br>
    </p>
    <p>An EDB customer has encountered the same issue. They are not
      using in-place tablespaces.<br>
    </p>
    <p>I have reproduced this problem on release 15, not using an
      in-place tablespace.</p>
    <p>The solution I came up with was to backpatch commits <font
        face="monospace">c5cb8f3b, 387803d8 and 5fc88c5d53.</font></p>
    <p><font face="monospace">I don't think we need to do anything
        relating to in-place tablespaces. These are documented as a
        developer only option and not for production.<br>
      </font></p>
    <p>The only question in my mind is whether those patches should be
      backpatched. It's a couple of hundred lines, and I think it's
      safe, but I'd welcome other opinions. If we are going to backpatch
      them we should also look at adding to adding tests for use of
      tablespaces with pg_rewind on the back branches. Ideally we'd get
      this done in time for the next maintenance release.<br>
    </p>
    <p><br>
    </p>
    <p>cheers</p>
    <p><br>
    </p>
    <p>andrew<br>
    </p>
    <span style="white-space: pre-wrap">
</span>
    <pre class="moz-signature"
    signature-switch-id="d0437855-2267-4610-80b3-83167ec45b0b" cols="72">--
Andrew Dunstan
EDB: <a class="moz-txt-link-freetext" href="https://www.enterprisedb.com">https://www.enterprisedb.com</a>
</pre>
  </body>
</html>

--------------5daeRJKvL8XqUU9BRmNXaDkV--