Обсуждение: BUG #16351: PostgreSQL closing connection during requests with segmentation fault

The following bug has been logged on the website:

Bug reference:      16351
Logged by:          Johannes Mols
Email address:      johannes.mols@googlemail.com
PostgreSQL version: 10.12
Operating system:   Ubuntu 18.04
Description:

I originally posted this on StackExchange yesterday, but someone recommended
me to post this here:
https://dba.stackexchange.com/questions/264530/postgresql-closing-connection-during-requests

I have a PostgreSQL 10.12 server running on an Ubuntu 18.04 Server. Since
today, I am suddenly getting errors when querying data from a Python script
running on the same machine, and in some edge cases also in pgAdmin 4.

In about 50% of the cases, my Python script will fail to return any data
because the DB server closed the connection. I am using psycopg2 and it is
giving me the following error:

psycopg2.errors.AdminShutdown: terminating connection due to administrator
command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

Interestingly, the same queries (which are simple "SELECT * FROM table"
statements) run perfectly fine in pgAdmin. However, when I try to run the
following query in pgAdmin, I get the message Connection to the server has
been lost.

select * from mytable where mycolumn <> ' '

This query specifically worked fine a few days ago. I couldn't find any
other examples where this happens in pgAdmin, but there is surely something
wrong.

What is weird is that the database has been running for over a month without
any issues, and this only started today. I did try to restart the service to
no avail.

The logs are not making much sense to me, but there are several things
wrong.

The last few days, it constantly gave me the following message:

2020-04-06 19:43:26.959 CEST [7820] postgres@postgres FATAL:  password
authentication failed for user "postgres"
2020-04-06 19:43:26.959 CEST [7820] postgres@postgres DETAIL:  Password does
not match for user "postgres".
    Connection matched pg_hba.conf line 102: "host    all             all
         all                     md5"

I am certain that I am using the correct password, as it does work 50% of
the time of the queries. It is showing this message for the default postgres
database and not my own, but it still confuses me why this happens as I'm
using the default postgres user for both databases. I got this message a lot
during the night where I was not doing anything with it either.

For the actual error regarding the query from pgAdmin, this is what I get:

2020-04-07 11:25:18.891 CEST [5497] LOG:  server process (PID 20569) was
terminated by signal 11: Segmentation fault
2020-04-07 11:25:18.891 CEST [5497] DETAIL:  Failed process was running:
SELECT * FROM transactionoutputs WHERE fileheader <> ''
2020-04-07 11:25:18.891 CEST [5497] LOG:  terminating any other active
server processes
2020-04-07 11:25:18.891 CEST [20567] postgres@bitcoin WARNING:  terminating
connection because of crash of another server process
2020-04-07 11:25:18.891 CEST [20567] postgres@bitcoin DETAIL:  The
postmaster has commanded this server process to roll back the current
transaction and exit, because another server process exited abnormally and
possibly corrupted shared memory.
2020-04-07 11:25:18.891 CEST [20567] postgres@bitcoin HINT:  In a moment you
should be able to reconnect to the database and repeat your command.
I can't seem to find any errors in the logs for the regular SELECT queries
from my Python scripts, so I don't know why the connection is closed for
this all the time. What is so confusing about this is that it worked
perfectly fine just a yesterday, and I have not changed anything. Restarting
PostgreSQL also did not help.

I also constantly get this message when trying to query data through my
Python API using Postman:

2020-04-07 11:25:18.891 CEST [9215] postgres@bitcoin WARNING:  terminating
connection because of crash of another server process
2020-04-07 11:25:18.891 CEST [9215] postgres@bitcoin DETAIL:  The postmaster
has commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2020-04-07 11:25:18.891 CEST [9215] postgres@bitcoin HINT:  In a moment you
should be able to reconnect to the database and repeat your command.

You can see the full log of the last ~3 days here:
https://pastebin.com/iBC1G2Qg

I'm not an expert with databases or Linux, so any help why this might happen
and how I can figure out what exactly is happening is highly appreciated,
thank you.

>> I have a PostgreSQL 10.12 server running on an Ubuntu 18.04 Server. Since
>> today, I am suddenly getting errors when querying data from a Python script
>> running on the same machine, and in some edge cases also in pgAdmin 4.
>>
>> In about 50% of the cases, my Python script will fail to return any data
>> because the DB server closed the connection. I am using psycopg2 and it is
>> giving me the following error:
>>
>> psycopg2.errors.AdminShutdown: terminating connection due to administrator
>> command
>> server closed the connection unexpectedly
>>     This probably means the server terminated abnormally
>>     before or while processing the request.
>>

On Thu, 9 Apr 2020 at 00:15, Hamid Akhtar <hamid.akhtar@gmail.com> wrote:
>
> Unfortunately, there isn't enough information here for anybody to reproduce the issue.
> Perhaps you can start by validating your own environment for any changes since the
> database server was running fine for almost a month before you started seeing this
> issue.
>
> For anybody to be of any help, they'll need complete steps to reproduce this issue,
> and a core dump that carries enough debugging information to track the problem.


Weirdly enough, we have received a handful of bug reports in the last
24 hours about unexpected disconnections:

- https://github.com/psycopg/psycopg2/issues/1078
- https://github.com/psycopg/psycopg2/issues/1079

None of the OPs haven't specified their network configurations though.
A similar report a few days ago
(https://github.com/psycopg/psycopg2/issues/1066) had Azure to blame.

-- Daniele

Hamid Akhtar <hamid.akhtar@gmail.com> writes:
> Unfortunately, there isn't enough information here for anybody to reproduce
> the issue.

Yeah.  There is some info here about extracting a stack trace, which
could be quite helpful:

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

            regards, tom lane

On Wed, Apr 08, 2020 at 04:58:04PM +0200, Johannes Mols wrote:
> I've tried to that but I'm unfortunately struggling to install the debug
> symbols (https://wiki.ubuntu.com/DebuggingProgramCrash).
> I can't seem to figure out how to do this. Is there any guide that is
> easier to understand for someone who isn't crazy knowledgeable about Linux?
> I would really like to provide you with the information you need, I have no
> idea how to solve this myself and if it is a bug or if something went wrong
> on my end.

Did you try to install the debug symbols using the commands on the
wiki and what kind of failure did you see then?  When it comes to
Ubuntu and Debian, there are a set of -debug RPMs which can be
installed to introduce all the debug symbols which are needed to get a
clear stack trace from a Postgres backend.  Also, your bug report
mentions Postgres 10.12.  Is that a new issue with this minor version?
Were things working as you expected with older versions?
--
Michael

Johannes Mols <johannes.mols@gmail.com> writes:
> I did, but I'm unable to do so. The wiki asks me to enable debug packages
> as described here:  https://wiki.ubuntu.com/DebuggingProgramCrash
> I then found the postgresql-10-dbg package here:
> https://www.ubuntuupdates.org/package/postgresql/trusty-pgdg/main/base/postgresql-10-dbg
> ...
> The following packages have unmet dependencies:
>  postgresql-10-dbg : Depends: postgresql-10 (= 10.8-1.pgdg14.04+1) but
> 10.12-0ubuntu0.18.04.1 is to be installed

That's the wrong repo, evidently.  You got the code from the PGDG
repository, so you need to get the matching debug-symbol package
from the same place.  I gather you don't have the PGDG repo enabled
by default.  I'm not a debian/ubuntu person so I'm not sure about
the exact commands to enable a non-default repo for apt-get.

It looks like there may be a version skew problem as well.  10.12
is the latest v10 release, 10.8 is, um, not.  If you are still on
10.8 then it would behoove you to first update to 10.12 and see
if maybe we already fixed the bug.  In any case, you have to find
a debug-symbol package that *exactly* matches your installed code,
or it's worthless.

            regards, tom lane

    Johannes Mols wrote:

> I then found the postgresql-10-dbg package here:
> https://www.ubuntuupdates.org/package/postgresql/trusty-pgdg/main/base/postgresql-10-dbg
> However, when I try to install it I get the following error:

"trusty" is the codename for ubuntu 14.04 (now obsolete), but
you're using 18.04 so this package is not what you want.

Normally you don't need to search for packages on websites.
If you installed postgresql-10 with "sudo apt install postgresql-10"
(or "sudo apt install postgresql" choosing version 10 automatically)
you may install the debug symbols with
"sudo apt install postgresql-10-dbgsym"


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite