pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed

BUG #16927: Postgres can`t access WAL files
29+ messages / 6 participants
[nested] [flat]

* BUG #16927: Postgres can`t access WAL files
@ 2021-03-15 14:19 PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 29+ messages in thread

From: PG Bug reporting form @ 2021-03-15 14:19 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: yarik97.6@gmail.com

The following bug has been logged on the website:

Bug reference:      16927
Logged by:          Ярослав Пашинский
Email address:      yarik97.6@gmail.com
PostgreSQL version: 13.2
Operating system:   Windows Server 2019
Description:        

Recently, I had updated 4 clusters postgreSQL from version 9.6 to 13.2, and
now have issue with errors that you can see below from log file. There is
high frequency of how this error gets occurred.
With such type of errors I can`t set up replication slot and I worry about
is it safe for data in DB cluster. 
I already took some actions to get rid of this error: 1) enabled exclusions
in GPO for Microsoft Defender (clusters directory and postgres process) 
2) checked system with sfc tool.
3) checked user & system rights for cluster directory.
But nothings from this works.
Here are some lines from log file.
2021-03-15 15:44:54.674 EET [3992] LOG:  could not rename file
"pg_wal/000000010000086E000000C6": No such file or directory
2021-03-15 15:48:38.884 EET [3992] LOG:  could not rename file
"pg_wal/000000010000086D000000E7": Permission denied
2021-03-15 15:48:49.813 EET [3992] LOG:  could not rename file
"pg_wal/000000010000086D000000FD": Permission denied
2021-03-15 15:49:00.642 EET [3992] LOG:  could not rename file
"pg_wal/000000010000086E00000033": Permission denied

Best regards, Yaroslav.



^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
@ 2021-03-16 01:20 ` Michael Paquier <michael@paquier.xyz>
  2021-03-16 01:53   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  0 siblings, 2 replies; 29+ messages in thread

From: Michael Paquier @ 2021-03-16 01:20 UTC (permalink / raw)
  To: yarik97.6@gmail.com; pgsql-bugs@lists.postgresql.org

On Mon, Mar 15, 2021 at 02:19:57PM +0000, PG Bug reporting form wrote:
> Recently, I had updated 4 clusters postgreSQL from version 9.6 to 13.2, and
> now have issue with errors that you can see below from log file. There is
> high frequency of how this error gets occurred.
> With such type of errors I can`t set up replication slot and I worry about
> is it safe for data in DB cluster. 
> I already took some actions to get rid of this error: 1) enabled exclusions
> in GPO for Microsoft Defender (clusters directory and postgres process) 
> 2) checked system with sfc tool.
> 3) checked user & system rights for cluster directory.
> But nothings from this works.
> Here are some lines from log file.
> 2021-03-15 15:44:54.674 EET [3992] LOG:  could not rename file
> "pg_wal/000000010000086E000000C6": No such file or directory
> 2021-03-15 15:48:38.884 EET [3992] LOG:  could not rename file
> "pg_wal/000000010000086D000000E7": Permission denied
> 2021-03-15 15:48:49.813 EET [3992] LOG:  could not rename file
> "pg_wal/000000010000086D000000FD": Permission denied
> 2021-03-15 15:49:00.642 EET [3992] LOG:  could not rename file
> "pg_wal/000000010000086E00000033": Permission denied

There have been multiple reports of this issue for 13, though we have
not been able to determine if this was coming from Postgres or if a
recent Windows update is causing that.  Here, you basically say that
all stable versions of PostgreSQL are seeing this problem, which is
new to me.  Particularly,  9.6.20 did not have any issues but 9.6.21
is showing this problem, right?  Or did you update from an even older
version, say 9.6.19 or 9.6.18?

Are all those instances involved with streaming replication?  What is
exactly the version of your Windows server?  d726e44f catches my
eyes here, looking at the diffs between both versions..
--
Michael

Attachments:

  [application/pgp-signature] signature.asc (832B, ../../YFAH1LbeUeXTFcEM@paquier.xyz/2-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
@ 2021-03-16 01:53   ` Michael Paquier <michael@paquier.xyz>
  1 sibling, 0 replies; 29+ messages in thread

From: Michael Paquier @ 2021-03-16 01:53 UTC (permalink / raw)
  To: yarik97.6@gmail.com; pgsql-bugs@lists.postgresql.org

On Tue, Mar 16, 2021 at 10:20:20AM +0900, Michael Paquier wrote:
> Are all those instances involved with streaming replication?  What is
> exactly the version of your Windows server?  d726e44f catches my
> eyes here, looking at the diffs between both versions..

And...  While stressing my Windows box with a pgbench this morning, I
have been able to reproduce the problem on HEAD after 20 minutes of
run, and my box is just an old VM image provided by Microsoft that has
no fancy scanner running concurrently as far as I know.  No
replication involved here, just a standalone deployment.  I'll look at
what I have.
--
Michael

Attachments:

  [application/pgp-signature] signature.asc (832B, ../../YFAPqmDl0s8ILMlS@paquier.xyz/2-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
@ 2021-03-16 08:40   ` Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  1 sibling, 1 reply; 29+ messages in thread

From: Michael Paquier @ 2021-03-16 08:40 UTC (permalink / raw)
  To: Ярослав Пашинский <yarik97.6@gmail.com>; +Cc: Postgres bugs <pgsql-bugs@lists.postgresql.org>

(Please keep pgsql-bugs in CC of this thread)

On Tue, Mar 16, 2021 at 10:16:29AM +0200, Ярослав Пашинский wrote:
> 2021-03-15 00:08:20.563 EET [9936] LOG:  could not rename temporary
> statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat":
> Permission denied

This pattern is new.

> I got this error also after updating from 9.6 to 13.2

Oh, sorry, I did not understand your last message as you used the word
"updating", which sounded like you ran a set of minor updates for
multiple servers and saw the same issue across all those versions.
But what you mean is that you did one major upgrade, from 9.6 to 13.

> So, the solution could be to rollback to 9.6 and then update to 12.X
> version?

There has been a collection of reports lately with 13.X misbehaving
when it comes to the recycling of WAL segments:
https://www.postgresql.org/message-id/3861ff1e-0923-7838-e826-094cc9bef737@hot.ee
https://www.postgresql.org/message-id/16874-c3eecd319e36a2bf@postgresql.org
https://www.postgresql.org/message-id/095ccf8d-7f58-d928-427c-b17ace23cae6@burgess.co.nz

Your report is the 4th one of its kind, and based on the data
collected up to now, 12.X or older major versions do not see the
issue.  I have begun a thread about the problem on -hackers, that's
too much to be a coincidence, and more than one version of Windows
sees the problem:
https://www.postgresql.org/message-id/YFBcRbnBiPdGZvfW@paquier.xyz
--
Michael

Attachments:

  [application/pgp-signature] signature.asc (832B, ../../YFBu8b4dH32kQwDG@paquier.xyz/2-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
@ 2021-03-16 22:30     ` Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 29+ messages in thread

From: Michael Paquier @ 2021-03-16 22:30 UTC (permalink / raw)
  To: Ярослав Пашинский <yarik97.6@gmail.com>; +Cc: Postgres bugs <pgsql-bugs@lists.postgresql.org>

Hi,

On Tue, Mar 16, 2021 at 12:40:15PM +0200, Ярослав Пашинский wrote:
> On prod server with windows server 2019 I used .zip downloaded from
> https://www.enterprisedb.com/download-postgresql-binaries and unpacked it
> to the default folder (Program Files/PostgreSQL/13). But the previous
> version (9.6) was installed via .exe installer.
> On developer server with Windows server 2016 I used an installer to install
> postgres 13, but as I said before there are the same issues on both servers.
> Are you talking about building patch from scratch? If yes, I`ll try it.

Yes, that's the idea, and it is an experience by itself to compile
the Postgres source code on Windows :)

We'd need to check after two things:
1) The code compiled from the source code of 13.2 is still able to
reproduce the issue.
2) Once the patch attached is applied on top of 13.2, check if the
problem goes away or not.

I am still running some tests on my own environments, but that's much
harder to hit for me, visibly, and the error code path complaining is
not the same.  If I may ask, what are the contents of pg_wal on the
instances where the errors happen.  Do you have some files suffixed
with ".deleted" around, or anything named like xlogtemp.N, where N is
an integer for a PID?

By the way, could you hit "reply-all" for the emails or attach
properly in CC pgsql-bugs so as everybody can see the discussion
happening here?
--
Michael

Attachments:

  [text/x-diff] 0001-Revert-Remove-HAVE_WORKING_LINK.patch (5.3K, ../../YFExaMohbb8tqM3k@paquier.xyz/2-0001-Revert-Remove-HAVE_WORKING_LINK.patch)
  download | inline diff:
From 961f9a03d4c27220c33e88402d5ef274424a0ab2 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Wed, 17 Mar 2021 07:12:35 +0900
Subject: [PATCH] Revert "Remove HAVE_WORKING_LINK"

This reverts commit aaa3aeddee51dd0058d38469907865052706a590.
---
 src/include/pg_config_manual.h        |  7 +++++++
 src/include/storage/fd.h              |  2 +-
 src/backend/access/transam/timeline.c |  4 ++--
 src/backend/access/transam/xlog.c     |  4 ++--
 src/backend/storage/file/fd.c         | 21 ++++++++++++++++-----
 5 files changed, 28 insertions(+), 10 deletions(-)

diff --git a/src/include/pg_config_manual.h b/src/include/pg_config_manual.h
index 8f3ec6bde1..966da99742 100644
--- a/src/include/pg_config_manual.h
+++ b/src/include/pg_config_manual.h
@@ -135,6 +135,13 @@
 #define EXEC_BACKEND
 #endif
 
+/*
+ * Define this if your operating system supports link()
+ */
+#if !defined(WIN32) && !defined(__CYGWIN__)
+#define HAVE_WORKING_LINK 1
+#endif
+
 /*
  * USE_POSIX_FADVISE controls whether Postgres will attempt to use the
  * posix_fadvise() kernel call.  Usually the automatic configure tests are
diff --git a/src/include/storage/fd.h b/src/include/storage/fd.h
index 8cd125d7df..2085c62b41 100644
--- a/src/include/storage/fd.h
+++ b/src/include/storage/fd.h
@@ -157,7 +157,7 @@ extern void fsync_fname(const char *fname, bool isdir);
 extern int	fsync_fname_ext(const char *fname, bool isdir, bool ignore_perm, int elevel);
 extern int	durable_rename(const char *oldfile, const char *newfile, int loglevel);
 extern int	durable_unlink(const char *fname, int loglevel);
-extern int	durable_rename_excl(const char *oldfile, const char *newfile, int loglevel);
+extern int	durable_link_or_rename(const char *oldfile, const char *newfile, int loglevel);
 extern void SyncDataDirectory(void);
 extern int	data_sync_elevel(int elevel);
 
diff --git a/src/backend/access/transam/timeline.c b/src/backend/access/transam/timeline.c
index e6a29d9a9b..27d70ff869 100644
--- a/src/backend/access/transam/timeline.c
+++ b/src/backend/access/transam/timeline.c
@@ -446,7 +446,7 @@ writeTimeLineHistory(TimeLineID newTLI, TimeLineID parentTLI,
 	 * Perform the rename using link if available, paranoidly trying to avoid
 	 * overwriting an existing file (there shouldn't be one).
 	 */
-	durable_rename_excl(tmppath, path, ERROR);
+	durable_link_or_rename(tmppath, path, ERROR);
 
 	/* The history file can be archived immediately. */
 	if (XLogArchivingActive())
@@ -524,7 +524,7 @@ writeTimeLineHistoryFile(TimeLineID tli, char *content, int size)
 	 * Perform the rename using link if available, paranoidly trying to avoid
 	 * overwriting an existing file (there shouldn't be one).
 	 */
-	durable_rename_excl(tmppath, path, ERROR);
+	durable_link_or_rename(tmppath, path, ERROR);
 }
 
 /*
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 7daa7c43ad..82e070e431 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -3624,11 +3624,11 @@ InstallXLogFileSegment(XLogSegNo *segno, char *tmppath,
 	 * Perform the rename using link if available, paranoidly trying to avoid
 	 * overwriting an existing file (there shouldn't be one).
 	 */
-	if (durable_rename_excl(tmppath, path, LOG) != 0)
+	if (durable_link_or_rename(tmppath, path, LOG) != 0)
 	{
 		if (use_lock)
 			LWLockRelease(ControlFileLock);
-		/* durable_rename_excl already emitted log message */
+		/* durable_link_or_rename already emitted log message */
 		return false;
 	}
 
diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c
index e5950b0726..ba92ceaf65 100644
--- a/src/backend/storage/file/fd.c
+++ b/src/backend/storage/file/fd.c
@@ -767,11 +767,10 @@ durable_unlink(const char *fname, int elevel)
 }
 
 /*
- * durable_rename_excl -- rename a file in a durable manner, without
- * overwriting an existing target file
+ * durable_link_or_rename -- rename a file in a durable manner.
  *
- * Similar to durable_rename(), except that this routine will fail if the
- * target file already exists.
+ * Similar to durable_rename(), except that this routine tries (but does not
+ * guarantee) not to overwrite the target file.
  *
  * Note that a crash in an unfortunate moment can leave you with two links to
  * the target file.
@@ -782,7 +781,7 @@ durable_unlink(const char *fname, int elevel)
  * valid upon return.
  */
 int
-durable_rename_excl(const char *oldfile, const char *newfile, int elevel)
+durable_link_or_rename(const char *oldfile, const char *newfile, int elevel)
 {
 	/*
 	 * Ensure that, if we crash directly after the rename/link, a file with
@@ -791,6 +790,7 @@ durable_rename_excl(const char *oldfile, const char *newfile, int elevel)
 	if (fsync_fname_ext(oldfile, false, false, elevel) != 0)
 		return -1;
 
+#ifdef HAVE_WORKING_LINK
 	if (link(oldfile, newfile) < 0)
 	{
 		ereport(elevel,
@@ -800,6 +800,17 @@ durable_rename_excl(const char *oldfile, const char *newfile, int elevel)
 		return -1;
 	}
 	unlink(oldfile);
+#else
+	/* XXX: Add racy file existence check? */
+	if (rename(oldfile, newfile) < 0)
+	{
+		ereport(elevel,
+				(errcode_for_file_access(),
+				 errmsg("could not rename file \"%s\" to \"%s\": %m",
+						oldfile, newfile)));
+		return -1;
+	}
+#endif
 
 	/*
 	 * Make change persistent in case of an OS crash, both the new entry and
-- 
2.30.2

  [application/pgp-signature] signature.asc (832B, ../../YFExaMohbb8tqM3k@paquier.xyz/3-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
@ 2021-03-16 23:56       ` Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  0 siblings, 1 reply; 29+ messages in thread

From: Michael Paquier @ 2021-03-16 23:56 UTC (permalink / raw)
  To: Ярослав Пашинский <yarik97.6@gmail.com>; +Cc: Postgres bugs <pgsql-bugs@lists.postgresql.org>

On Wed, Mar 17, 2021 at 07:30:00AM +0900, Michael Paquier wrote:
> I am still running some tests on my own environments, but that's much
> harder to hit for me, visibly, and the error code path complaining is
> not the same.  If I may ask, what are the contents of pg_wal on the
> instances where the errors happen.  Do you have some files suffixed
> with ".deleted" around, or anything named like xlogtemp.N, where N is
> an integer for a PID?

Another thing I could do here is to share links to download all the
binaries built for 13.2 and 13.2 + a patch, that you could drop into
your own servers to test what I am suspecting causes the issue.
Depending on your server policies, perhaps that's not acceptable,
though.  Just let me know which one you'd prefer.
--
Michael

Attachments:

  [application/pgp-signature] signature.asc (832B, ../../YFFFkWzunPO9M6kV@paquier.xyz/2-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
@ 2021-03-17 08:34         ` Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 29+ messages in thread

From: Ярослав Пашинский @ 2021-03-17 08:34 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Postgres bugs <pgsql-bugs@lists.postgresql.org>

I am able to run compiled binaries + patch on my servers, that not a
problem. Or I could also compile if you could tell me briefly how to do
that because it`s real useful skill :)
I attached 2 files: files_list.txt - that content of pg_wal directory;
second file file_option.png - properties of one wal file that postgres
can`t access. Strange thing is even with domain admin or local admin I
can`t see rights properties for this file.

ср, 17 мар. 2021 г. в 01:56, Michael Paquier <michael@paquier.xyz>:

> On Wed, Mar 17, 2021 at 07:30:00AM +0900, Michael Paquier wrote:
> > I am still running some tests on my own environments, but that's much
> > harder to hit for me, visibly, and the error code path complaining is
> > not the same.  If I may ask, what are the contents of pg_wal on the
> > instances where the errors happen.  Do you have some files suffixed
> > with ".deleted" around, or anything named like xlogtemp.N, where N is
> > an integer for a PID?
>
> Another thing I could do here is to share links to download all the
> binaries built for 13.2 and 13.2 + a patch, that you could drop into
> your own servers to test what I am suspecting causes the issue.
> Depending on your server policies, perhaps that's not acceptable,
> though.  Just let me know which one you'd prefer.
> --
> Michael
>
ÿþarchive_status

000000010000086E000000A9

000000010000086E000000C7

0000000100000870000000B3.deleted

000000010000087100000007.deleted

000000010000087100000034.deleted

000000010000087100000037.deleted

000000010000087100000048.deleted

0000000100000872000000C5

000000010000087300000095.deleted

0000000100000873000000A4.deleted

0000000100000873000000AE.deleted

0000000100000873000000B2.deleted

0000000100000873000000BA.deleted

0000000100000873000000C4.deleted

0000000100000873000000C6.deleted

0000000100000873000000C7.deleted

0000000100000873000000C8.deleted

0000000100000873000000C9

0000000100000873000000CA

0000000100000873000000CB

0000000100000873000000CC

0000000100000873000000CD

0000000100000873000000CE

0000000100000873000000CF

0000000100000873000000D0

0000000100000873000000D1

0000000100000873000000D2

0000000100000873000000D3

0000000100000873000000D4

0000000100000873000000D5

0000000100000873000000D6

0000000100000873000000D7

0000000100000873000000D8

0000000100000873000000D9

0000000100000873000000DA

0000000100000873000000DB

0000000100000873000000DC

0000000100000873000000DD

0000000100000873000000DE

0000000100000873000000DF

0000000100000873000000E0

0000000100000873000000E1

0000000100000873000000E2

0000000100000873000000E3

0000000100000873000000E4

0000000100000873000000E5

0000000100000873000000E6

0000000100000873000000E7

0000000100000873000000E8

0000000100000873000000E9

0000000100000873000000EA

0000000100000873000000EB

0000000100000873000000EC

0000000100000873000000ED

0000000100000873000000EE

0000000100000873000000EF

0000000100000873000000F0

0000000100000873000000F1

0000000100000873000000F2

0000000100000873000000F3

0000000100000873000000F4

0000000100000873000000F5

0000000100000873000000F6

0000000100000873000000F7

0000000100000873000000F8

0000000100000873000000F9

0000000100000873000000FA

0000000100000873000000FB

0000000100000873000000FC

0000000100000873000000FD

0000000100000873000000FE

0000000100000873000000FF

000000010000087400000000

000000010000087400000001

000000010000087400000002

000000010000087400000003

000000010000087400000004

000000010000087400000005

000000010000087400000006

000000010000087400000007

000000010000087400000008

000000010000087400000009

00000001000008740000000A

00000001000008740000000B

00000001000008740000000C

00000001000008740000000D

00000001000008740000000E

00000001000008740000000F

000000010000087400000010

000000010000087400000011

000000010000087400000012

000000010000087400000013

000000010000087400000014

000000010000087400000015

000000010000087400000016

000000010000087400000017

000000010000087400000018

000000010000087400000019

00000001000008740000001A

00000001000008740000001B

00000001000008740000001C

00000001000008740000001D

00000001000008740000001E

00000001000008740000001F

000000010000087400000020

000000010000087400000021

000000010000087400000022

000000010000087400000023

000000010000087400000024

000000010000087400000025

000000010000087400000026

000000010000087400000027

000000010000087400000028

000000010000087400000029

00000001000008740000002A

00000001000008740000002B

00000001000008740000002C

00000001000008740000002D

00000001000008740000002E

00000001000008740000002F

000000010000087400000030

000000010000087400000031

000000010000087400000032

000000010000087400000033

000000010000087400000034

000000010000087400000035

000000010000087400000036

000000010000087400000037

000000010000087400000038

000000010000087400000039

00000001000008740000003A

00000001000008740000003B

00000001000008740000003C

00000001000008740000003D

00000001000008740000003E

00000001000008740000003F

000000010000087400000040

000000010000087400000041

000000010000087400000042

000000010000087400000043

000000010000087400000044

000000010000087400000045

000000010000087400000046

000000010000087400000047

000000010000087400000048

000000010000087400000049

00000001000008740000004A

00000001000008740000004B

00000001000008740000004C

00000001000008740000004D

00000001000008740000004E

00000001000008740000004F

000000010000087400000050

000000010000087400000051

000000010000087400000052

000000010000087400000053

000000010000087400000054

000000010000087400000055

000000010000087400000056

000000010000087400000057

000000010000087400000058

000000010000087400000059

00000001000008740000005A

00000001000008740000005B

00000001000008740000005C

00000001000008740000005D

00000001000008740000005E

00000001000008740000005F

000000010000087400000060

000000010000087400000061

000000010000087400000062

000000010000087400000063

000000010000087400000064

000000010000087400000065

000000010000087400000066

000000010000087400000067

000000010000087400000068

000000010000087400000069

00000001000008740000006A

00000001000008740000006B

00000001000008740000006C

00000001000008740000006D

00000001000008740000006E

00000001000008740000006F

000000010000087400000070

000000010000087400000071

000000010000087400000072

000000010000087400000073

000000010000087400000074

000000010000087400000075

000000010000087400000076

000000010000087400000077

000000010000087400000078

000000010000087400000079

00000001000008740000007A

00000001000008740000007B

00000001000008740000007C

00000001000008740000007D

00000001000008740000007E

00000001000008740000007F

000000010000087400000080

000000010000087400000081

000000010000087400000082

000000010000087400000083

000000010000087400000084

000000010000087400000085

000000010000087400000086

000000010000087400000087

000000010000087400000088

000000010000087400000089

00000001000008740000008A

00000001000008740000008B

00000001000008740000008C

00000001000008740000008D

00000001000008740000008E

00000001000008740000008F

000000010000087400000090

000000010000087400000091

000000010000087400000092

000000010000087400000093

000000010000087400000094

000000010000087400000095

000000010000087400000096

000000010000087400000097

000000010000087400000098

000000010000087400000099

00000001000008740000009A

00000001000008740000009B

00000001000008740000009C

00000001000008740000009D

00000001000008740000009E

00000001000008740000009F

0000000100000874000000A0

0000000100000874000000A1

0000000100000874000000A2

0000000100000874000000A3

0000000100000874000000A4

0000000100000874000000A5

0000000100000874000000A6

0000000100000874000000A7

0000000100000874000000A8

0000000100000874000000A9

0000000100000874000000AA

0000000100000874000000AB

0000000100000874000000AC

0000000100000874000000AD

0000000100000874000000AE

0000000100000874000000AF

0000000100000874000000B0

0000000100000874000000B1

0000000100000874000000B2

0000000100000874000000B3

0000000100000874000000B4

0000000100000874000000B5

0000000100000874000000B6

0000000100000874000000B7

0000000100000874000000B8

0000000100000874000000B9



Attachments:

  [text/plain] files_list.txt (13.4K, ../../CADLmToLRCck9P7k=iOzLZRU7FiUmFTTTTggxF3gc0a1RasYrhQ@mail.gmail.com/3-files_list.txt)
  download | inline:
ÿþarchive_status

000000010000086E000000A9

000000010000086E000000C7

0000000100000870000000B3.deleted

000000010000087100000007.deleted

000000010000087100000034.deleted

000000010000087100000037.deleted

000000010000087100000048.deleted

0000000100000872000000C5

000000010000087300000095.deleted

0000000100000873000000A4.deleted

0000000100000873000000AE.deleted

0000000100000873000000B2.deleted

0000000100000873000000BA.deleted

0000000100000873000000C4.deleted

0000000100000873000000C6.deleted

0000000100000873000000C7.deleted

0000000100000873000000C8.deleted

0000000100000873000000C9

0000000100000873000000CA

0000000100000873000000CB

0000000100000873000000CC

0000000100000873000000CD

0000000100000873000000CE

0000000100000873000000CF

0000000100000873000000D0

0000000100000873000000D1

0000000100000873000000D2

0000000100000873000000D3

0000000100000873000000D4

0000000100000873000000D5

0000000100000873000000D6

0000000100000873000000D7

0000000100000873000000D8

0000000100000873000000D9

0000000100000873000000DA

0000000100000873000000DB

0000000100000873000000DC

0000000100000873000000DD

0000000100000873000000DE

0000000100000873000000DF

0000000100000873000000E0

0000000100000873000000E1

0000000100000873000000E2

0000000100000873000000E3

0000000100000873000000E4

0000000100000873000000E5

0000000100000873000000E6

0000000100000873000000E7

0000000100000873000000E8

0000000100000873000000E9

0000000100000873000000EA

0000000100000873000000EB

0000000100000873000000EC

0000000100000873000000ED

0000000100000873000000EE

0000000100000873000000EF

0000000100000873000000F0

0000000100000873000000F1

0000000100000873000000F2

0000000100000873000000F3

0000000100000873000000F4

0000000100000873000000F5

0000000100000873000000F6

0000000100000873000000F7

0000000100000873000000F8

0000000100000873000000F9

0000000100000873000000FA

0000000100000873000000FB

0000000100000873000000FC

0000000100000873000000FD

0000000100000873000000FE

0000000100000873000000FF

000000010000087400000000

000000010000087400000001

000000010000087400000002

000000010000087400000003

000000010000087400000004

000000010000087400000005

000000010000087400000006

000000010000087400000007

000000010000087400000008

000000010000087400000009

00000001000008740000000A

00000001000008740000000B

00000001000008740000000C

00000001000008740000000D

00000001000008740000000E

00000001000008740000000F

000000010000087400000010

000000010000087400000011

000000010000087400000012

000000010000087400000013

000000010000087400000014

000000010000087400000015

000000010000087400000016

000000010000087400000017

000000010000087400000018

000000010000087400000019

00000001000008740000001A

00000001000008740000001B

00000001000008740000001C

00000001000008740000001D

00000001000008740000001E

00000001000008740000001F

000000010000087400000020

000000010000087400000021

000000010000087400000022

000000010000087400000023

000000010000087400000024

000000010000087400000025

000000010000087400000026

000000010000087400000027

000000010000087400000028

000000010000087400000029

00000001000008740000002A

00000001000008740000002B

00000001000008740000002C

00000001000008740000002D

00000001000008740000002E

00000001000008740000002F

000000010000087400000030

000000010000087400000031

000000010000087400000032

000000010000087400000033

000000010000087400000034

000000010000087400000035

000000010000087400000036

000000010000087400000037

000000010000087400000038

000000010000087400000039

00000001000008740000003A

00000001000008740000003B

00000001000008740000003C

00000001000008740000003D

00000001000008740000003E

00000001000008740000003F

000000010000087400000040

000000010000087400000041

000000010000087400000042

000000010000087400000043

000000010000087400000044

000000010000087400000045

000000010000087400000046

000000010000087400000047

000000010000087400000048

000000010000087400000049

00000001000008740000004A

00000001000008740000004B

00000001000008740000004C

00000001000008740000004D

00000001000008740000004E

00000001000008740000004F

000000010000087400000050

000000010000087400000051

000000010000087400000052

000000010000087400000053

000000010000087400000054

000000010000087400000055

000000010000087400000056

000000010000087400000057

000000010000087400000058

000000010000087400000059

00000001000008740000005A

00000001000008740000005B

00000001000008740000005C

00000001000008740000005D

00000001000008740000005E

00000001000008740000005F

000000010000087400000060

000000010000087400000061

000000010000087400000062

000000010000087400000063

000000010000087400000064

000000010000087400000065

000000010000087400000066

000000010000087400000067

000000010000087400000068

000000010000087400000069

00000001000008740000006A

00000001000008740000006B

00000001000008740000006C

00000001000008740000006D

00000001000008740000006E

00000001000008740000006F

000000010000087400000070

000000010000087400000071

000000010000087400000072

000000010000087400000073

000000010000087400000074

000000010000087400000075

000000010000087400000076

000000010000087400000077

000000010000087400000078

000000010000087400000079

00000001000008740000007A

00000001000008740000007B

00000001000008740000007C

00000001000008740000007D

00000001000008740000007E

00000001000008740000007F

000000010000087400000080

000000010000087400000081

000000010000087400000082

000000010000087400000083

000000010000087400000084

000000010000087400000085

000000010000087400000086

000000010000087400000087

000000010000087400000088

000000010000087400000089

00000001000008740000008A

00000001000008740000008B

00000001000008740000008C

00000001000008740000008D

00000001000008740000008E

00000001000008740000008F

000000010000087400000090

000000010000087400000091

000000010000087400000092

000000010000087400000093

000000010000087400000094

000000010000087400000095

000000010000087400000096

000000010000087400000097

000000010000087400000098

000000010000087400000099

00000001000008740000009A

00000001000008740000009B

00000001000008740000009C

00000001000008740000009D

00000001000008740000009E

00000001000008740000009F

0000000100000874000000A0

0000000100000874000000A1

0000000100000874000000A2

0000000100000874000000A3

0000000100000874000000A4

0000000100000874000000A5

0000000100000874000000A6

0000000100000874000000A7

0000000100000874000000A8

0000000100000874000000A9

0000000100000874000000AA

0000000100000874000000AB

0000000100000874000000AC

0000000100000874000000AD

0000000100000874000000AE

0000000100000874000000AF

0000000100000874000000B0

0000000100000874000000B1

0000000100000874000000B2

0000000100000874000000B3

0000000100000874000000B4

0000000100000874000000B5

0000000100000874000000B6

0000000100000874000000B7

0000000100000874000000B8

0000000100000874000000B9



  [image/png] file_option.PNG (11.8K, ../../CADLmToLRCck9P7k=iOzLZRU7FiUmFTTTTggxF3gc0a1RasYrhQ@mail.gmail.com/4-file_option.PNG)
  download | view image

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
@ 2021-03-17 22:27           ` Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  0 siblings, 1 reply; 29+ messages in thread

From: Michael Paquier @ 2021-03-17 22:27 UTC (permalink / raw)
  To: Ярослав Пашинский <yarik97.6@gmail.com>; +Cc: Postgres bugs <pgsql-bugs@lists.postgresql.org>

On Wed, Mar 17, 2021 at 10:34:05AM +0200, Ярослав Пашинский wrote:
> I am able to run compiled binaries + patch on my servers, that not a
> problem. Or I could also compile if you could tell me briefly how to do
> that because it`s real useful skill :)

There is some documentation to do that with Visual Studio:
https://www.postgresql.org/docs/devel/install-windows.html
In my case, I just use a command prompt to launch those commands and
do the work.  I can send you links to download custom builds, of
course.  My guess is that these should be able to work on your host,
as Windows is good in terms of backward-compatibility.

> I attached 2 files: files_list.txt - that content of pg_wal directory;
> second file file_option.png - properties of one wal file that postgres
> can`t access. Strange thing is even with domain admin or local admin I
> can`t see rights properties for this file.

Thanks.  The .deleted files come from RemoveXlogFile() where a file
gets removed.  This means that a rename before doing an unlink()
fails.  What we are looking for here is what is holding those files
back.
--
Michael

Attachments:

  [application/pgp-signature] signature.asc (832B, ../../YFKB0ccg5zN+nS0Z@paquier.xyz/2-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
@ 2021-03-18 08:21             ` Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 08:43               ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  0 siblings, 2 replies; 29+ messages in thread

From: Ярослав Пашинский @ 2021-03-18 08:21 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Postgres bugs <pgsql-bugs@lists.postgresql.org>

Okay, I`ll try to build from source today following the instructions that
you sent to me. And another question is how to apply patch or you will send
me links with build + patch?
By the way, unfortunately, yesterday another 3 postgres instances started
"complaining" about access to wal files in logs, so that why I`m
interesting in fixing this ASAP :)

чт, 18 мар. 2021 г. в 00:27, Michael Paquier <michael@paquier.xyz>:

> On Wed, Mar 17, 2021 at 10:34:05AM +0200, Ярослав Пашинский wrote:
> > I am able to run compiled binaries + patch on my servers, that not a
> > problem. Or I could also compile if you could tell me briefly how to do
> > that because it`s real useful skill :)
>
> There is some documentation to do that with Visual Studio:
> https://www.postgresql.org/docs/devel/install-windows.html
> In my case, I just use a command prompt to launch those commands and
> do the work.  I can send you links to download custom builds, of
> course.  My guess is that these should be able to work on your host,
> as Windows is good in terms of backward-compatibility.
>
> > I attached 2 files: files_list.txt - that content of pg_wal directory;
> > second file file_option.png - properties of one wal file that postgres
> > can`t access. Strange thing is even with domain admin or local admin I
> > can`t see rights properties for this file.
>
> Thanks.  The .deleted files come from RemoveXlogFile() where a file
> gets removed.  This means that a rename before doing an unlink()
> fails.  What we are looking for here is what is holding those files
> back.
> --
> Michael
>

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
@ 2021-03-18 08:43               ` Michael Paquier <michael@paquier.xyz>
  1 sibling, 0 replies; 29+ messages in thread

From: Michael Paquier @ 2021-03-18 08:43 UTC (permalink / raw)
  To: Ярослав Пашинский <yarik97.6@gmail.com>; +Cc: Postgres bugs <pgsql-bugs@lists.postgresql.org>

On Thu, Mar 18, 2021 at 10:21:53AM +0200, Ярослав Пашинский wrote:
> Okay, I`ll try to build from source today following the instructions that
> you sent to me. And another question is how to apply patch or you will send
> me links with build + patch?

The "patch" command would be enough.  Please note that I have
generated some builds of 13.2 unpatched and 13.2 patched that you
could directly reuse, so that may make your life easier.  I'll send
you the links in a couple of minutes in a separate email.

> By the way, unfortunately, yesterday another 3 postgres instances started
> "complaining" about access to wal files in logs, so that why I`m
> interesting in fixing this ASAP :)

:(
--
Michael

Attachments:

  [application/pgp-signature] signature.asc (832B, ../../YFMSyKAzjKLhmzO9@paquier.xyz/2-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
@ 2021-03-18 12:16               ` Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 21:56                 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 23:51                 ` Re: BUG #16927: Postgres can`t access WAL files Andres Freund <andres@anarazel.de>
  1 sibling, 2 replies; 29+ messages in thread

From: Ярослав Пашинский @ 2021-03-18 12:16 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; Postgres bugs <pgsql-bugs@lists.postgresql.org>

The strange thing is why one server works fine on unpatched binaries while
the second one requires a patched version to get rid of pg_wal access
error.
UPD: just right now on developer server I got an error: "2021-03-18
14:11:39.444 EET [892] LOG:  could not rename file
"pg_wal/00000001000009130000006F": Permission denied"
Will switch to patched binaries and tell you later.
P.S: Sorry, that I didn't include in reply psql-bugs, is it ok right now?

чт, 18 мар. 2021 г. в 13:15, Michael Paquier <michael@paquier.xyz>:

> On Thu, Mar 18, 2021 at 12:44:29PM +0200, Ярослав Пашинский wrote:
> > So, I started test on my Windows server that we using for replica on
> > instance, which I copied from master. The binarys was unpatched, that
> > you sent me here. The system is: windows server 2016, os build
> 14393.4283.
> > To emulate load I used pgbench with such parameters -t 10000 -c 50 -j 20.
> > After couple of running test in log file I found almost same errors:
> > "2021-03-18 11:27:14.322 EET [3748] LOG:  could not rename temporary
> > statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat":
> > Permission denied
> > 2021-03-18 11:27:18.928 EET [692] LOG:  using stale statistics instead of
> > current ones because stats collector is not responding"
> > ...and
> > "2021-03-18 11:48:49.630 EET [6476] LOG:  could not rename file
> > "pg_wal/00000001000000650000008F": Permission denied"
> > So I decided to switch to patched binaries and sometimes get only this
> one
> > error:
> > "2021-03-18 12:27:14.571 EET [4840] LOG:  could not rename temporary
> > statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat":
> > Permission denied
> > 2021-03-18 12:27:19.178 EET [7556] LOG:  using stale statistics instead
> of
> > current ones because stats collector is not responding"
> > Which is not very critical, so it`s ok.
>
> Okay, so it looks like a very good news to me.  With the patched
> binaries you are not seeing the renaming problem with the WAL files
> anymore.
>
> > On other hand, on developer server (Windows Server 2016 (version 1607, OS
> > build 14393.4225))  with real load and unpatched binaries now I got no
> > errors about about pg_wal and gets only twice this error:
> > "2021-03-18 12:07:26.153 EET [2956] LOG:  could not rename temporary
> > statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat":
> > Permission denied"
> >
> > So, keep testing. That's strange for now. I am thinking about changing
> > binaries on prod server, but it will be possible on Saturday.
>
> Yes, I think that it would be good to do more tests, as it may be
> possible that what you are seeing does not repeat.  What you are
> reporting is encouraging though.  Thanks!
>
> By the way, it is very important to report that to the community
> mailing lists.  Could you add pgsql-bugs when replying please?
> --
> Michael
>

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
@ 2021-03-18 21:56                 ` Michael Paquier <michael@paquier.xyz>
  2021-03-19 08:08                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  1 sibling, 1 reply; 29+ messages in thread

From: Michael Paquier @ 2021-03-18 21:56 UTC (permalink / raw)
  To: Ярослав Пашинский <yarik97.6@gmail.com>; +Cc: Postgres bugs <pgsql-bugs@lists.postgresql.org>

On Thu, Mar 18, 2021 at 02:16:21PM +0200, Ярослав Пашинский wrote:
> чт, 18 мар. 2021 г. в 13:15, Michael Paquier <michael@paquier.xyz>:
>> On Thu, Mar 18, 2021 at 12:44:29PM +0200, Ярослав Пашинский wrote:
>>> So, I started test on my Windows server that we using for replica on
>>> instance, which I copied from master. The binarys was unpatched, that
>>> you sent me here. The system is: windows server 2016, os build 14393.4283.
>>> To emulate load I used pgbench with such parameters -t 10000 -c 50 -j 20.
>>> After couple of running test in log file I found almost same errors:
>>> "2021-03-18 11:27:14.322 EET [3748] LOG:  could not rename temporary
>>> statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat":
>>> Permission denied
>>> 2021-03-18 11:27:18.928 EET [692] LOG:  using stale statistics instead of
>>> current ones because stats collector is not responding"
>>> ...and
>>> "2021-03-18 11:48:49.630 EET [6476] LOG:  could not rename file
>>> "pg_wal/00000001000000650000008F": Permission denied"
>>> So I decided to switch to patched binaries and sometimes get only this
>>> one error:
>>> "2021-03-18 12:27:14.571 EET [4840] LOG:  could not rename temporary
>>> statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat":
>>> Permission denied
>>> 2021-03-18 12:27:19.178 EET [7556] LOG:  using stale statistics instead
>> of current ones because stats collector is not responding"
>>> Which is not very critical, so it`s ok.
>>
>> Okay, so it looks like a very good news to me.  With the patched
>> binaries you are not seeing the renaming problem with the WAL files
>> anymore.
>>
>>> On other hand, on developer server (Windows Server 2016 (version 1607, OS
>>> build 14393.4225))  with real load and unpatched binaries now I got no
>>> errors about about pg_wal and gets only twice this error:
>>> "2021-03-18 12:07:26.153 EET [2956] LOG:  could not rename temporary
>>> statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat":
>>> Permission denied"
>>>
>>> So, keep testing. That's strange for now. I am thinking about changing
>>> binaries on prod server, but it will be possible on Saturday.
>>
>> Yes, I think that it would be good to do more tests, as it may be
>> possible that what you are seeing does not repeat.  What you are
>> reporting is encouraging though.  Thanks!
>>
>> By the way, it is very important to report that to the community
>> mailing lists.  Could you add pgsql-bugs when replying please?
>
> The strange thing is why one server works fine on unpatched binaries while
> the second one requires a patched version to get rid of pg_wal access
> error.
>
> UPD: just right now on developer server I got an error: "2021-03-18
> 14:11:39.444 EET [892] LOG:  could not rename file
> "pg_wal/00000001000009130000006F": Permission denied"
> Will switch to patched binaries and tell you later.

The issue seems to depend on timing and the load your cluster is
facing, so that is not surprising to hear that this does not show up
100% of the time.  I am actually glad to hear that you have not seen
the issue anymore with the patched builds, while the unpatched builds
have shown the problem at least once.  It would be a problem if the
patched builds begin to complain about the renaming of the WAL
segments though as we would have to consider a different theory.

> P.S: Sorry, that I didn't include in reply psql-bugs, is it ok right now?

That's fine.  Thanks :)

I have added to this email the last things we discussed, for
transparency.
--
Michael

Attachments:

  [application/pgp-signature] signature.asc (832B, ../../YFPMl3TUPXbN127E@paquier.xyz/2-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 21:56                 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
@ 2021-03-19 08:08                   ` Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 08:28                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 29+ messages in thread

From: Ярослав Пашинский @ 2021-03-19 08:08 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Postgres bugs <pgsql-bugs@lists.postgresql.org>

Yes, for this moment I have 4 clusters on developer server and 1 cluster
that I`m testing by my own and there is no error connected with
access to WAL files for last about 17-20 hours. I`ll will run my prod
clusters and will also tell you. If I won`t send you any new message - the
problem is gone also on prod server. Thanks in advance!
P.S: the error "2021-03-18 12:22:26.096 EET [4840] LOG:  could not rename
temporary statistics file "pg_stat_tmp/global.tmp" to
"pg_stat_tmp/global.stat": Permission denied" still be, as I know it`s not
very
P.S.S: will be this patch available to download for everyone?

чт, 18 мар. 2021 г. в 23:56, Michael Paquier <michael@paquier.xyz>:

> On Thu, Mar 18, 2021 at 02:16:21PM +0200, Ярослав Пашинский wrote:
> > чт, 18 мар. 2021 г. в 13:15, Michael Paquier <michael@paquier.xyz>:
> >> On Thu, Mar 18, 2021 at 12:44:29PM +0200, Ярослав Пашинский wrote:
> >>> So, I started test on my Windows server that we using for replica on
> >>> instance, which I copied from master. The binarys was unpatched, that
> >>> you sent me here. The system is: windows server 2016, os build
> 14393.4283.
> >>> To emulate load I used pgbench with such parameters -t 10000 -c 50 -j
> 20.
> >>> After couple of running test in log file I found almost same errors:
> >>> "2021-03-18 11:27:14.322 EET [3748] LOG:  could not rename temporary
> >>> statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat":
> >>> Permission denied
> >>> 2021-03-18 11:27:18.928 EET [692] LOG:  using stale statistics instead
> of
> >>> current ones because stats collector is not responding"
> >>> ...and
> >>> "2021-03-18 11:48:49.630 EET [6476] LOG:  could not rename file
> >>> "pg_wal/00000001000000650000008F": Permission denied"
> >>> So I decided to switch to patched binaries and sometimes get only this
> >>> one error:
> >>> "2021-03-18 12:27:14.571 EET [4840] LOG:  could not rename temporary
> >>> statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat":
> >>> Permission denied
> >>> 2021-03-18 12:27:19.178 EET [7556] LOG:  using stale statistics instead
> >> of current ones because stats collector is not responding"
> >>> Which is not very critical, so it`s ok.
> >>
> >> Okay, so it looks like a very good news to me.  With the patched
> >> binaries you are not seeing the renaming problem with the WAL files
> >> anymore.
> >>
> >>> On other hand, on developer server (Windows Server 2016 (version 1607,
> OS
> >>> build 14393.4225))  with real load and unpatched binaries now I got no
> >>> errors about about pg_wal and gets only twice this error:
> >>> "2021-03-18 12:07:26.153 EET [2956] LOG:  could not rename temporary
> >>> statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat":
> >>> Permission denied"
> >>>
> >>> So, keep testing. That's strange for now. I am thinking about changing
> >>> binaries on prod server, but it will be possible on Saturday.
> >>
> >> Yes, I think that it would be good to do more tests, as it may be
> >> possible that what you are seeing does not repeat.  What you are
> >> reporting is encouraging though.  Thanks!
> >>
> >> By the way, it is very important to report that to the community
> >> mailing lists.  Could you add pgsql-bugs when replying please?
> >
> > The strange thing is why one server works fine on unpatched binaries
> while
> > the second one requires a patched version to get rid of pg_wal access
> > error.
> >
> > UPD: just right now on developer server I got an error: "2021-03-18
> > 14:11:39.444 EET [892] LOG:  could not rename file
> > "pg_wal/00000001000009130000006F": Permission denied"
> > Will switch to patched binaries and tell you later.
>
> The issue seems to depend on timing and the load your cluster is
> facing, so that is not surprising to hear that this does not show up
> 100% of the time.  I am actually glad to hear that you have not seen
> the issue anymore with the patched builds, while the unpatched builds
> have shown the problem at least once.  It would be a problem if the
> patched builds begin to complain about the renaming of the WAL
> segments though as we would have to consider a different theory.
>
> > P.S: Sorry, that I didn't include in reply psql-bugs, is it ok right now?
>
> That's fine.  Thanks :)
>
> I have added to this email the last things we discussed, for
> transparency.
> --
> Michael
>

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 21:56                 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 08:08                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
@ 2021-03-19 08:28                     ` Michael Paquier <michael@paquier.xyz>
  2021-03-19 09:04                       ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  0 siblings, 1 reply; 29+ messages in thread

From: Michael Paquier @ 2021-03-19 08:28 UTC (permalink / raw)
  To: Ярослав Пашинский <yarik97.6@gmail.com>; +Cc: Postgres bugs <pgsql-bugs@lists.postgresql.org>

On Fri, Mar 19, 2021 at 10:08:36AM +0200, Ярослав Пашинский wrote:
> Yes, for this moment I have 4 clusters on developer server and 1 cluster
> that I`m testing by my own and there is no error connected with
> access to WAL files for last about 17-20 hours. I`ll will run my prod
> clusters and will also tell you. If I won`t send you any new message - the
> problem is gone also on prod server. Thanks in advance!

Cool.  So, it really looks like we have found the issue based on what
you are saying here, and that we had better consider as a first step a
revert of aaaef7a on HEAD and REL_13_STABLE.

So, what do others think?  Would people agree to revert aaaef7a for
now?

> P.S: the error "2021-03-18 12:22:26.096 EET [4840] LOG:  could not rename
> temporary statistics file "pg_stat_tmp/global.tmp" to
> "pg_stat_tmp/global.stat": Permission denied" still be, as I know it`s not
> very

This one is in a different code path.

> P.S.S: will be this patch available to download for everyone?

Well, if a different committer or myself is able to get a patch
committed, it will available to everyone once 13.3 gets released.
This would happen in May based on the existing roadmap:
https://www.postgresql.org/developer/roadmap/

How much did you test the unpatched builds by the way?
--
Michael

Attachments:

  [application/pgp-signature] signature.asc (832B, ../../YFRglwF+j2RMmbXF@paquier.xyz/2-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 21:56                 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 08:08                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 08:28                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
@ 2021-03-19 09:04                       ` Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 11:36                         ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 29+ messages in thread

From: Ярослав Пашинский @ 2021-03-19 09:04 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Postgres bugs <pgsql-bugs@lists.postgresql.org>

"and that we had better consider as a first step a
revert of aaaef7a on HEAD and REL_13_STABLE.

So, what do others think?  Would people agree to revert aaaef7a for
now?"
I didn't quite understand you here, is " aaaef7a " stands for patch name?

" How much did you test the unpatched builds by the way?  "
On cluster where load was caused by pgbench I met wal file access error
after about 1 hour, but on developer server with real load (usually up to
100 connections) the error occurred after 2.5-3 hours.

пт, 19 мар. 2021 г. в 10:28, Michael Paquier <michael@paquier.xyz>:

> On Fri, Mar 19, 2021 at 10:08:36AM +0200, Ярослав Пашинский wrote:
> > Yes, for this moment I have 4 clusters on developer server and 1 cluster
> > that I`m testing by my own and there is no error connected with
> > access to WAL files for last about 17-20 hours. I`ll will run my prod
> > clusters and will also tell you. If I won`t send you any new message -
> the
> > problem is gone also on prod server. Thanks in advance!
>
> Cool.  So, it really looks like we have found the issue based on what
> you are saying here, and that we had better consider as a first step a
> revert of aaaef7a on HEAD and REL_13_STABLE.
>
> So, what do others think?  Would people agree to revert aaaef7a for
> now?
>
> > P.S: the error "2021-03-18 12:22:26.096 EET [4840] LOG:  could not rename
> > temporary statistics file "pg_stat_tmp/global.tmp" to
> > "pg_stat_tmp/global.stat": Permission denied" still be, as I know it`s
> not
> > very
>
> This one is in a different code path.
>
> > P.S.S: will be this patch available to download for everyone?
>
> Well, if a different committer or myself is able to get a patch
> committed, it will available to everyone once 13.3 gets released.
> This would happen in May based on the existing roadmap:
> https://www.postgresql.org/developer/roadmap/
>
> How much did you test the unpatched builds by the way?
> --
> Michael
>

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 21:56                 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 08:08                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 08:28                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 09:04                       ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
@ 2021-03-19 11:36                         ` Michael Paquier <michael@paquier.xyz>
  2021-03-19 11:45                           ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 15:13                           ` Re: BUG #16927: Postgres can`t access WAL files Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 29+ messages in thread

From: Michael Paquier @ 2021-03-19 11:36 UTC (permalink / raw)
  To: Ярослав Пашинский <yarik97.6@gmail.com>; +Cc: Postgres bugs <pgsql-bugs@lists.postgresql.org>

On Fri, Mar 19, 2021 at 11:04:14AM +0200, Ярослав Пашинский wrote:
> I didn't quite understand you here, is " aaaef7a " stands for patch name?

There was a typo in one of my previous messages.  What I was referring
to is aaa3aedd.  That's a commit of the Postgres code tree, if you are
not familiar with git, here is a link to the code change:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=aaa3aedd

>> " How much did you test the unpatched builds by the way?  "
> On cluster where load was caused by pgbench I met wal file access error
> after about 1 hour, but on developer server with real load (usually up to
> 100 connections) the error occurred after 2.5-3 hours.

OK, thanks.  My environments are not that sensitive to the issue,
unfortunately.
--
Michael

Attachments:

  [application/pgp-signature] signature.asc (832B, ../../YFSMsxc8w2eqUHHR@paquier.xyz/2-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 21:56                 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 08:08                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 08:28                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 09:04                       ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 11:36                         ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
@ 2021-03-19 11:45                           ` Ярослав Пашинский <yarik97.6@gmail.com>
  1 sibling, 0 replies; 29+ messages in thread

From: Ярослав Пашинский @ 2021-03-19 11:45 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Postgres bugs <pgsql-bugs@lists.postgresql.org>

I don`t know the dependencies of appearances of this issue (Honestly, I
tried to find them before mailing psql-bugs). For example, yesterday log
file was full of this messages but today I was waiting to get this error
back to check wal file linking via FindLinks program. At least I`m
confident that this issue could destroy replication and this issue not only
on one machine. Anyway, I`m happy that your patch seems to be key to solve
this problem.

пт, 19 мар. 2021 г. в 13:36, Michael Paquier <michael@paquier.xyz>:

> On Fri, Mar 19, 2021 at 11:04:14AM +0200, Ярослав Пашинский wrote:
> > I didn't quite understand you here, is " aaaef7a " stands for patch name?
>
> There was a typo in one of my previous messages.  What I was referring
> to is aaa3aedd.  That's a commit of the Postgres code tree, if you are
> not familiar with git, here is a link to the code change:
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=aaa3aedd
>
> >> " How much did you test the unpatched builds by the way?  "
> > On cluster where load was caused by pgbench I met wal file access error
> > after about 1 hour, but on developer server with real load (usually up to
> > 100 connections) the error occurred after 2.5-3 hours.
>
> OK, thanks.  My environments are not that sensitive to the issue,
> unfortunately.
> --
> Michael
>

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 21:56                 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 08:08                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 08:28                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 09:04                       ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 11:36                         ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
@ 2021-03-19 15:13                           ` Tom Lane <tgl@sss.pgh.pa.us>
  2021-03-19 15:19                             ` Re: BUG #16927: Postgres can`t access WAL files Magnus Hagander <magnus@hagander.net>
  1 sibling, 1 reply; 29+ messages in thread

From: Tom Lane @ 2021-03-19 15:13 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Ярослав Пашинский <yarik97.6@gmail.com>; Postgres bugs <pgsql-bugs@lists.postgresql.org>

Michael Paquier <michael@paquier.xyz> writes:
> There was a typo in one of my previous messages.  What I was referring
> to is aaa3aedd.

Ah, I was just about to ask what the heck aaaef7a referred to.

Given the evidence that there's a problem, I agree with reverting
that.  I'd suggest keeping the cosmetic rename of the function,
but we have to put back the Windows-doesn't-HAVE_WORKING_LINK logic.

Grepping in the v12 branch, I find a second use of HAVE_WORKING_LINK
in contrib/pg_standby.  But that seems to be in a non-WIN32 code path,
so I don't think putting that back is necessary.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 21:56                 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 08:08                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 08:28                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 09:04                       ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 11:36                         ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 15:13                           ` Re: BUG #16927: Postgres can`t access WAL files Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-03-19 15:19                             ` Magnus Hagander <magnus@hagander.net>
  2021-03-19 22:32                               ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 29+ messages in thread

From: Magnus Hagander @ 2021-03-19 15:19 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michael Paquier <michael@paquier.xyz>; Ярослав Пашинский <yarik97.6@gmail.com>; Postgres bugs <pgsql-bugs@lists.postgresql.org>

On Fri, Mar 19, 2021 at 4:14 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Michael Paquier <michael@paquier.xyz> writes:
> > There was a typo in one of my previous messages.  What I was referring
> > to is aaa3aedd.
>
> Ah, I was just about to ask what the heck aaaef7a referred to.
>
> Given the evidence that there's a problem, I agree with reverting
> that.  I'd suggest keeping the cosmetic rename of the function,
> but we have to put back the Windows-doesn't-HAVE_WORKING_LINK logic.

+1. I think the indications are definitely clear enough that this has
to go back in.


> Grepping in the v12 branch, I find a second use of HAVE_WORKING_LINK
> in contrib/pg_standby.  But that seems to be in a non-WIN32 code path,
> so I don't think putting that back is necessary.

.. and apart front aht I *really* doubt that one has many users,
especially on Windows :)

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/





^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 21:56                 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 08:08                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 08:28                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 09:04                       ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 11:36                         ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 15:13                           ` Re: BUG #16927: Postgres can`t access WAL files Tom Lane <tgl@sss.pgh.pa.us>
  2021-03-19 15:19                             ` Re: BUG #16927: Postgres can`t access WAL files Magnus Hagander <magnus@hagander.net>
@ 2021-03-19 22:32                               ` Michael Paquier <michael@paquier.xyz>
  2021-03-19 23:56                                 ` Re: BUG #16927: Postgres can`t access WAL files Andres Freund <andres@anarazel.de>
  2021-03-20 00:08                                 ` Re: BUG #16927: Postgres can`t access WAL files Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 29+ messages in thread

From: Michael Paquier @ 2021-03-19 22:32 UTC (permalink / raw)
  To: Magnus Hagander <magnus@hagander.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ярослав Пашинский <yarik97.6@gmail.com>; Postgres bugs <pgsql-bugs@lists.postgresql.org>

On Fri, Mar 19, 2021 at 04:19:50PM +0100, Magnus Hagander wrote:
> On Fri, Mar 19, 2021 at 4:14 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Given the evidence that there's a problem, I agree with reverting
>> that.  I'd suggest keeping the cosmetic rename of the function,
>> but we have to put back the Windows-doesn't-HAVE_WORKING_LINK logic.
> 
> +1. I think the indications are definitely clear enough that this has
> to go back in.

No problem from me to keep the rename, and so this leads to the simple
patch attached, then.  Any comments?

>> Grepping in the v12 branch, I find a second use of HAVE_WORKING_LINK
>> in contrib/pg_standby.  But that seems to be in a non-WIN32 code path,
>> so I don't think putting that back is necessary.
> 
> .. and apart front aht I *really* doubt that one has many users,
> especially on Windows :)

Yeah, agreed.
--
Michael

Attachments:

  [text/x-diff] win32-link.patch (1.4K, ../../YFUmi3diUlqyoL44@paquier.xyz/2-win32-link.patch)
  download | inline diff:
diff --git a/src/include/pg_config_manual.h b/src/include/pg_config_manual.h
index f10ad0acd6..e28c990382 100644
--- a/src/include/pg_config_manual.h
+++ b/src/include/pg_config_manual.h
@@ -135,6 +135,13 @@
 #define EXEC_BACKEND
 #endif
 
+/*
+ * Define this if your operating system supports link()
+ */
+#if !defined(WIN32) && !defined(__CYGWIN__)
+#define HAVE_WORKING_LINK 1
+#endif
+
 /*
  * USE_POSIX_FADVISE controls whether Postgres will attempt to use the
  * posix_fadvise() kernel call.  Usually the automatic configure tests are
diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c
index 110ba31517..92b1959648 100644
--- a/src/backend/storage/file/fd.c
+++ b/src/backend/storage/file/fd.c
@@ -820,6 +820,7 @@ durable_rename_excl(const char *oldfile, const char *newfile, int elevel)
 	if (fsync_fname_ext(oldfile, false, false, elevel) != 0)
 		return -1;
 
+#ifdef HAVE_WORKING_LINK
 	if (link(oldfile, newfile) < 0)
 	{
 		ereport(elevel,
@@ -829,6 +830,17 @@ durable_rename_excl(const char *oldfile, const char *newfile, int elevel)
 		return -1;
 	}
 	unlink(oldfile);
+#else
+	/* XXX: Add racy file existence check? */
+	if (rename(oldfile, newfile) < 0)
+	{
+		ereport(elevel,
+				(errcode_for_file_access(),
+				 errmsg("could not rename file \"%s\" to \"%s\": %m",
+						oldfile, newfile)));
+		return -1;
+	}
+#endif
 
 	/*
 	 * Make change persistent in case of an OS crash, both the new entry and

  [application/pgp-signature] signature.asc (832B, ../../YFUmi3diUlqyoL44@paquier.xyz/3-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 21:56                 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 08:08                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 08:28                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 09:04                       ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 11:36                         ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 15:13                           ` Re: BUG #16927: Postgres can`t access WAL files Tom Lane <tgl@sss.pgh.pa.us>
  2021-03-19 15:19                             ` Re: BUG #16927: Postgres can`t access WAL files Magnus Hagander <magnus@hagander.net>
  2021-03-19 22:32                               ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
@ 2021-03-19 23:56                                 ` Andres Freund <andres@anarazel.de>
  2021-03-20 00:31                                   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  1 sibling, 1 reply; 29+ messages in thread

From: Andres Freund @ 2021-03-19 23:56 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Magnus Hagander <magnus@hagander.net>; Tom Lane <tgl@sss.pgh.pa.us>; Ярослав Пашинский <yarik97.6@gmail.com>; Postgres bugs <pgsql-bugs@lists.postgresql.org>

Hi,

On 2021-03-20 07:32:43 +0900, Michael Paquier wrote:
> No problem from me to keep the rename, and so this leads to the simple
> patch attached, then.  Any comments?

- I think there needs to be a reference to the problem, otherwise we'll
  just redo this a couple years down the line
- I'd not add the XXX, because the whole idea of durable_rename_excl
  seems wrong to me, and it might motivate people to come up with
  patches...

Greetings,

Andres Freund





^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 21:56                 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 08:08                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 08:28                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 09:04                       ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 11:36                         ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 15:13                           ` Re: BUG #16927: Postgres can`t access WAL files Tom Lane <tgl@sss.pgh.pa.us>
  2021-03-19 15:19                             ` Re: BUG #16927: Postgres can`t access WAL files Magnus Hagander <magnus@hagander.net>
  2021-03-19 22:32                               ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 23:56                                 ` Re: BUG #16927: Postgres can`t access WAL files Andres Freund <andres@anarazel.de>
@ 2021-03-20 00:31                                   ` Michael Paquier <michael@paquier.xyz>
  2021-03-22 05:54                                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 29+ messages in thread

From: Michael Paquier @ 2021-03-20 00:31 UTC (permalink / raw)
  To: Andres Freund <andres@anarazel.de>; +Cc: Magnus Hagander <magnus@hagander.net>; Tom Lane <tgl@sss.pgh.pa.us>; Ярослав Пашинский <yarik97.6@gmail.com>; Postgres bugs <pgsql-bugs@lists.postgresql.org>

On Fri, Mar 19, 2021 at 04:56:45PM -0700, Andres Freund wrote:
> - I think there needs to be a reference to the problem, otherwise we'll
>   just redo this a couple years down the line
> - I'd not add the XXX, because the whole idea of durable_rename_excl
>   seems wrong to me, and it might motivate people to come up with
>   patches...

Fine by me to remove that :)

What about replacing the XXX comment by a small note, say:
"On Windows, using a hard link followed by an unlink() causes
concurrency issues with code paths interacting with those files, a
rename does not cause that."

If you have a better idea, which I am sure you do, please feel free to
send suggestions.
--
Michael

Attachments:

  [application/pgp-signature] signature.asc (832B, ../../YFVCSwu4U9htrqJ%2F@paquier.xyz/2-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 21:56                 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 08:08                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 08:28                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 09:04                       ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 11:36                         ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 15:13                           ` Re: BUG #16927: Postgres can`t access WAL files Tom Lane <tgl@sss.pgh.pa.us>
  2021-03-19 15:19                             ` Re: BUG #16927: Postgres can`t access WAL files Magnus Hagander <magnus@hagander.net>
  2021-03-19 22:32                               ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 23:56                                 ` Re: BUG #16927: Postgres can`t access WAL files Andres Freund <andres@anarazel.de>
  2021-03-20 00:31                                   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
@ 2021-03-22 05:54                                     ` Michael Paquier <michael@paquier.xyz>
  2021-03-23 09:49                                       ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  0 siblings, 1 reply; 29+ messages in thread

From: Michael Paquier @ 2021-03-22 05:54 UTC (permalink / raw)
  To: Andres Freund <andres@anarazel.de>; +Cc: Magnus Hagander <magnus@hagander.net>; Tom Lane <tgl@sss.pgh.pa.us>; Ярослав Пашинский <yarik97.6@gmail.com>; Postgres bugs <pgsql-bugs@lists.postgresql.org>

On Sat, Mar 20, 2021 at 09:31:07AM +0900, Michael Paquier wrote:
> Fine by me to remove that :)

Applied this stuff as of 909b449 for 13.3~ to address the bug, but I
would not mind tweak more the areas and its comments if people have
more ideas.  Yaroslav, things should be good with the next minor
release of Postgres.
--
Michael

Attachments:

  [application/pgp-signature] signature.asc (832B, ../../YFgxHcDCW9FmzWfK@paquier.xyz/2-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 21:56                 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 08:08                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 08:28                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 09:04                       ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 11:36                         ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 15:13                           ` Re: BUG #16927: Postgres can`t access WAL files Tom Lane <tgl@sss.pgh.pa.us>
  2021-03-19 15:19                             ` Re: BUG #16927: Postgres can`t access WAL files Magnus Hagander <magnus@hagander.net>
  2021-03-19 22:32                               ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 23:56                                 ` Re: BUG #16927: Postgres can`t access WAL files Andres Freund <andres@anarazel.de>
  2021-03-20 00:31                                   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-22 05:54                                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
@ 2021-03-23 09:49                                       ` Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-24 00:32                                         ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 29+ messages in thread

From: Ярослав Пашинский @ 2021-03-23 09:49 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Andres Freund <andres@anarazel.de>; Magnus Hagander <magnus@hagander.net>; Tom Lane <tgl@sss.pgh.pa.us>; Postgres bugs <pgsql-bugs@lists.postgresql.org>

The error has completely gone and replication works fine. Your patch
definitely works, my 8 prod & dev clusters + 4 replication slots works fine
on 13.2 now. Only one error still sometimes gets occurred (as I showed
before): "2021-03-23 11:44:52.932 EET [9272] LOG:  could not rename
temporary statistics file "pg_stat_tmp/global.tmp" to
"pg_stat_tmp/global.stat": Permission denied"
Hope It'll be fixed in the next releases.
 Best wishes, Yaroslav!

пн, 22 мар. 2021 г. в 07:54, Michael Paquier <michael@paquier.xyz>:

> On Sat, Mar 20, 2021 at 09:31:07AM +0900, Michael Paquier wrote:
> > Fine by me to remove that :)
>
> Applied this stuff as of 909b449 for 13.3~ to address the bug, but I
> would not mind tweak more the areas and its comments if people have
> more ideas.  Yaroslav, things should be good with the next minor
> release of Postgres.
> --
> Michael
>

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 21:56                 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 08:08                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 08:28                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 09:04                       ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 11:36                         ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 15:13                           ` Re: BUG #16927: Postgres can`t access WAL files Tom Lane <tgl@sss.pgh.pa.us>
  2021-03-19 15:19                             ` Re: BUG #16927: Postgres can`t access WAL files Magnus Hagander <magnus@hagander.net>
  2021-03-19 22:32                               ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 23:56                                 ` Re: BUG #16927: Postgres can`t access WAL files Andres Freund <andres@anarazel.de>
  2021-03-20 00:31                                   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-22 05:54                                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-23 09:49                                       ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
@ 2021-03-24 00:32                                         ` Michael Paquier <michael@paquier.xyz>
  0 siblings, 0 replies; 29+ messages in thread

From: Michael Paquier @ 2021-03-24 00:32 UTC (permalink / raw)
  To: Ярослав Пашинский <yarik97.6@gmail.com>; +Cc: Andres Freund <andres@anarazel.de>; Magnus Hagander <magnus@hagander.net>; Tom Lane <tgl@sss.pgh.pa.us>; Postgres bugs <pgsql-bugs@lists.postgresql.org>

On Tue, Mar 23, 2021 at 11:49:32AM +0200, Ярослав Пашинский wrote:
> The error has completely gone and replication works fine.

Glad to hear that.  Until 13.3 is out, you could use the binaries I
have provided but these have been stripped from most of their build
options to make them portable for the tests: no OpenSSL, no ICU, and
more things missing.

> Your patch definitely works, my 8 prod & dev clusters + 4
> replication slots works fine on 13.2 now. Only one error still
> sometimes gets occurred (as I showed > before): "2021-03-23
> 11:44:52.932 EET [9272] LOG:  could not rename temporary
> statistics file "pg_stat_tmp/global.tmp" to
> "pg_stat_tmp/global.stat": Permission denied" 
> Hope It'll be fixed in the next releases.

This one is a separate issue, and I don't think I'll be able to look
at that in more details until the commit fest finishes (development of
14 finsihes in two weeks so the activity is high).  Is this something
new to 13 or did you see that in past version as well on your servers?
--
Michael

Attachments:

  [application/pgp-signature] signature.asc (832B, ../../YFqIgTrnN1KzFiHs@paquier.xyz/2-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 21:56                 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 08:08                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 08:28                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 09:04                       ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-19 11:36                         ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-19 15:13                           ` Re: BUG #16927: Postgres can`t access WAL files Tom Lane <tgl@sss.pgh.pa.us>
  2021-03-19 15:19                             ` Re: BUG #16927: Postgres can`t access WAL files Magnus Hagander <magnus@hagander.net>
  2021-03-19 22:32                               ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
@ 2021-03-20 00:08                                 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 29+ messages in thread

From: Tom Lane @ 2021-03-20 00:08 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Magnus Hagander <magnus@hagander.net>; Ярослав Пашинский <yarik97.6@gmail.com>; Postgres bugs <pgsql-bugs@lists.postgresql.org>

Michael Paquier <michael@paquier.xyz> writes:
> No problem from me to keep the rename, and so this leads to the simple
> patch attached, then.  Any comments?

LGTM.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
@ 2021-03-18 23:51                 ` Andres Freund <andres@anarazel.de>
  2021-03-19 08:38                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  1 sibling, 1 reply; 29+ messages in thread

From: Andres Freund @ 2021-03-18 23:51 UTC (permalink / raw)
  To: Ярослав Пашинский <yarik97.6@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Postgres bugs <pgsql-bugs@lists.postgresql.org>

Hi,

On 2021-03-18 14:16:21 +0200, Ярослав Пашинский wrote:
> The strange thing is why one server works fine on unpatched binaries while
> the second one requires a patched version to get rid of pg_wal access
> error.
> UPD: just right now on developer server I got an error: "2021-03-18
> 14:11:39.444 EET [892] LOG:  could not rename file
> "pg_wal/00000001000009130000006F": Permission denied"
> Will switch to patched binaries and tell you later.

Could you use
https://docs.microsoft.com/en-us/sysinternals/downloads/findlinks on one
of the files that can't be renamed? Or even better, the all the WAL
files?

Greetings,

Andres Freund





^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 23:51                 ` Re: BUG #16927: Postgres can`t access WAL files Andres Freund <andres@anarazel.de>
@ 2021-03-19 08:38                   ` Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-22 04:15                     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 29+ messages in thread

From: Ярослав Пашинский @ 2021-03-19 08:38 UTC (permalink / raw)
  To: Andres Freund <andres@anarazel.de>; +Cc: Michael Paquier <michael@paquier.xyz>; Postgres bugs <pgsql-bugs@lists.postgresql.org>

Hello, sure. Right now I found recent log for one WAL file and here is a
output of find links program:
".\FindLinks64.exe "D:\DB_update\PSQL_4\pg_wal\000000010000429E00000077"

Findlinks v1.1 - Locate file hard links
Copyright (C) 2011-2016 Mark Russinovich
Sysinternals - www.sysinternals.com

Error opening d:\db_update\psql_4\pg_wal\000000010000429e00000077:
Access is denied. "
 P.S.: I run program with admin rights.

пт, 19 мар. 2021 г. в 01:51, Andres Freund <andres@anarazel.de>:

> Hi,
>
> On 2021-03-18 14:16:21 +0200, Ярослав Пашинский wrote:
> > The strange thing is why one server works fine on unpatched binaries
> while
> > the second one requires a patched version to get rid of pg_wal access
> > error.
> > UPD: just right now on developer server I got an error: "2021-03-18
> > 14:11:39.444 EET [892] LOG:  could not rename file
> > "pg_wal/00000001000009130000006F": Permission denied"
> > Will switch to patched binaries and tell you later.
>
> Could you use
> https://docs.microsoft.com/en-us/sysinternals/downloads/findlinks on one
> of the files that can't be renamed? Or even better, the all the WAL
> files?
>
> Greetings,
>
> Andres Freund
>

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

* Re: BUG #16927: Postgres can`t access WAL files
  2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
  2021-03-16 01:20 ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 08:40   ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 22:30     ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-16 23:56       ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-17 08:34         ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-17 22:27           ` Re: BUG #16927: Postgres can`t access WAL files Michael Paquier <michael@paquier.xyz>
  2021-03-18 08:21             ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 12:16               ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
  2021-03-18 23:51                 ` Re: BUG #16927: Postgres can`t access WAL files Andres Freund <andres@anarazel.de>
  2021-03-19 08:38                   ` Re: BUG #16927: Postgres can`t access WAL files Ярослав Пашинский <yarik97.6@gmail.com>
@ 2021-03-22 04:15                     ` Michael Paquier <michael@paquier.xyz>
  0 siblings, 0 replies; 29+ messages in thread

From: Michael Paquier @ 2021-03-22 04:15 UTC (permalink / raw)
  To: Ярослав Пашинский <yarik97.6@gmail.com>; +Cc: Andres Freund <andres@anarazel.de>; Postgres bugs <pgsql-bugs@lists.postgresql.org>

On Fri, Mar 19, 2021 at 10:38:51AM +0200, Ярослав Пашинский wrote:
> Error opening d:\db_update\psql_4\pg_wal\000000010000429e00000077:
> Access is denied. "
>  P.S.: I run program with admin rights.

Hmm.  I recall that EACCES would happen on files marked as pending for
deletion.
--
Michael

Attachments:

  [application/pgp-signature] signature.asc (832B, ../../YFgZ8xufBk8SLQzy@paquier.xyz/2-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 29+ messages in thread

end of thread, other threads:[~2021-03-24 00:32 UTC | newest]

Thread overview: 29+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-15 14:19 BUG #16927: Postgres can`t access WAL files PG Bug reporting form <noreply@postgresql.org>
2021-03-16 01:20 ` Michael Paquier <michael@paquier.xyz>
2021-03-16 01:53   ` Michael Paquier <michael@paquier.xyz>
2021-03-16 08:40   ` Michael Paquier <michael@paquier.xyz>
2021-03-16 22:30     ` Michael Paquier <michael@paquier.xyz>
2021-03-16 23:56       ` Michael Paquier <michael@paquier.xyz>
2021-03-17 08:34         ` Ярослав Пашинский <yarik97.6@gmail.com>
2021-03-17 22:27           ` Michael Paquier <michael@paquier.xyz>
2021-03-18 08:21             ` Ярослав Пашинский <yarik97.6@gmail.com>
2021-03-18 08:43               ` Michael Paquier <michael@paquier.xyz>
2021-03-18 12:16               ` Ярослав Пашинский <yarik97.6@gmail.com>
2021-03-18 21:56                 ` Michael Paquier <michael@paquier.xyz>
2021-03-19 08:08                   ` Ярослав Пашинский <yarik97.6@gmail.com>
2021-03-19 08:28                     ` Michael Paquier <michael@paquier.xyz>
2021-03-19 09:04                       ` Ярослав Пашинский <yarik97.6@gmail.com>
2021-03-19 11:36                         ` Michael Paquier <michael@paquier.xyz>
2021-03-19 11:45                           ` Ярослав Пашинский <yarik97.6@gmail.com>
2021-03-19 15:13                           ` Tom Lane <tgl@sss.pgh.pa.us>
2021-03-19 15:19                             ` Magnus Hagander <magnus@hagander.net>
2021-03-19 22:32                               ` Michael Paquier <michael@paquier.xyz>
2021-03-19 23:56                                 ` Andres Freund <andres@anarazel.de>
2021-03-20 00:31                                   ` Michael Paquier <michael@paquier.xyz>
2021-03-22 05:54                                     ` Michael Paquier <michael@paquier.xyz>
2021-03-23 09:49                                       ` Ярослав Пашинский <yarik97.6@gmail.com>
2021-03-24 00:32                                         ` Michael Paquier <michael@paquier.xyz>
2021-03-20 00:08                                 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-03-18 23:51                 ` Andres Freund <andres@anarazel.de>
2021-03-19 08:38                   ` Ярослав Пашинский <yarik97.6@gmail.com>
2021-03-22 04:15                     ` Michael Paquier <michael@paquier.xyz>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox