Re: PG in cash till machines

Having some experience in the point of sale area, you are right to be
concerned. Before I go to inline points, I just want to say that my
experience is that PostgreSQL does an admirable job reliability-wise in
such environments, but it isn't perfect (no software solution can be) and
the problems that remain, quite frankly, are not ones you can completely
solve so a part of the strategy needs to be containment of damage.

On Fri, May 10, 2013 at 6:43 AM, Carlos Henrique Reimer <
carlos(dot)reimer(at)opendb(dot)com(dot)br> wrote:

> Hi,
>
> We are developing a solution which will run in thousands of small cash
> till machines running Linux and we would like to use PostgreSQL but there
> is a insecurity feeling regarding the solution basically because these
> boxes would be exposed to an insecure environment and insecure procedures
> like: non controlled power outages, untrusted hardware, no appropriate
> environment for hardware (dusty, hot) etc...
>

PostgreSQL isn't perfect. Of course no software will be perfect with
possibly bad electrical inputs and overheating CPU's....

Additionally my experience working with point of sale systems is that
customers *will* abuse the systems. I had one customer call me about
database corruption (turned out to only be index corruption thankfully) and
I showed up to find they had put the database server up in an unventillated
closet above door level, so it was sitting in a cloud of its own hot
air.... Especially when customers don't understand computer they will do
things that will cause problems and you can plan on it.

However, my experience has been that even on desktop-level hardware
PostgreSQL has been more reliable in such environments than any other
database I have seen used in small business point of sale systems. Despite
horrid abuse of hardware the worst I ever saw was index corruption. Some
other systems caused problems that required periodic restoration from
backup and loss of at least a day's data.

However you really want to plan for the possibility of losing a day's data.
Fortunately the customers who most abuse their hardware tend to be the
most tolerant to such data loss.

> Our major concern is related to the write-back issues we can face in this
> environment. Is there a way to prevent it or a way PG can be configured to
> detect write-back configurations? Don't think so, but...
>

With a little development effort (C-language UDF's) you can make PostgreSQL
detect anything you can detect through programming applications in the
system. You can then hook such detection functions into your app.
However, if the app is running on the same system, it may be easier to
just do the detection in the application itself.

>
> Basically, the question is: is there a way to guaranty a reliable PG
> database in an untrusted and insecure environment? Maybe some kind of file
> system could give this guaranty, not sure..
>
>
To some extent, yes. However the filesystem can only do so much if, for
example, hard drives lie to the filesystem.....

Anyway, here is my recommendation:

1. Plan on having anyone running multiple tills to hit a centralized
server. That makes it much easier to centralize these guarantees.

2. Document possible issues for those implementing the system. Discuss
them on both a hardware and software level.

3. Rely on implementors to do primary database support and implementation.
This hopefully allows some reasonable feedback and local knowledge even if
the end user may not know how to keep things running effectively.

4. I would suggest building a pg_base_backup run into the daily closing so
that if you need to restore from a backup this can be done quickly and
easily. This would be in addition to off-site backups (or even off-machine
backups).

Basically rather than worrying about the guarantees from a technical level,
I would be looking at it from a human angle first, and then technical
guarantees for containment if something goes horribly wrong. Your best
technical measures may depend on things you can't depend on. For example
if the CPU overheats maybe send a message to someone urging technical
support.... but what if the internet connection is down?

It wouldn't be a bad idea to have a diagnostic tool which could detect
possible issues and which could be run demand (perhaps over the phone) but
don't underestimate the importance of human contact, particularly on-site,
in these sorts of environments. A person visiting the site will spot
issues that your software hasn't thought of and so it's a good idea to be
thinking in terms of supplementing that.

Best Wishes,
Chris Travers

--
> Reimer
> <carlos(dot)reimer(at)opendb(dot)com(dot)br>
>