RE: Troubleshooting a segfault and instance crash

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(dot)bilek(at)eftlab(dot)com(dot)au<mailto:jan(dot)bilek(at)eftlab(dot)com(dot)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(at)eftlab(dot)com(dot)au<mailto:support(at)eftlab(dot)com(dot)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(dot)bilek(at)eftlab(dot)com(dot)au>
Date: Thursday, March 8, 2018 at 2:56 PM
To: "pavel(dot)stehule(at)gmail(dot)com" <pavel(dot)stehule(at)gmail(dot)com>, Blair Boadway <bboadway(at)abebooks(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)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(at)abebooks(dot)com<mailto:bboadway(at)abebooks(dot)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(dot)stehule(at)gmail(dot)com<mailto:pavel(dot)stehule(at)gmail(dot)com>>
Date: Thursday, March 8, 2018 at 9:49 AM
To: Blair Boadway <bboadway(at)abebooks(dot)com<mailto:bboadway(at)abebooks(dot)com>>
Cc: "pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>" <pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>>
Subject: Re: Troubleshooting a segfault and instance crash
Hi

2018-03-08 18:40 GMT+01:00 Blair Boadway <bboadway(at)abebooks(dot)com<mailto:bboadway(at)abebooks(dot)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<http://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