GnuCash PostgreSQL support

[Klaus Dahlke]

On Fri, 27 Jan 2012 08:49:14 -0800
John Ralls <jralls at ceridwen.us> wrote:

> 
> On Jan 27, 2012, at 7:48 AM, Tarlika Elisabeth Schmitz wrote:
> 
> > On Fri, 27 Jan 2012 09:57:56 -0500
> > Derek Atkins <warlord at MIT.EDU> wrote:
> > 
> >> There are a LOT of data loss issues in
> >> the SQL backend that have been fixed in later releases.  You do NOT
> >> want to use 2.4.4 with SQL.  You want at least 2.4.9 if not the (just
> >> released) 2.4.10.  And even there you might still find some data loss
> >> issues with SQL.
> > 
> > Thanks for the warning, Derek.
> > 
> > In other words, GnuCash with SQL is not stable for real-world use at
> > the moment.
> > 
> > I had so much been looking forward to moving to SQL for three reasons:
> > 
> > 1) speed (not too important) 
> > 
> > 2) being able to extract my own reports via SQL
> > 
> > 3) I want to bulk modify 1000s of transactions (I know it's not
> > recommended to tamper with the data directly and I'd do a backup
> > beforehand). For instance, I want to transpose Description and Notes of
> > certain transactions (so they show up in the reconcile window). I have
> > 8 years worth of data, 55MB xml and I'd like to restructure the way I
> > record some expenses based on my notes.
> > 
> > I'm sure you're going to shoot me down in flames but this would either
> > have to be automated or it will never be done.
> 
> The *known* data loss issues have been resolved in 2.4.9, but we don't yet have sufficient test coverage to be sure that everything is really covered. There's no speed difference, because with all backends the entire database is loaded into memory when the file or database is opened. 
> 
> Extracting reports via SQL is more difficult than it would seem at first glance because none of the "business logic" is encoded in the database (not even foreign keys). Some of the useful data are kept in nested key-value pairs which require recursive queries to extract, which isn't easy to do in pure SQL.
> 
> Your notes/description swap is certainly possible and should be safe enough -- but you could accomplish it as easily with XSLT on the XML file as with SQL. I'm not sure what you mean by "restructuring your expenses", but if it means moving splits from one account to another, that's definitely *not* safe to do outside of the Gnucash API.
> 
> Regards,
> John Ralls
> 

Hi,
I'd like to share my experience. I use the postgresql-backend since it came available with 2.4.x and never experienced a data loss. Okay, I don't use the business features, just ordinary double book keeping, fetching transactions from my bank and getting online quotes.

I als modified some transactions by e.g. moving them from one cost account to another one. I agree that has to be done careful, but it worked out. Having teh xml-file as backup is a good idea.

My main intention to go the the postgresql backend is that I get rid of the xml file and could write my own reports. For the later I wrote a small application in Ruby on Rails. Ruby on Rails also allows to define foreign keys at the application level. Doing so I can extrate the transactions and splits nicely and put them together in a format I like.

In short: I have good experience tracking my private finance with gnucash on the postgresql backend.

Cheers,
Klaus