Re: 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(dot)mols(at)googlemail(dot)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(at)postgres FATAL: password
authentication failed for user "postgres"
2020-04-06 19:43:26.959 CEST [7820] postgres(at)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(at)bitcoin WARNING: terminating
connection because of crash of another server process
2020-04-07 11:25:18.891 CEST [20567] postgres(at)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(at)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(at)bitcoin WARNING: terminating
connection because of crash of another server process
2020-04-07 11:25:18.891 CEST [9215] postgres(at)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(at)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.

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.

On Wed, Apr 8, 2020 at 3:45 PM PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 16351
> Logged by: Johannes Mols
> Email address: johannes(dot)mols(at)googlemail(dot)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(at)postgres FATAL: password
> authentication failed for user "postgres"
> 2020-04-06 19:43:26.959 CEST [7820] postgres(at)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(at)bitcoin WARNING:
> terminating
> connection because of crash of another server process
> 2020-04-07 11:25:18.891 CEST [20567] postgres(at)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(at)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(at)bitcoin WARNING: terminating
> connection because of crash of another server process
> 2020-04-07 11:25:18.891 CEST [9215] postgres(at)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(at)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.
>
>

--
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
ADDR: 10318 WHALLEY BLVD, Surrey, BC
CELL：+923335449950 EMAIL: mailto:hamid(dot)akhtar(at)highgo(dot)ca
SKYPE: engineeredvirus

>> 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(dot)akhtar(at)gmail(dot)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

Hi Daniele,

That is strange. My configuration is that it is a remote Ubuntu machine and
I'm connecting over the regular port in pgAdmin.
My Python script is running on the same machine though, so it's just using
localhost as the connection.

Johannes

On Wed, Apr 8, 2020 at 3:52 PM Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
wrote:

> >> 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(dot)akhtar(at)gmail(dot)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(dot)akhtar(at)gmail(dot)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

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.

Best regards,
Johannes Mols

On Wed, Apr 8, 2020 at 4:11 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Hamid Akhtar <hamid(dot)akhtar(at)gmail(dot)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

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
However, when I try to install it I get the following error:

ubuntu(at)vm:~$ sudo apt-get install postgresql-10-dbg
Reading package lists... Done
Building dependency tree
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

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
E: Unable to correct problems, you have held broken packages.

I can't make a lot of sense of that, since: postgresql-10 is already the
newest version (10.12-0ubuntu0.18.04.1).
It might just be me not being very knowledgeable with Linux, but the
instructions to install all of this and finally get a stack trace are not
clear to me at all.
I would really like to get to the bottom of this since the database is now
also closing connections with my data collection services in the middle of
transactions.

About your question about the version: 10.12 is the first and only version
I have installed. When I installed it about a month ago, this was the
newest version, at least from the instructions that I looked at.
Should I try to upgrade to 12.x?

Best regards,
Johannes

On Thu, Apr 9, 2020 at 3:48 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:

> 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(dot)mols(at)gmail(dot)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

Thank you for the info. I couldn't get it to work and honestly have no idea
what the problem is here.
So, as I'm working on a relatively small project database-wise, my solution
was to switch to MS SQL.

Best regards,
Johannes Mols

On Thu, Apr 9, 2020 at 3:40 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Johannes Mols <johannes(dot)mols(at)gmail(dot)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
>