Using an externally hosted postGreSQL as a gnu cash backend?

[Russell Mercer]

Mike,

I think there are a couple of things at play here.

1.  Using a database backend as opposed to the .xml file:  There are noted
issues with data consistency when using any backend storage aside from the
.xml.  The issues may not be major, and may only apply in certain areas
like business functions, but that has not been quantified.  In short, there
are known data integrity issues, but their scope and severity are not known
as they pertain to any or all database backends.  That is one primary
reason other people on the list have recommended against using it.

2.  Postgres data integrity, ACID support, etc.  I use Postgres on a daily
basis for my work, so I completely understand and agree that it is a
stellar RDBMS.  I do not, however, use it for Gnucash.  The primary reason,
is as mentioned in item 1.  There are unresolved data integrity issues.
The secondary reason is that at this time, Gnucash has not been modified to
take advantage of any database functionality.  To your example, a partial
transaction could not happen in Gnucash, but for a different reason..  A
transaction between accounts in Gnucash does not involve a transaction
between 2 tables in Postgres.  This is because the data is essentially
stored in a flat file in Postgres.  It is loaded completely into memory and
all operations are done in memory with the results written back to the
database.
So, yes, while in theory, the RDBMS functionality of Postgres should
provide far superior data protection than the .xml file, in practice, it is
no better.

Hope this helps,
Russell

On Mon, Apr 11, 2016 at 2:43 PM, Michael Wagner <mikepwagner at mikepwagner.net
> wrote:

> 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
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>