Safe way to perform DB script If I am reading the documentation correctly, issuing this command

psql -v ON_ERROR_STOP=1 database &lt; script.sql


where script.sql starts with BEGIN

BEGIN;
ALTER TABLE...
some other DDL...

COMMIT;


should mean that if one of the commands in script.sql fails, the whole script is rolled back?

(I am looking for a way to issue a DB upgrade that atomically succeeds or fails.)
Yep, that's basically right. FYI, the ON_ERROR_STOP business is just a hint to psql that you want it to stop (and, in your case, effectively rollback by closing the connection) immediately upon an error, instead of flooding you with a bunch of messages like "ERROR:  current transaction is aborted" for each subsequent command in the transaction.

There are a few exceptions, see some hints in this answer. But if you try to stick a command in your script.sql which is not allowed to run inside a transaction block, PostgreSQL should give you an error like "... cannot run inside a transaction block" and your transaction should get rolled back.

Of course, you'd be well advised to try your migration script on a test/backup instance (you do test your backups somewhere, right?) first.