Transactions are only useful when you actually use them, otherwise it's too late when you realize you've forgotten the WHERE clause and would really, really like to be able to roll back the changes - as Robert Treat found out recently.

If you're lucky conventional backups or PITR will help you avoid total disaster, but apart from the delay in extracting the necessary data from the backups and reading it in, Murphy's Law dicates that exactly the data you need won't be in the backup. Far better is to remember to start a transaction in the first place, putting recovery a blood-pressure friendly ROLLBACK away. psql has two surprisingly little-used functions which help ensure you're inside a transaction when you need to be.

I've written about this before: psql has a prompt formatting option %x which displays an asterisk * whenever a transaction is in process. Set it with e.g. \set PROMPT1 '%/%R%x%# ' and train yourself to be very nervous about carrying out anything other than simple SELECTs when that little asterisk is not visible:

test=> \set PROMPT1 '%/%R%x%# '
test=> BEGIN;
BEGIN
test=*> DELETE FROM important_table;
DELETE 24566321
test=*> ROLLBACK;
ROLLBACK
test=> \echo Phew, that was close!
Phew, that was close!

(See the psql manual for further details)

Of course this still requires you to remember to issue the BEGIN instruction, because PostgreSQL's default setting is for AUTOCOMMIT to be on. Issuing \set AUTOCOMMIT off, especially in combination with the prompt setting above, provides a useful safety net:

test=> \set AUTOCOMMIT off
test=> DELETE FROM important_table;
DELETE 24566321
test=*> \echo Ooops!
Ooops!
test=*> ROLLBACK;
ROLLBACK

The risk here of course is closing psql before committing the transaction, implicitly rolling back any changes you wanted to make, but the asterisk in the prompt serves as a useful visual reminder.

Put \set PROMPT1 '%/%R%x%# ' and \set AUTOCOMMIT off in your .psqlrc file to make them permanent settings.