Re: Suspending postgresql during heavy load

On 7 October 2014 23:31, The Doctor <doctor(at)doctor(dot)nl2k(dot)ab(dot)ca> wrote:
> On Tue, Oct 07, 2014 at 09:17:47AM -0600, Scott Marlowe wrote:
>> On Tue, Oct 7, 2014 at 9:07 AM, The Doctor <doctor(at)doctor(dot)nl2k(dot)ab(dot)ca> wrote:
>> > Question is their a way to suspend PostGres during a heavy average load
>> > session?
>> >
>> > I would like to suspend Postgresql transaction on a load average higher
>> > then a load average of 5.

If you do want to stop your system being overwhelmed, I'd suggest
putting pgbouncer between your web application and PostgreSQL. You can
use this to limit the number of concurrent connections being
processed, limiting your load and improving overall throughput. If you
are seeing this sort of problem at all, you will likely find you have
hundreds or thousands of open database connections when your hardware
is incapable of processing more than 30 or so connections
simultaneously in an efficient manner.

> In this case PostGresQL 9.3.5 taking on brute force attacks from
> Blog Spammers using Serendity .
>
> I have yused mod_limits to limit the damage from the brute force
> wep spam attack, but this is turing the web server off for about 120 s and I would like
> to turn away these pests forver.

You will still need something on the web end to actually stop the
attack. Nothing you do at the PostgreSQL end can differentiate
attacker requests from genuine user requests. The bots will beat users
any day as bots just keep reloading for ever, while users give up and
go away after too many error pages in a row.

--
Stuart Bishop <stuart(at)stuartbishop(dot)net>
http://www.stuartbishop.net/