Using an externally hosted postGreSQL as a gnu cash backend?

[Michael Wagner]

On Mon, Apr 11, 2016 at 3:51 PM, Colin Law <clanlaw at gmail.com> wrote:

> On 11 April 2016 at 19:20, Michael Wagner <mikepwagner at mikepwagner.net>
> wrote:
> > ...
> >  It probably took a couple of hours of reading
> > for me figure out how to install and set up PostGres on mu Ubuntu box,
> and
> > the vanilla PostGres settings all worked like a charm.
>
> If you are hosting PostGres on a machine connected to the internet
> don't forget to consider all the security implications.  You don't
> want anyone getting into your machine via the open ports, or of
> accessing your data if you have not got it right.  For example, if you
> connect to a pg server are the user and pwd encrypted or are they open
> for anyone to snoop between you and the server?  I don't know the
> answer to that.
>

I am looking into cloud security. I work in computer storage for a living,
and I have talked my manager into letting me take a cloud infrastructure
class so I understand more about the problem domain.

My guess is that the various cloud providers have solved this problem.


>
> > ...
> > From my (somewhat nerdy) perspective. from what I know of DB guarantees
> and
> > the ease of setting up PostGres, I am surprised everyone hasn't switched
> to
> > using the PostGres DB - a huge number if data reliability problem are
> > solved.
>
> What do you see as the advantages of PG over just storing the xml file
> on a basic file server?
>

The most important thing I am looking for is atomicity (the A in ACID).

The classic example is transferring money form one account to another. To
do the transfer from Mike to Kim, two things need to be recorded to disk:


   1. Subtract $20 from Mike's account.
   2. Add $20 to Kim's account.

If a power glitch/system crash happens after the money has been  removed
from Mike's account, but before it gets added to Kim's account. then Mike
has lost $20. If the money gets added to Kim's account before it gets
subtracted from Mike's account, then the bank has lost $20.

A database wraps both of these actions into a single "atomic transaction" -
and either both actions occur, or neither action occurs - no matter when
the system crashes.

It's not implemented this way, but if the system crashed after the
subtraction from Mike's account was written to disk, but before the
addition to Kim's account, then the subtraction "disappears" from Mike'c
account.

If the system crashes after addition to Kim's account make it to disk, but
before the subtraction from Mike's account makes it to disk, then the
addition disappears.

That "make partial transactions disappear" magic is the heart of what DB is
about - that's what really distinguished a DM from a file in a file system.

My understanding is that when I enter a transaction into gnucash, it's
mapped to a PostGres "atomic transaction".That  means that even if my
computer cashes at any point, either the debit and the credit make it to
disk, or neither the debit or the credit make it to disk.

There are three other properties, consistency, independence, and durability.

Here's a description of ACID properties offered by a DB like Postgres.

https://en.wikipedia.org/wiki/ACID

A DB will be immune to call of that. Unless there are bug in the DB, only a
catastrophic disk failure should destroy the data.

There are a lot of PhD hours in PostGres, my guess is that they mostly have
it  correct.

You can in fact simulate a lot of this with flat file if you are very
careful - but I think that it makes more sense to let gnu cash developers
focus on the accounting and let PosGres focus on things like atomicity and
durability.

The development of these fundamental DB algorithm is important enough that
it made in into "Nine Algorithms that Changes the Future":

http://www.amazon.com/Nine-Algorithms-That-Changed-Future/dp/0691158193/ref=sr_1_1?ie=UTF8&qid=1460410206&sr=8-1&keywords=Nine+Algorithms+That+Changed+the+Future


>
> Colin
>



-- 
“The bassoon is one of my favorite instruments. It has a medieval aroma,
like the days when everything used to sound like that. Some people crave
baseball...I find this unfathomable, but I can easily understand why a
person could get excited about playing the bassoon.” - Frank Zappa