Re: Database consistency after a power shortage

Samuel Gendler wrote:
>
>
> On Wed, Dec 15, 2010 at 11:38 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com
> <mailto:scott(dot)marlowe(at)gmail(dot)com>> wrote:
>
> On Wed, Dec 15, 2010 at 8:12 AM, Alberto <blob2020(at)gmail(dot)com
> <mailto:blob2020(at)gmail(dot)com>> wrote:
>
> >
> > Is there any way to make the 3 operations be one transaction for the
> > database, so that it keeps them all consistent in case a power
> shortage
> > occurs in the middle?
>
> Yes, put them in a transaction.
>
> begin;
> insert into head_invoice ...
> insert into detail_invocie ...
> insert into payments_x_header_invoice ...
> commit;
>
> Then they either all go or none go.
>
>
> But if the database transaction concept is new to you, I highly
> recommend you do a little reading about database transactions in general
> and postgres' implementation specifics as well. It can be very easy for
> you to make mistakes that can cause the database to get slow or use up a
> lot of disk if you use transactions without understanding at least a
> little of what is happening in the database while the transaction is
> open but uncommitted.
>
> Incidentally, any error on a query within the transaction will cause the
> transaction to automatically 'rollback' when the transaction completes,
> undoing all of the changes, or you can manually cancel a transaction by
> issuing a 'rollback;' statement instead of 'commit;' at the end.
>
>
You can also (or more appropriately, in addition) equip your system with
an uninterruptable power supply with enough capacity to coast over the
power shortage interval, or to perform a controlled shutdown. I do not
know how long it takes to do such a shutdown with postgreSQL, but it
could involve stopping all new transactions from entering the system,
and allowing those in process to complete. A UPS to allow 10 minutes of
run-time is not normally considered too expensive. Mine will run for
about an hour with new batteries, but after a few years it dwindles to
about 1/2 hour. Then I get new ones.

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 09:30:01 up 14 days, 23:16, 4 users, load average: 5.61, 4.98, 4.89