Re: SV: Log files polluted with permission denied error messages after every 10 seconds
Hi! I am not completely sure which flags your installation has, but another possibility is that I directly send to you two compiled builds, one with the patch and one without it that you could directly test. I would not send that to the lists as an installation is rather large, but I could just send you links from where you could download both of them. Then you would just need to stop the Postgres service, do a drop-in deplacement of the binaries, and start again the Postgres service. I can try binaries in Intel server which runs Postgres 13.1. I hope its data directory is compatible with them. Andrus.
Re: SV: Log files polluted with permission denied error messages after every 10 seconds
Hi >I am not completely sure which flags your installation has, but pg_config --configure outputs --enable-thread-safety --enable-nls --with-ldap --with-openssl --with-uuid --with-libxml --with-libxslt --with-icu --with-tcl --with-perl --with-python Andrus.
Re: SV: Log files polluted with permission denied error messages after every 10 seconds
On Wed, Mar 17, 2021 at 09:25:00AM +0200, Andrus wrote: > pg_config --configure outputs > > --enable-thread-safety --enable-nls --with-ldap --with-openssl --with-uuid > --with-libxml --with-libxslt --with-icu --with-tcl --with-perl --with-python Thanks. Do you actually use OpenSSL, LDAP, uuid-ossp, xml2, PL/Perl PL/Python, or the XML datatype for your applications there? It may be better if those custom builds have a minimum number of dependencies filled, while still being compatible with what you do on those servers so as they can still have some load applied. -- Michael signature.asc Description: PGP signature
Re: SV: Log files polluted with permission denied error messages after every 10 seconds
>On Wed, Mar 17, 2021 at 09:25:00AM +0200, Andrus wrote: pg_config --configure outputs --enable-thread-safety --enable-nls --with-ldap --with-openssl --with-uuid --with-libxml --with-libxslt --with-icu --with-tcl --with-perl --with-python Thanks. Do you actually use OpenSSL, Not directly. Maybe server uses it for ssl connections but non-ssl connections are also allowed. LDAP no. uuid-ossp, It is used to get guid in few places for compatibility with older servers. xml2, PL/Perl PL/Python, No. or the XML datatype In this server hopefully no. Application code contains xml parsing it but probably those queries are never running in this server. Andrus. for your applications there? It may be better if those custom builds have a minimum number of dependencies filled, while still being compatible with what you do on those servers so as they can still have some load applied. -- Michael
Re: WAL-files is not removing authomaticaly
But maybe there is a way to fix this ? Rebuilding slave from master with
erasing ${PGDATA} on slave does not help.
вт, 16 мар. 2021 г. в 16:56, Laurenz Albe :
> On Tue, 2021-03-16 at 16:11 +0200, Andrew Anderson wrote:
> > postgres=# show wal_keep_segments;
> > wal_keep_segments
> > ---
> > 32
> > (1 row)
>
> Ok, then I am out of ideas.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
Re: SV: Log files polluted with permission denied error messages after every 10 seconds
On Wed, 2021-03-17 at 01:09 +0200, Andrus wrote: > > Those ten seconds are coming from RemoveXlogFile(), where pgrename() > > loops 100 times for 100ms before giving up. So something holding up > > the file's handle prevents the removal to happen. > > I tried sysinternals > > handle pg_wal > > It shows that only postgres processes have opened files in pg_wal directory: > > postgres.exe pid: 11800 type: File CC0: C:\Program > Files\PostgreSQL\13\data\pg_wal\0001001200B7 > postgres.exe pid: 11800 type: File 12B8: C:\Program > Files\PostgreSQL\13\data\pg_wal > postgres.exe pid: 23904 type: File 1134: C:\Program > Files\PostgreSQL\13\data\pg_wal\0001001200B5 > postgres.exe pid: 20908 type: File 17C: C:\Program > Files\PostgreSQL\13\data\pg_wal\0001001200B7 > postgres.exe pid: 29892 type: File C08: C:\Program > Files\PostgreSQL\13\data\pg_wal\0001001100F4 > ... > postgres.exe pid: 34260 type: File C18: C:\Program > Files\PostgreSQL\13\data\pg_wal\0001001100FC > postgres.exe pid: 34484 type: File C48: C:\Program > Files\PostgreSQL\13\data\pg_wal\0001001100FC > postgres.exe pid: 38740 type: File 180: C:\Program > Files\PostgreSQL\13\data\pg_wal\0001001200B7 I think what it would be most helpful to run "process monitor", so that you get a log of the system calls and errors; perhaps that shows some details that we cannot get from the error message. Yours, Laurenz Albe
Re: SV: Log files polluted with permission denied error messages after every 10 seconds
Hi! I think what it would be most helpful to run "process monitor", so that you get a log of the system calls and errors; perhaps that shows some details that we cannot get from the error message. Process monitor shows huge number of DELETE PENDING results from CreateFile operation: 15:22:35,1708187 postgres.exe 11800 CreateFile C:\Program Files\PostgreSQL\13\data\pg_wal\00010011003B DELETE PENDING Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a 15:22:35,2713856 postgres.exe 11800 CreateFile C:\Program Files\PostgreSQL\13\data\pg_wal\00010011003B DELETE PENDING Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a 15:22:35,3719483 postgres.exe 11800 CreateFile C:\Program Files\PostgreSQL\13\data\pg_wal\00010011003B DELETE PENDING Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a 15:22:35,4723624 postgres.exe 11800 CreateFile C:\Program Files\PostgreSQL\13\data\pg_wal\00010011003B DELETE PENDING Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a Andrus.
Getting Involved
Hello, I am a student and i would like to contribute through the GSoc 2021. Do you have any tips for getting started and solving a few simple issues? I cant quite how the whole bug fixing process works. Any help is greatly appreciated!
Re: SV: Log files polluted with permission denied error messages after every 10 seconds
On Wed, 2021-03-17 at 15:26 +0200, Andrus wrote: > > I think what it would be most helpful to run "process monitor", so that you > > get > > a log of the system calls and errors; perhaps that shows some details that > > we cannot get from the error message. > > > > Process monitor shows huge number of DELETE PENDING results from CreateFile > operation: > > 15:22:35,1708187postgres.exe11800CreateFileC:\Program > Files\PostgreSQL\13\data\pg_wal\00010011003BDELETE PENDING > Desired Access: Read Attributes, Delete, > Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Open > Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, > AllocationSize: n/a > 15:22:35,2713856postgres.exe11800CreateFileC:\Program > Files\PostgreSQL\13\data\pg_wal\00010011003BDELETE PENDING > Desired Access: Read Attributes, Delete, > Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Open > Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, > AllocationSize: n/a > > 15:22:35,3719483postgres.exe11800CreateFileC:\Program > Files\PostgreSQL\13\data\pg_wal\00010011003BDELETE PENDING > Desired Access: Read Attributes, Delete, > Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Open > Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, > AllocationSize: n/a > > 15:22:35,4723624postgres.exe11800CreateFileC:\Program > Files\PostgreSQL\13\data\pg_wal\00010011003BDELETE PENDING > Desired Access: Read Attributes, Delete, > Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Open > Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, > AllocationSize: n/a Doesn't look like these are error messages. There should be error messages that correspond to the error messages you see in the PostgreSQL log. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Portal name in cursor
Hello Does anyone know why assigning a string to a refcursor before opening it makes fetching data faster from a cursor? begin; select * from func1() ; fetch all from "test"; end; in func1 i set refcursor_variable := 'test';
Re: Getting Involved
Em qua., 17 de mar. de 2021 às 10:27, Πανος Κοροβεσης < panoskorove...@outlook.com> escreveu: > > Hello, > > I am a student and i would like to contribute through the GSoc 2021. Do you have any tips for getting started and solving a few simple issues? I cant quite how the whole bug fixing process works. > > Any help is greatly appreciated! Hi, First of all be welcome... the links below can help you to solve all initial doubts about the GSoC: https://wiki.postgresql.org/wiki/GSoC https://wiki.postgresql.org/wiki/GSoC_2021 Regards, -- Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Re: SV: Log files polluted with permission denied error messages after every 10 seconds
Hi! Doesn't look like these are error messages. There should be error messages that correspond to the error messages you see in the PostgreSQL log. I excluded SUCCESS and DELETE PENDING results and included pg_wal path and postgres.exe process. Log file contains only few entries, BUFFER OVERFLOW and NAME NOT FOUND 17:07:09,9618758 postgres.exe 11800 QueryAllInformationFile C:\Program Files\PostgreSQL\13\data\pg_wal\000100130021 BUFFER OVERFLOW CreationTime: 16.03.2021 20:04:57, LastAccessTime: 17.03.2021 16:58:43, LastWriteTime: 17.03.2021 16:58:43, ChangeTime: 17.03.2021 16:58:43, FileAttributes: A, AllocationSize: 16 777 216, EndOfFile: 16 777 216, NumberOfLinks: 1, DeletePending: False, Directory: False, IndexNumber: 0x3810009ffb4, EaSize: 0, Access: Read Attributes, Synchronize, Position: 0, Mode: Synchronous IO Non-Alert, AlignmentRequirement: Byte 17:07:09,9624993 postgres.exe 11800 QueryAllInformationFile C:\Program Files\PostgreSQL\13\data\pg_wal\000100130023 BUFFER OVERFLOW CreationTime: 16.03.2021 20:04:57, LastAccessTime: 17.03.2021 17:07:09, LastWriteTime: 17.03.2021 17:07:09, ChangeTime: 17.03.2021 17:07:09, FileAttributes: A, AllocationSize: 16 777 216, EndOfFile: 16 777 216, NumberOfLinks: 1, DeletePending: False, Directory: False, IndexNumber: 0x2000a010c, EaSize: 0, Access: Read Attributes, Synchronize, Position: 0, Mode: Synchronous IO Non-Alert, AlignmentRequirement: Byte 17:07:09,9630741 postgres.exe 11800 QueryAllInformationFile C:\Program Files\PostgreSQL\13\data\pg_wal\000100130024 BUFFER OVERFLOW CreationTime: 16.03.2021 20:04:57, LastAccessTime: 16.03.2021 20:05:12, LastWriteTime: 16.03.2021 20:05:12, ChangeTime: 16.03.2021 20:15:24, FileAttributes: A, AllocationSize: 16 777 216, EndOfFile: 16 777 216, NumberOfLinks: 1, DeletePending: False, Directory: False, IndexNumber: 0x2000a010d, EaSize: 0, Access: Read Attributes, Synchronize, Position: 0, Mode: Synchronous IO Non-Alert, AlignmentRequirement: Byte 17:07:09,9636100 postgres.exe 11800 QueryAllInformationFile C:\Program Files\PostgreSQL\13\data\pg_wal\000100130025 BUFFER OVERFLOW CreationTime: 16.03.2021 20:04:57, LastAccessTime: 16.03.2021 20:05:12, LastWriteTime: 16.03.2021 20:05:12, ChangeTime: 16.03.2021 20:15:24, FileAttributes: A, AllocationSize: 16 777 216, EndOfFile: 16 777 216, NumberOfLinks: 1, DeletePending: False, Directory: False, IndexNumber: 0x2000a010e, EaSize: 0, Access: Read Attributes, Synchronize, Position: 0, Mode: Synchronous IO Non-Alert, AlignmentRequirement: Byte 17:07:09,9641594 postgres.exe 11800 QueryAllInformationFile C:\Program Files\PostgreSQL\13\data\pg_wal\000100130026 BUFFER OVERFLOW CreationTime: 16.03.2021 20:04:57, LastAccessTime: 16.03.2021 20:05:13, LastWriteTime: 16.03.2021 20:05:13, ChangeTime: 16.03.2021 20:15:24, FileAttributes: A, AllocationSize: 16 777 216, EndOfFile: 16 777 216, NumberOfLinks: 1, DeletePending: False, Directory: False, IndexNumber: 0x2000a010f, EaSize: 0, Access: Read Attributes, Synchronize, Position: 0, Mode: Synchronous IO Non-Alert, AlignmentRequirement: Byte 17:07:09,9659048 postgres.exe 11800 CreateFile C:\Program Files\PostgreSQL\13\data\pg_wal IS DIRECTORY Desired Access: Generic Read, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, Delete, AllocationSize: n/a 17:07:09,9661026 postgres.exe 11800 QueryAllInformationFile C:\Program Files\PostgreSQL\13\data\pg_wal BUFFER OVERFLOW CreationTime: 10.02.2021 14:07:18, LastAccessTime: 17.03.2021 17:07:09, LastWriteTime: 17.03.2021 17:07:09, ChangeTime: 17.03.2021 17:07:09, FileAttributes: D, AllocationSize: 196 608, EndOfFile: 196 608, NumberOfLinks: 1, DeletePending: False, Directory: True, IndexNumber: 0x100055e2b, EaSize: 0, Access: Read Attributes, Synchronize, Position: 0, Mode: Synchronous IO Non-Alert, AlignmentRequirement: Byte 17:07:09,9666396 postgres.exe 11800 CreateFile C:\Program Files\PostgreSQL\13\data\pg_wal\archive_status\000100130021.done NAME NOT FOUND Desired Access: Read Attributes, Delete, Disposition: Open, Options: Non-Directory File, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a 17:07:09,9667813 postgres.exe 11800 CreateFile C:\Program Files\PostgreSQL\13\data\pg_wal\archive_status\000100130021.ready NAME NOT FOUND Desired Access: Read Attributes, Delete, Disposition: Open, Options: Non-Directory File, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a 17:07:09,9668956 postgres.exe 11800 QueryDirectory C:\Program Files\PostgreSQL\13\data\pg_wal NO MORE FILES FileInformationClass: FileBothDirectoryInformation Andrus.
Re: SV: Log files polluted with permission denied error messages after every 10 seconds
Hi! BUFFER OVERFLOW result stack trace is "Frame","Module","Location","Address","Path" "0","FLTMGR.SYS","FltDecodeParameters + 0x1c5d","0xf8019f72555d","C:\Windows\System32\drivers\FLTMGR.SYS" "1","FLTMGR.SYS","FltDecodeParameters + 0x17bc","0xf8019f7250bc","C:\Windows\System32\drivers\FLTMGR.SYS" "2","FLTMGR.SYS","FltDecodeParameters + 0x1328","0xf8019f724c28","C:\Windows\System32\drivers\FLTMGR.SYS" "3","FLTMGR.SYS","FltDecodeParameters + 0x111e","0xf8019f724a1e","C:\Windows\System32\drivers\FLTMGR.SYS" "4","ntoskrnl.exe","IofCallDriver + 0x59","0xf80051856109","C:\Windows\system32\ntoskrnl.exe" "5","ntoskrnl.exe","KeIsAttachedProcess + 0xf3","0xf80051929343","C:\Windows\system32\ntoskrnl.exe" "6","ntoskrnl.exe","NtQueryInformationFile + 0x492","0xf80051e8b5c2","C:\Windows\system32\ntoskrnl.exe" "7","ntoskrnl.exe","setjmpex + 0x7905","0xf800519e6705","C:\Windows\system32\ntoskrnl.exe" "8","","0x7ffc7a17f9e4","0x7ffc7a17f9e4","" "9","","0x7ffc7639af04","0x7ffc7639af04","" "10","","0x7ffc7728fe86","0x7ffc7728fe86","" "11","","0x7ffc7728f622","0x7ffc7728f622","" "12","","0x7ffc77290a46","0x7ffc77290a46","" "13","","0x14048ccca","0x14048ccca","" "14","","0x14009463b","0x14009463b","" "15","","0x140094365","0x140094365","" "16","","0x14008e541","0x14008e541","" "17","","0x140286f46","0x140286f46","" "18","","0x1400a17ca","0x1400a17ca","" "19","","0x1402923cb","0x1402923cb","" "20","","0x1401b95c0","0x1401b95c0","" "21","","0x14049f304","0x14049f304","" "22","","0x7ffc77bb7974","0x7ffc77bb7974","" "23","","0x7ffc7a13a2d1","0x7ffc7a13a2d1","" It looks like too small buffer is passed to NtQueryInformationFile . Andrus.
Re: SV: Log files polluted with permission denied error messages after every 10 seconds
Andrus writes: > It looks like too small buffer is passed to NtQueryInformationFile . Oh! That's an interesting theory; it'd explain why this broke recently, because we didn't use to use that function. But how do you draw that conclusion from this stack trace? Anyway, if you've diagnosed this correctly, I bet the fault is in this bit in win32stat.c: #if _WIN32_WINNT < 0x0600 IO_STATUS_BLOCK ioStatus; FILE_STANDARD_INFORMATION standardInfo; #else FILE_STANDARD_INFO standardInfo; #endif Maybe the version cutoff is wrong? Maybe we have to do this with a run-time version check, instead of statically compiling it? regards, tom lane
how to troubleshoot: FATAL: canceling authentication due to timeout
hi all, We are facing a problem with a user login into database. It happens when there is large load and only from time to time. Once we get this error, the user becomes unusable until database is restarted. (That user is being used by multiple instances of the same application, it also happens using dedicated users for each application, resulting on one of those users being locked out, the rest keep working fine) The errors is as follows: LOG: pam_authenticate failed: Authentication failure FATAL: canceling authentication due to timeout Our setup: 3 nodes cluster - Centos 7 - Streaming replication in place (async) - WAL shipped to an external location - pooling done at client side - Centos joined to an Active Directory domain - Authentication is using PAM module User is completely fine in AD side since i can use it to login to a standby DB. I guess there must be a lock that prevents this user to do the first authentication step, but no idea how to find it. I’ve tried common queries to find locks but I can’t see anything relevant. I would appreciate if someone could point me to the right direction! Thanks a lot! Marc.
Re: SV: Log files polluted with permission denied error messages after every 10 seconds
On Wed, Mar 17, 2021 at 11:44:48AM -0400, Tom Lane wrote: > Oh! That's an interesting theory; it'd explain why this broke recently, > because we didn't use to use that function. But how do you draw that > conclusion from this stack trace? > > Anyway, if you've diagnosed this correctly, I bet the fault is in > this bit in win32stat.c: > > #if _WIN32_WINNT < 0x0600 > IO_STATUS_BLOCK ioStatus; > FILE_STANDARD_INFORMATION standardInfo; > #else > FILE_STANDARD_INFO standardInfo; > #endif > > Maybe the version cutoff is wrong? Maybe we have to do this with > a run-time version check, instead of statically compiling it? All the reports received are on 13.1 and 13.2. This code is new as of bed9075, no? -- Michael signature.asc Description: PGP signature
Re: SV: Log files polluted with permission denied error messages after every 10 seconds
Michael Paquier writes: > On Wed, Mar 17, 2021 at 11:44:48AM -0400, Tom Lane wrote: >> Oh! That's an interesting theory; it'd explain why this broke recently, >> because we didn't use to use that function. But how do you draw that >> conclusion from this stack trace? > All the reports received are on 13.1 and 13.2. This code is new as of > bed9075, no? Ah, right. But then this theory must be nonsense, because we did not use NtQueryInformationFile() in v13. regards, tom lane
Re: how to troubleshoot: FATAL: canceling authentication due to timeout
hi! What you see in the log files of CentOS ( /var/log ) ? i On 17/03/2021 16:00, Marc wrote: hi all, We are facing a problem with a user login into database. It happens when there is large load and only from time to time. Once we get this error, the user becomes unusable until database is restarted. (That user is being used by multiple instances of the same application, it also happens using dedicated users for each application, resulting on one of those users being locked out, the rest keep working fine) The errors is as follows: LOG: pam_authenticate failed: Authentication failure FATAL: canceling authentication due to timeout Our setup: 3 nodes cluster - Centos 7 - Streaming replication in place (async) - WAL shipped to an external location - pooling done at client side - Centos joined to an Active Directory domain - Authentication is using PAM module User is completely fine in AD side since i can use it to login to a standby DB. I guess there must be a lock that prevents this user to do the first authentication step, but no idea how to find it. I’ve tried common queries to find locks but I can’t see anything relevant. I would appreciate if someone could point me to the right direction! Thanks a lot! Marc.
Re: SV: Log files polluted with permission denied error messages after every 10 seconds
FWIW, this looks the same issue I am getting (reported last month: https://www.postgresql.org/message-id/f444a84e-2d29-55f9-51a6-a5dcea3bc253%40burgess.co.nz) I get the same Process Monitor output, including BUFFER OVERFLOW entries. No sign of any process other than postgres.exe touching the WAL files. Regards, Guy On 18/03/2021 2:26 am, Andrus wrote: Hi! I think what it would be most helpful to run "process monitor", so that you get a log of the system calls and errors; perhaps that shows some details that we cannot get from the error message. Process monitor shows huge number of DELETE PENDING results from CreateFile operation: 15:22:35,1708187 postgres.exe 11800 CreateFile C:\Program Files\PostgreSQL\13\data\pg_wal\00010011003B DELETE PENDING Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a 15:22:35,2713856 postgres.exe 11800 CreateFile C:\Program Files\PostgreSQL\13\data\pg_wal\00010011003B DELETE PENDING Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a 15:22:35,3719483 postgres.exe 11800 CreateFile C:\Program Files\PostgreSQL\13\data\pg_wal\00010011003B DELETE PENDING Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a 15:22:35,4723624 postgres.exe 11800 CreateFile C:\Program Files\PostgreSQL\13\data\pg_wal\00010011003B DELETE PENDING Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a Andrus.
Re: SV: Log files polluted with permission denied error messages after every 10 seconds
On Wed, Mar 17, 2021 at 10:45:28AM +0200, Andrus wrote: > In this server hopefully no. Application code contains xml parsing it but > probably those queries are never running in this server. Okay, cool. I am going to send you privately two links to the builds I am going to produce, 13.2 unpatched and 13.2 patched. -- Michael signature.asc Description: PGP signature
Re: SV: Log files polluted with permission denied error messages after every 10 seconds
On Thu, Mar 18, 2021 at 12:25:45PM +1300, Guy Burgess wrote: > FWIW, this looks the same issue I am getting (reported last month: > https://www.postgresql.org/message-id/f444a84e-2d29-55f9-51a6-a5dcea3bc253%40burgess.co.nz) Yep. > I get the same Process Monitor output, including BUFFER OVERFLOW entries. > No sign of any process other than postgres.exe touching the WAL files. Guy, do you have an environment where this is still happening and where you could test a potential fix? We are not sure yet what's causing that, but one code path has changed in this area, involving CreateHardLinkA()+_unlink() instead of a single rename when attempting to recycle a segment. And I am just in a mood to build things by myself and send some links to people to be able to download and test that, so one more is fine.. -- Michael signature.asc Description: PGP signature
Re: SV: Log files polluted with permission denied error messages after every 10 seconds
Thanks - yes pleas send me a private link and I will try to reproduce it in a test environment and then test the patch. Regards, Guy On 18/03/2021 1:08 pm, Michael Paquier wrote: On Thu, Mar 18, 2021 at 12:25:45PM +1300, Guy Burgess wrote: FWIW, this looks the same issue I am getting (reported last month: https://www.postgresql.org/message-id/f444a84e-2d29-55f9-51a6-a5dcea3bc253%40burgess.co.nz) Yep. I get the same Process Monitor output, including BUFFER OVERFLOW entries. No sign of any process other than postgres.exe touching the WAL files. Guy, do you have an environment where this is still happening and where you could test a potential fix? We are not sure yet what's causing that, but one code path has changed in this area, involving CreateHardLinkA()+_unlink() instead of a single rename when attempting to recycle a segment. And I am just in a mood to build things by myself and send some links to people to be able to download and test that, so one more is fine.. -- Michael
Re: how to troubleshoot: FATAL: canceling authentication due to timeout
Hi, Not much, we don't see any failed login. We have added debug login into sssd service since we just found out that restarting sssd released the user and it became usable again. So there must be something wrong between postgres and sssd/pam modules... Waiting now for fresh logs if it happens again. Thanks! On Wed, 17 Mar 2021, 22:32 Diego, wrote: > hi! > > What you see in the log files of CentOS ( /var/log ) ? > i > > > On 17/03/2021 16:00, Marc wrote: > > hi all, > > We are facing a problem with a user login into database. It happens when > there is large load and only from time to time. > Once we get this error, the user becomes unusable until database is > restarted. (That user is being used by multiple instances of the same > application, it also happens using dedicated users for each application, > resulting on one of those users being locked out, the rest keep working fine) > > The errors is as follows: > LOG: pam_authenticate failed: Authentication failure > FATAL: canceling authentication due to timeout > > Our setup: > 3 nodes cluster > - Centos 7 > - Streaming replication in place (async) > - WAL shipped to an external location > - pooling done at client side > - Centos joined to an Active Directory domain > - Authentication is using PAM module > > User is completely fine in AD side since i can use it to login to a standby > DB. > I guess there must be a lock that prevents this user to do the first > authentication step, but no idea how to find it. I’ve tried common queries to > find locks but I can’t see anything relevant. > > I would appreciate if someone could point me to the right direction! > > Thanks a lot! > Marc. > > > > >
Re: WAL-files is not removing authomaticaly
Deleting replication slot on slave resolve the issue, now WAL-files
removing automaticaly ! Thanks a lot for your answers !
ср, 17 мар. 2021 г. в 10:59, Andrew Anderson :
> But maybe there is a way to fix this ? Rebuilding slave from master with
> erasing ${PGDATA} on slave does not help.
>
> вт, 16 мар. 2021 г. в 16:56, Laurenz Albe :
>
>> On Tue, 2021-03-16 at 16:11 +0200, Andrew Anderson wrote:
>> > postgres=# show wal_keep_segments;
>> > wal_keep_segments
>> > ---
>> > 32
>> > (1 row)
>>
>> Ok, then I am out of ideas.
>>
>> Yours,
>> Laurenz Albe
>> --
>> Cybertec | https://www.cybertec-postgresql.com
>>
>>
