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.reimer@xxxxxxxxxxxxx> 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
