1

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 SQLAlchemy 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'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.

6
  • Is this a server in your local network or some hosted by a cloud provider?
    – piro
    Apr 8 '20 at 13:49
  • @piro It is a remote machine. Apr 8 '20 at 13:51
  • What does it mean remote? Is it Amazon, Azure, etc. or it's just your network?
    – piro
    Apr 8 '20 at 13:56
  • @piro It's actually a university network where I have a virtual machine. It's completely open to the Internet and I just opened the ports that I needed. No third party providers. The Python script is running on localhost though. For pgAdmin, I'm connecting from my own machine over the Internet directly to the IP of the VM. Apr 8 '20 at 13:58
  • Ok, thank you for your info. I'm asking because I've seen weird things happening on Azure. Your server crash is probably relevant to the PostgreSQL guys, but they'll sure need more help to investigate into that. Cheers!
    – piro
    Apr 8 '20 at 14:02
1

This kind of error looks like a bug in PostgreSQL:

server process (PID 20569) was terminated by signal 11: Segmentation fault

Try to post to https://www.postgresql.org/list/pgsql-bugs/

1
  • I will try that, thank you. Apr 8 '20 at 8:43
1
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"

You should add %h to your log_line_prefix setting, so you can see where these are coming from. Most likely some bad guy on the internet is trying to guess your password. Based on this error, he failed, at least this time.

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 <> ''

Is this the type of query you would intentionally be running? If not, then it seems like the bad guy succeeded at least once.

2
  • That could be the answer why I see this so much in my logs. Thank you for pointing it out. And yes, I ran this query myself, and it causes the connection to disconnect. Apr 8 '20 at 15:19
  • In that case, it seems like two separate issues, an attacker (which you an ignore if you have a really good password) and a bug. Try to get a core dump (wiki.postgresql.org/wiki/Developer_FAQ#core_dumps) if you don't already have one, and report the backtrace. Also, can you give the output of psql's \d transactionoutputs
    – jjanes
    Apr 8 '20 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.