HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

PostgreSQL lost connection during massive index operation? What exactly happened?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqllostmassivewhatduringoperationindexhappenedexactlyconnection

Problem

Using PostgresQL 9.4, Ubuntu 14.04, and 32 GB RAM (with modified settings in postgresql.conf file; I think the highest allocation is 16GB).

I ran a ts_vector creation on a text column in a table containing a little more than 1.8 billion rows. It was successful.

However, running an index on the new vector column failed in a strange way.

database_name=# create index tsv_column_idx on table_name using gin(tsv_column);


The query ran for about 56 hours in a screen session, and then I got this message:

FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.


Trying another query resulted in:

!> ;
You are currently not connected to a database.


Why would the connection have been lost? Nobody else connected to the database or made updates to the server. Is this an issue with memory allocation?

I ran dmesg | grep -iC 3 "kill" and nothing came up.

It seems like there was some kind of shutdown error... no idea how that occurred though.

2015-10-09 14:55:32 PDT [1506-2] LOG: received fast shutdown request
2015-10-09 14:55:32 PDT [1506-3] LOG: aborting any active transactions
...
2015-10-09 14:55:32 PDT [3803-4] postgres@database FATAL: terminating connection
due to administrator command
2015-10-09 14:55:32 PDT [3803-5] postgres@database STATEMENT: create index ...;

Solution

Checking sudo vim /var/log/apt/term.log, I confirmed that this was caused by a PostgreSQL update hidden in the apt-get upgrade that another user had executed on the system.

Context

StackExchange Database Administrators Q#117651, answer score: 2

Revisions (0)

No revisions yet.