Postgresql database randomly crashes

Question

I really have no idea what the problem is.

The logs read

FATAL: sorry, too many clients already

Over and over again. At first I thought that sometimes the connections lingered or were not closed properly so I tested that by connecting to the database and checking how many opened connections there were at any given time, and the answer has always been 1.

I tried to connect to the website that's using the DB and I managed to glimpse at 2 or 3 opened connections that were promptly closed when the page was done loading.

My remaining guess is that sometimes there are spikes in concurrent connections to the website and that causes the database to stop accepting new connections and somehow doesn't allow the current connections to be dropped.

I DID NOT WRITE ANY CODE THAT CONNECTS TO THE DATABASE, I'm using a pretty vanilla Django (1.7) backend that handles all of the connections.

I couldn't find anything while searching google, has anyone experienced any problems?

EDIT:

Database configuration is here(PasteBin)

Essential part:

port = 26445                # (change requires restart)
max_connections = 500           # (change requires restart)
unix_socket_directory = '/home/clearintent/webapps/norr2_db/run'        # (change requires restart)

shared_buffers = 32MB           # min 128kB
                    # (change requires restart)
log_destination = 'stderr'      # Valid values are combinations of
logging_collector = on          # Enable capturing of stderr and csvlog
log_directory = 'pg_log'        # directory where log files are written,
log_filename = 'postgresql-%a.log'  # log file name pattern,
log_truncate_on_rotation = on       # If on, an existing log file with the
log_rotation_age = 1d           # Automatic rotation of logfiles will
log_rotation_size = 0           # Automatic rotation of logfiles will
datestyle = 'iso, mdy'
lc_messages = 'C'           # locale for system error message
lc_monetary = 'C'           # locale for monetary formatting
lc_numeric = 'C'            # locale for number formatting
lc_time = 'C'               # locale for time formatting
default_text_search_config = 'pg_catalog.english'

Please include database configuration, maybe the problem is there. — noamk
– noamk, Commented Nov 26, 2015 at 15:04
@CraigRinger I think it is, I cannot even kill the process, I need to "kill -9" it — XelharK
– XelharK, Commented Nov 26, 2015 at 15:42
It only shows my current connection, or the website 2 or 3 connections if it's currently visited. I should probably set up a cron job to output that table into a file every minute or so so I know what's going on before the crash but I'm not sure how to do it — XelharK
– XelharK, Commented Dec 1, 2015 at 9:33

edd · Accepted Answer · 2015-12-01 16:15:50Z

3

+25

Sounds like something is bashing your PostgreSQL but that error alone does not crash the DB.
It simply means the latest connection attempt exceeded the amount of allowed parallel connections to the DB and it was rejected.

But, if you want to dump the amount of connections every minute, you can use this script

#!/bin/bash

function spew_connections() {
# Run psql on local if trust or auth is set.
# Change dbadmin and dbname accordingly.
/usr/bin/psql -d dbname -U dbadmin -w -t -c "SELECT localtimestamp(2), count(*) FROM pg_stat_activity;"
}

echo -n `spew_connections` >> /tmp/connections
echo >> /tmp/connections

And then, to execute it using crontab every minute

crontab -e
*/1 * * * * /path/to/executable/script

answered Dec 1, 2015 at 16:15

edd

1,43712 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

XelharK Over a year ago

Thanks. I'll try and come back with results after another crash.

Tomasz Jakub Rup · Accepted Answer · 2015-12-08 19:11:42Z

1

Try to install tools like pgBouncer between Your app and database.

PgBouncer is a lightweight connection pooler for PostgreSQL.

Most important points:

[databases]
pgbase = host=localhost dbname=bazdb

[pgbouncer]
listen_addr = *
listen_port = 6432

pool_mode = transaction

And in client connect to pgbase on localhost:6432

edited Dec 8, 2015 at 19:11

answered Dec 8, 2015 at 9:06

Tomasz Jakub Rup

10.7k7 gold badges52 silver badges49 bronze badges

2 Comments

XelharK Over a year ago

Wouldn't this increase the number of connections I need?

Tomasz Jakub Rup Over a year ago

No. pgBoucer manage connections to DB. I had the same problem on my system. pgBoucer resolved this problem definitly. When I catch my admin, then I paste this pgBoucer config.

Tomasz Jakub Rup · Accepted Answer · 2015-12-05 19:24:31Z

0

Django doesn't close connections automatically.

Add

'OPTIONS': {
    'autocommit': True,
}

to Your database configuration.

answered Dec 5, 2015 at 19:24

Tomasz Jakub Rup

10.7k7 gold badges52 silver badges49 bronze badges

3 Comments

Steven Over a year ago

Django does close connections automatically. Auto-commit is turned on by default since Django 1.6

XelharK Over a year ago

Yeah I would have been pretty shocked. I'm using Django 1.7. And also I would have seen the opened lingering connections after my tests.

Tomasz Jakub Rup Over a year ago

@Steven Yes, You have right. I did not notice information about Django version.

Collectives™ on Stack Overflow

Postgresql database randomly crashes

3 Answers 3

1 Comment

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related