RE: Troubleshooting a segfault and instance crash - Mailing list pgsql-general

From Jan Bilek
Subject RE: Troubleshooting a segfault and instance crash
Date
Msg-id SYXPR01MB0701D7C6B50B8D2C6B862117B5AE0@SYXPR01MB0701.ausprd01.prod.outlook.com
Whole thread Raw
In response to Re: Troubleshooting a segfault and instance crash  (Blair Boadway <bboadway@abebooks.com>)
List pgsql-general
Hi Blair,

In regards of that debug package i found it here: http://cbs.centos.org/koji/buildinfo?buildID=20425 , see http://cbs.centos.org/kojifiles/packages/rh-postgresql96-postgresql/9.6.5/1.el7/x86_64/rh-postgresql96-postgresql-debuginfo-9.6.5-1.el7.x86_64.rpm

However I have very little experience with it to provide more instructions.


There is also an option to get your own debugging symbols from building postgresql server with those and stripping them. I've found pretty good example here: http://marcioandreyoliveira.blogspot.com.au/2008/03/how-to-debug-striped-programs-with-gdb.html

Finally, looking into that Seg. Fault, strcmp is pretty common-nasty error. Most probably buffer overflow, where RHEL is more sensitive OS on this then the others. This is where postgresql pays a toll for being written in C and not e.g. C++. It can be practically anywhere.

Anyway, by being able to link those debugging symbols to your core dump, we should immediately see where it is and you'll do a great help to the community. I'm sure that then Pavel will be able to issue a fix in a matter of minutes ;)

Kind Regards,
Jan
 
-- 
Jan Bilek

CTO, EFTLab 

M: +61 (0) 498 103 179
E:  jan.bilek@eftlab.com.au
A: 109 Brighton Road, Sandgate, QLD 4017

IMPORTANT NOTICE 
This message contains confidential information and is intended only for the addressee(s). E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. EFTlab Pty Ltd cannot accept liability for any errors or omissions in the contents of this message, which may arise as a result of e-mail transmission. Please note that EFTlab Pty Ltd may monitor, analyse and archive email traffic, data and the content of email for the purposes of security, legal compliance and staff training. If you have received this email in error please notify us at support@eftlab.com.au
 

On 2018-03-25 08:44:21+10:00 Blair Boadway wrote:

Following up on this thread, we removed pgaudit from the system to eliminate on variable (removed from postgres.conf including shared_preload_libraries) but after a couple of weeks of success we hit the segfault again.  Again it happened while running some DDL (object grants).  This time we were configured to harvest a core file, which gave us a small bit of info:

 

gdb -q -c core /usr/pgsql-9.6/bin/postgres

Reading symbols from /usr/pgsql-9.6/bin/postgres...(no debugging symbols found)...done.

<many more lines such as this with no debugging symbols found>

Core was generated by `postgres: batch_user_account'.

Program terminated with signal 11, Segmentation fault.

#0  0x000000386712868a in __strcmp_sse42 () from /lib64/libc.so.6

Missing separate debuginfos, use: debuginfo-install postgresql96-server-9.6.5-1PGDG.rhel6.x86_64

 

That wasn’t really enough information to tell me what the problem.  Did not have success with installing debuginfo:

 

Could not find debuginfo for main pkg: postgresql96-server-9.6.5-1PGDG.rhel6.x86_64

 

Not sure how useful that would be to dig further on.  So it doesn’t seem pgaudit is the culprit but not sure what to make of the strcmp error.

 

-Blair

 

 

From: Jan Bilek <jan.bilek@eftlab.com.au>
Date: Thursday, March 8, 2018 at 2:56 PM
To: "pavel.stehule@gmail.com" <pavel.stehule@gmail.com>, Blair Boadway <bboadway@abebooks.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: RE: Troubleshooting a segfault and instance crash

 

Hi Blair, Pavel,

we are using procedure described in
https://access.redhat.com/solutions/4896  to automate crash detail collection for our production systems on RHEL 7.

Perhaps something like this can help on your side.

Kind Regards,
Jan

On 2018-03-09 04:35:05+10:00 Pavel Stehule wrote:

 

 

2018-03-08 19:16 GMT+01:00 Blair Boadway <bboadway@abebooks.com>:

Hi Pavel,

 

I don’t have a core yet, the only way I have now is to intentionally crash the prod system a couple of times.  Haven’t resorted to that yet.

hard to help without backtrace - and then you need core dump
 

 

Interesting you mentioned pgaudit—it is installed on this system because that is a our standard installation but on this particular system we haven’t yet needed audits so the audit role is ‘empty’.  (And on a different system with same installation and heavy of audit we’ve seen no segfaults)

other extensions are simply or without relation to DDL or well known. So pgaudit is best candidate - but the error can be anywhere
 

Regards
 

Pavel

On this system

 

pgaudit.role = 'auditor'

pgaudit.log_parameter = off

pgaudit.log_catalog = off

pgaudit.log_statement_once = on

pgaudit.log_level = log

 

select * from information_schema.role_table_grants where grantee = 'auditor';

(0 rows)

 

thanks, Blair

 

From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Thursday, March 8, 2018 at 9:49 AM
To: Blair Boadway <
bboadway@abebooks.com>
Cc: "
pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: Troubleshooting a segfault and instance crash

Hi

 

2018-03-08 18:40 GMT+01:00 Blair Boadway <bboadway@abebooks.com>:

Hello,

 

We’re seeing an occasional segfault on a particular database

 

Mar  7 14:46:35 pgprod2 kernel:postgres[29351]: segfault at 0 ip 000000302f32868a sp 00007ffcf1547498 error 4 in libc-2.12.so[302f200000+18a000]

Mar  7 14:46:35 pgprod2 POSTGRES[21262]: [5] user=,db=,app=client= LOG:  server process (PID 29351) was terminated by signal 11: Segmentation fault

 

It crashes the database, though it starts again on its own without any apparent issues.  This has happened 3 times in 2 months and each time the segfault error and memory address is the same. We’ve only seen it on one database, though we’ve seen it on both hosts of primary/standby setup—we switched over primary to other host and got a segfault there, which seems to eliminate a hardware issue.  Oddly the database has no issues for normal DML workloads (it is a moderately busy prod oltp system) but the segfault has happened very shortly after DML changes are made.  Most recently it happened while running a series of grants for new db users we were deploying (ie. running a sql script from psql on the primary host)

 

grant usage on schema app to app_user1;

grant usage on schema app to app_user2;

...

 

Our set up is

RHEL 6.9  - 2.6.32-696.16.1.el6.x86_64

PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit

Extensions - pg_cron,repmgr_funcs,pgaudit,pg_stat_statements,pg_hint_plan,pglogical

 

So far can’t reproduce on a test system, have just added some OS config to collect core from the OS but haven’t collected a core yet.  There isn’t any particular config change or extension that we can link to the problem, this is a system that has run for months without problems since last config changes.  Appreciate any ideas.

can you get core dump? It can be pgaudit bug maybe? It is complex extension.

Regards

Pavel

 

Regards,

Blair

pgsql-general by date:

Previous
From: Blair Boadway
Date:
Subject: Re: Troubleshooting a segfault and instance crash
Next
From: Pavel Stehule
Date:
Subject: Re: Troubleshooting a segfault and instance crash