Re: Embedding postgresql in an application

On Apr 4, 2013, at 4:14 PM, Sam Talebbeik <sam777t(at)yahoo(dot)com> wrote:

> Josh, I am trying to figure out all the reasons and navigate through this myself. I can give two reasons. It may or may not be strong reasons from your point of view.
>
> 1) customer expectations - they expect a turnkey solution tested against a specific version of the database. We control everything including access to the database and its upgrade. We do not want any admin to use apt-get and upgrade the database while the application is running.

"Turnkey" makes me think you're selling your app into businesses (and for a Linux app that means servers rather than desktops).

I've been deploying a postgresql backed enterprise app into very large companies for the better part of a decade - originally with a bundled copy of postgresql when packaging was not as robust as it is now, but now relying on OS-packaged versions (on multiple versions of Ubuntu, Debian and RHEL as well as Solaris and occasionally OS X and FreeBSD) - so I have some opinions.

If there is an admin with root access to the machine then you do not control everything.

They can backup the database however they choose (and will, regardless of what you advise) - I document pg_dump based backups, but I have customers who are using replication and other forms of HA. And that's great, they can do that, without me needing to explicitly support it (other than the occasional request from them to modify app behaviour to make replication simpler).

They can report on their data using any tool they like - I hate Crystal Reports, and have no intention of supporting it, but if a customer wants to use it, there's no problem. It's just a generic database. I bundle some brief docs with my app to give them a roadmap through the schema. This adds huge amounts of value, and requires hardly any work from me.

They will add bags on the side that do other things with the data. Summarize it to a web portal. Monitor data values once an hour, and send out a page if something is out of range. This is both a big value add, and something you can't stop your customers doing even if they want to. I occasionally get database dumps from customers when I'm diagnosing issues and I'll often find other tables have been added to the schema, to support related things the customer is doing.

As for admins upgrading the database when your app doesn't expect it? If your app can't cope with that, and reconnect gracefully, you've got bigger problems. If you're concerned about reliability when you've not QAed on different database versions then you can check the database version at startup - bail if it's too old, show a warning (or bail) if it's too new. (Though automated QA is a very good investment of time, and it makes cross-version QA very cheap to do).

> 2) A handful of cases where the database was installed on a machine behind a firewall which could not get outside and perform apt-get.

You don't need apt-get to install from .deb packages. It's *better*, because it handles security and bug fix updates automatically, but it's perfectly possible to install them without network access - either from the original OS installation disks, or by downloading the needed .debs from a network connected machine and sneakerneting them. (Though I'd be surprised to find a machine that couldn't apt-get - it's not something that a typical firewall would affect at all).

> I have been able to manually install and bring up postgresql on Ubuntu 12.04 desktop version using the binary tar file from EnterpriseDB web site. It was very straightforward. I am not arguing that this is the most optimum way, but I still believe that the good old option of expanding a binary tar file on Unix should work and it may come handy in certain situations. Even Firefox folks have not abandoned this approach and you can still get the binary tar files from their ftp web site.

That's not something you can reliably do with dynamically linked applications, certainly not on different linux distributions.

More importantly, in business terms, no competent corporate sysadmin is going to want to touch non-packaged apps if they can possibly avoid it. Maintenance and dependency management (and keeping up with security patches) are just too painful. Most of the better folks I've worked with would, if presented with a tarball of binaries would turn their nose up, rip it apart, package it properly as a native type (e.g. .deb or .rpm or…) and then deploy it that way. You'll certainly find customers for whom that isn't true, but the more valuable the customer the better odds that their sysadmins are competent at large scale software management.

Cheers,
Steve