From: "Frans Simmelvuo (Papula-Nevinpat)"
	<Frans.Simmelvuo@papula-nevinpat.com>
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: pg_basebackup "Permission denied" error when initiating streaming
 replication
Thread-Topic: pg_basebackup "Permission denied" error when initiating
 streaming replication
Thread-Index: AdqgZPvkOs4nEgynRpKxVoKO3HinrA==
Date: Tue, 7 May 2024 10:28:23 +0000
Message-ID:
 <GV1PR01MB85793D04E48B1855BCA755ABA7E42@GV1PR01MB8579.eurprd01.prod.exchangelabs.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?us-ascii?Q?2btPSOeUEje1wGwbx6FYegsjT+dyCSexFdt2or9rYPcxzOd2C7NNCKJkZq0H?=
 =?us-ascii?Q?W6qOfG1FfNCSCkswrQX1c8yqmfOweNvby20uu/6VT4GWxCDk6DpaBPuiJy+C?=
 =?us-ascii?Q?v0SW7mGxKOttjDjVxxEP6EVQoI4aIbW5422JTgQEWnYXWBjWJ8n79B0GOKC4?=
 =?us-ascii?Q?b0b3B7/6no0JQTnDpY5om7FgUzoBXiH1BdlGOO4DusVavfusJY0+lC+BymaM?=
 =?us-ascii?Q?h1xKDE3tf6oLQ4E6Hkn+TgvP46V3Z3b5uEUDrhtoamlUdYG+LWguISurviRC?=
 =?us-ascii?Q?FDkVENy4LBboCXau0jqeZq2DDCIUGURrX7JQiz+ytVHogzvPBf2sZk3hgYEh?=
 =?us-ascii?Q?U5lVzKRGBv6aDTgmKdqlGZg4/8910+QooBHTiltwq9rlK5Gu7jOjepolmu3Y?=
 =?us-ascii?Q?L/0SL3nekWB0BOHyGx6HJma63Bn3EQYn4Eq0FSafQGX/49v56Z3GfnGXidf+?=
 =?us-ascii?Q?t5XAPyXaLCP0ZaiB1rJYtTetp9c73w1SzvHdOhxYlEJoynLN7dzjop1gBg9U?=
 =?us-ascii?Q?w/2HlbMbHzJIoVGu9C90l8f5qHnQ4jjObp1ufxlC/TRibZEaAKll6CKqBoAl?=
 =?us-ascii?Q?7QwaC0LR2VSOBCl2RzD3bdzfY+PN/aZSTfkOlDm6+EbQQkIOKjo1Z0b06tPn?=
 =?us-ascii?Q?OECt3PQBvqAHCeFgmIBDLN/qZjYws2gRVhxeXRiyxLzI9dXa19C50y0/Xb//?=
 =?us-ascii?Q?vo/HCgLs6MtxbYFME0h5mGGen54zkrVk4eauL5x+2+LVGpzwh96moenl8vzj?=
 =?us-ascii?Q?Vtbnm4SaPTXe4TXUmBhfCl/c0uU9KEOq2SEtEOhhIBiqn9Q8AgOjQfkP2kX4?=
 =?us-ascii?Q?PZqlRAG/8L08cVgfHqQr+Dair/F2xoXDdrnNiuLvKofAy16LRFVG/8mmlRb9?=
 =?us-ascii?Q?wmymBMLvf7gGwiUHFRZ1vT+TpCcOBUbVDnMBC1oG64Q2dDO3dzo0gojkbKtS?=
 =?us-ascii?Q?WJwqE/I9GMCkMyiUj+RkUHB+12ibe9sdgoXzDJ16PDoWou92PHrIyFOFs68P?=
 =?us-ascii?Q?xqkI8gnQLcoAUyHRIYcd3iXmc1ZLHoa4cXzbkThKP7osOe5K5JhLMernin5i?=
 =?us-ascii?Q?GboTMpTuIkS6BQqmGDwDkzaw1fgG24SVeYxzzENCu+rTkRHH3ywxCfNuYma5?=
 =?us-ascii?Q?PwG+Lgiz8yarZrATdv6JhGWjhsBgkxRSdJyCQ2dfpOfflpU6+NyIqlvrmgES?=
 =?us-ascii?Q?QASAe4VnM4QZ9Ij+rrOLpgnfFdsxXacZJ1QVl2dtL29hj/66DB2Am12mjmIk?=
 =?us-ascii?Q?zkdlsuLw02l69ux8kd5cvAeSt9mdtoGMwLsvAWShMaYbytTn5Vjp67S5yCCj?=
 =?us-ascii?Q?J8uA4fWSdVzen0Hc5UytVAb/88a/n8Rki4BDJvtvH7XPJUixFnGjhv4wRz8x?=
 =?us-ascii?Q?d5LxpEpkZUoyA/qZNRZsISzWoJm0SWzxAZU57QR4U6+zeKi3XGRe+GhKDy43?=
 =?us-ascii?Q?3bzvN+BIhJHOiUNeS3Hn6cJEleML/359oG0ryyRAc8M/QXIULrqMBndkhXHO?=
 =?us-ascii?Q?MePm4s4912o59XxY/YIq0UyxAwpk9uFE3MzJC6C92O17v4QDLHAWKKokg7+G?=
 =?us-ascii?Q?w4C9IRhXiI+xPQ4mhfkLViazV6yINuvN+b86anMIuYqK6PrbTddoZzGLsizq?=
 =?us-ascii?Q?XQ=3D=3D?=
Content-Type: multipart/alternative;
	boundary="_000_GV1PR01MB85793D04E48B1855BCA755ABA7E42GV1PR01MB8579eurp_"
MIME-Version: 1.0
X-OriginatorOrg: papula-nevinpat.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: GV1PR01MB8579.eurprd01.prod.exchangelabs.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 3b09237b-696f-46e3-ee18-08dc6e806c4c
X-MS-Exchange-CrossTenant-originalarrivaltime: 07 May 2024 10:28:23.1019
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 2784e481-228c-4a27-a9b6-e4f9b51d2882
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: rhnDUf2OTrsbE1CcDWCWPlDDNCPIlpa7whvbkQLpwCOFAotrkI6tWZydAKTGJAEQ/iZxvzhJAxNgp6TCQCBZ2GCkvb09J9HLtm35D0t9phd9oRW6gy3MQMlOo5FyKeaJ
X-MS-Exchange-Transport-CrossTenantHeadersStamped: AM0PR01MB6402
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/GV1PR01MB85793D04E48B1855BCA755ABA7E42%40GV1PR01MB8579.eurprd01.prod.exchangelabs.com>
Precedence: bulk

--_000_GV1PR01MB85793D04E48B1855BCA755ABA7E42GV1PR01MB8579eurp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi,

I'm trying to initiate streaming replication between two Postgres 10 server=
s on Windows. The source cluster is of size ~450gb, and the cluster consist=
s of the primary data folder, and two separated tablespaces. One of the tab=
lespaces is configured with a symbolic link inside Windows.

Now, the problem I'm encountering is the following:
I call pg_basebackup at the target as follows:
>> pg_basebackup -h <SRC_IP> -U <SUPERUSER> -p <SRC_PORT> -W -D <TARGETDIR>=
 -T "C:\Server Files\Data\ProgramsData\PostgreSQL\10\data\base\demo_tablesp=
ace"=3DE:\PG_tablespace_data\10\demo_tablespace -T "C:\Server Files\Data\Pr=
ogramsData\PostgreSQL\10\data\base\tsb_tablespace"=3DE:\PG_tablespace_data\=
10\tsb_tablespace -P -R -v

This starts the pg_basebackup and it runs without problems for the duration=
 of copying the 450gbs of data.
Then the output is the following:

>>pg_basebackup: initiating base backup, waiting for checkpoint to complete
>>pg_basebackup: checkpoint complete
>>pg_basebackup: write-ahead log start point: 181/9000028 on timeline 1
>>pg_basebackup: starting background WAL receiver
>>432917950/482036946 kB (100%), 3/3 tablespaces
>>pg_basebackup: could not get write-ahead log end position from server: ER=
ROR: could not open file "./base/demo_tablespace": Permission denied

The demo_tablespace is the one that is a symbolic link from another folder.=
 After the error, I find that all the data is copied to the target tablespa=
ces and primary data directory, but the data directory is missing all pg_..=
. directories and configuration files. So no running database at target.

I'm running the source Postgres service on Windows as a local account, the =
command is given with administrator cmd and the ./base/demo_tablespace fold=
er's security settings should have all needed permissions for the operation=
.

I have a hard time understanding why pg_basebackup can retrieve the data fr=
om the ./base/demo_tablespace directory, and then suddenly gets denied. I'm=
 running out of ideas on what to try next.

BR,
Frans Simmelvuo

--_000_GV1PR01MB85793D04E48B1855BCA755ABA7E42GV1PR01MB8579eurp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;
	mso-fareast-language:EN-US;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	mso-ligatures:none;
	mso-fareast-language:EN-US;}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:70.85pt 2.0cm 70.85pt 2.0cm;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"FI" link=3D"#0563C1" vlink=3D"#954F72" style=3D"word-wrap:bre=
ak-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal"><span lang=3D"EN-US">Hi,<br>
<br>
I&#8217;m trying to initiate streaming replication between two Postgres 10 =
servers on Windows. The source cluster is of size ~450gb, and the cluster c=
onsists of the primary data folder, and two separated tablespaces. One of t=
he tablespaces is configured with a symbolic
 link inside Windows. <o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">Now, the problem I&#8217;m enco=
untering is the following:
<br>
I call pg_basebackup at the target as follows:<br>
&gt;&gt; pg_basebackup -h &lt;SRC_IP&gt; -U &lt;SUPERUSER&gt; -p &lt;SRC_PO=
RT&gt; -W -D &lt;TARGETDIR&gt; -T &#8220;C:\Server Files\Data\ProgramsData\=
PostgreSQL\10\data\base\demo_tablespace&#8221;=3DE:\PG_tablespace_data\10\d=
emo_tablespace -T &#8220;C:\Server Files\Data\ProgramsData\PostgreSQL\10\da=
ta\base\tsb_tablespace&#8221;=3DE:\PG_tablespace_data\10\tsb_tablespace
 -P -R -v<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">This starts the pg_basebackup a=
nd it runs without problems for the duration of copying the 450gbs of data.
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">Then the output is the followin=
g:<br>
<br>
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">&gt;&gt;pg_basebackup: initiati=
ng base backup, waiting for checkpoint to complete<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">&gt;&gt;pg_basebackup: checkpoi=
nt complete<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">&gt;&gt;pg_basebackup: write-ah=
ead log start point: 181/9000028 on timeline 1<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">&gt;&gt;pg_basebackup: starting=
 background WAL receiver<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">&gt;&gt;432917950/482036946 kB =
(100%), 3/3 tablespaces<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">&gt;&gt;pg_basebackup: could no=
t get write-ahead log end position from server: ERROR: could not open file =
&#8220;./base/demo_tablespace&#8221;: Permission denied<o:p></o:p></span></=
p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">The demo_tablespace is the one =
that is a symbolic link from another folder. After the error, I find that a=
ll the data is copied to the target tablespaces and primary data directory,=
 but the data directory is missing all
 pg_... directories and configuration files. So no running database at targ=
et. <br>
<br>
I&#8216;m running the source Postgres service on Windows as a local account=
, the command is given with administrator cmd and the ./base/demo_tablespac=
e folder&#8217;s security settings should have all needed permissions for t=
he operation.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">I have a hard time understandin=
g why pg_basebackup can retrieve the data from the ./base/demo_tablespace d=
irectory, and then suddenly gets denied. I&#8217;m running out of ideas on =
what to try next.
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">BR,<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">Frans Simmelvuo<o:p></o:p></spa=
n></p>
</div>
</body>
</html>

--_000_GV1PR01MB85793D04E48B1855BCA755ABA7E42GV1PR01MB8579eurp_--