Just answer the F*#king question

SELECT *, (pg_relation_size(indexrelname))
FROM pg_stat_all_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelname) DESC, idx_scan ASC

This assumes that id is your unique SERIAL field, and that {table}_id_seq is the field for the next value.

select * from {table}
where id >= (
select floor(random() * (
select last_value
from {table}_id_seq )) ) order by id asc limit 1

Make sure mod_status is enabled in your httpd / apache config, and add the following lines


ExtendedStatus On
<Location /server-status>
SetHandler server-status
Order deny,allow
Deny from all
Allow from localhost
</Location>

If you are getting permission errors, try setting

UseCanonicalName Off

Had some issues with bots hammering one of my servers. For a while I was blocking by hand and returning 404 errors, with a simple 404 redirect, however, this will still used up resources and http / apache threads.

This is a very basic method that was implemented to stop spammers from your site. You could modify this to be a bit more intelligent, in this case I have a “honeypot” page that only the spammers and bad bots seem to access plus a few sanity checks just incase google visits by accident, however, you could put a counter in to a DB or do some other simple checking before triggering the firewall.

First step, you need to use visudo to allow apache ( or www-data ) access to iptables.

visudo

and then add the following line

apache ALL = NOPASSWD: /sbin/iptables -I INPUT -m iprange --src-range * -j DROP

Replace “apache” with www-data if needed ( if you are unsure, do a ps -al | grep apache or ps -al | grep httpd ) and get the user … we hope apache is not running as root.

This will allow the apache user access to the specific command above. You could probably lock this down more if needed.

Exit out, and then add the following to your “honeypot” page

exec("sudo /sbin/iptables -I INPUT -m iprange --src-range ".$REMOTE_ADDR."-".$REMOTE_ADDR." -j DROP");

Now, obviously DON’T TEST THIS FROM YOUR OWN IP. Otherwise your going to block yourself from your server. To test, hardcode a different IP in there or do it from another public IP.

So, hopefully, the next time a spammy IP comes and touches your IP address, they will be locked right out. If someone gets blocked by accident, do a sh /etc/init.d/iptables save then edit the saved files ( normally /etc/sysconfig/iptables ) to remove the DROP command for that IP and then sh /etc/init.d/iptables restart

You could also modify the code to block a bigger range of IP’s by padding the range to the class C address or more depending on what level of activity you are getting.

netstat -ntu | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n