0

If I am reading the documentation correctly, issuing this command

psql -v ON_ERROR_STOP=1 database < 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.)

2

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.

  • Thanks a lot! Seems the last sentence has been cut short, could you please finish it? – vektor Jan 22 '15 at 5:30
  • Fixed last sentence, sorry about that. – Josh Kupershmidt Jan 22 '15 at 13:45
  • Thanks! Yes, all the scripts are first run on the test instance - my question goes in the direction of a tool that would first try the script and then actually run it, think $ upgrade.sh script.sql – vektor Jan 22 '15 at 16:57
  • Sure, you should be able to use psql as in your example to actually load in script.sql from inside your upgrade.sh script -- the exit code of psql should tell you whether the migration finished successfully or not. Oh, and one more tip -- it's a bit nicer to feed in your script.sql via the --file option to psql (easier for psql to pinpoint the line in the file which is bad in case of problems). – Josh Kupershmidt Jan 23 '15 at 17:01
  • I am using the < so that I don't need to upload the script on the remote server beforehand... – vektor Jan 24 '15 at 7:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.