I had some problems last month. I had a bad query in Postgres (PostGIS) of a user launched during the night. This query took all the memory and I had 0% of free disk.
Fortunately, I restored the database with my dumps, it's ok, but I'm afraid for the next time.
Can I predict a bad query in Postgres and stop it immediately?
Which tools? Which scripts? Do you recommend any?
Limiting is hard, but you can set a timeout on query for a user :
ALTER ROLE <user> SET statement_timeout TO '60s';
As of the disk I'm not aware of any technique to limit disk usage of a query in postgresql.
You can limit the amount of scratch space using temp_file_limit. This is a per-process limit. You can also create a tablespace, on a separate partition, and use temp_tablespaces to indicate temporary objects and temporary files should be put there.
Running out of disk space should not cause your server to crash so hard so as to require it to be restored from a backup. The usual case is that the query causing the problem is delivered an ERROR and then frees up the temporary space, and everything else continues on. The worst case is when something needs to be written to pg_xlog or pg_wal and there is no space for it (But even then, you should be able to restart the server, possibly after some manual clean up; you shouldn't need to restore from a backup). One possible way to make this worst case less likely is by putting pg_xlog on its own partition so that other things can't fill up its space unexpectedly.
