Re: [GENERAL] 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.rei...@opendb.com.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 > >
Re: [GENERAL] PG in cash till machines
Hi John, On 5/10/2013 2:55 PM, John R Pierce wrote: On 5/10/2013 2:11 PM, Bexley Hall wrote: Having designed (regulated) gaming and "grey area" devices (each handling hard currency), I can tell you that you have to have already performed a pretty exhaustive threat analysis (e.g., red team, blue\ team) *before* you start the product's design. If you can't imagine *all* of the ways you can be targeted, then you can't determine how/if you will be "secure" in each of those scenarios (e.g., I've incorporated features into the hardware designs to counter certain types of physical attacks). indeed, and there's always threat models that no one could foresee, witness the recent story of coordinated ATM withdrawals of $45,000,000 enabled by some back door hacking of the bank databases. All (?) software and security flaws can be traced to "improper assumptions". Someone, somewhere, involved in the design of the "system" (which includes more than just hardware and software) made some BASIC assumption that was flawed. I.e., assumed "/* CAN'T HAPPEN */" actually *couldn't* happen! The trick to designing robust software (and robust systems) is to identify those assumptions and then seriously question whether they *really* are valid -- or, just valid in your particular outlook on Life, The Universe and Everything. - This instruction got executed so the next one will, as well. - The only way for this instruction to be executed is if the preceeding one is, also. - There can't be two credit cards with the same account number. - People can't be in geographically different locations at the same time (so they can't possibly make withdrawals on the same account from those different locations, concurrently) - Social security numbers can't begin with '0'. - System power won't fail (or be intentionally interrupted) before I get a chance to do X... (e.g., flush buffers to permanent storage) - All first names are less than BUFLEN characters. - When turning *right*, I can't hit anything on the *left*. - No one will unplug (or cut!) this cable. - Users will choose "good" passwords. - malloc() will never FAIL. - This contact will close each time a coin is dispensed. - Coins can't pass this sensor faster than once every N seconds. etc. Conversely, the way to find faults in those systems/software is to "do the unexpected". Developers tend to be *so* focused on "just trying to get it to work" that they often don't attend to "getting it to work *well*". Carlos has to figure out which of these assumptions he's made that aren't *guaranteed* (by some agency/mechanism) to be true and figure out how to *make* them true (or, detect when they are not). --don -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to clone a running master cluster?
On Sat, May 11, 2013 at 7:56 AM, Moshe Jacobson wrote: > I have a master database cluster on one server, and it is configured to > ship logs via scp to an archive directory on my slave server. The slave > server is configured for streaming replication, and also is configured to > delete the archived xlogs when they are no longer needed (using > pg_archivecleanup). > > I have a third machine on which I'd like to get another master cluster > running, and I'd like it to start with a copy of my current master. I'd > cannot restart my master, and would prefer not to restart my slave either. > > Given my xlog archive configuration, Is there a way to clone my master > cluster to another machine, including all of the necessary xlogs, without > bringing down the original master or slave? > I'd probably use "pg_basebackup --xlog-method=stream ..." Or temporarily disable pg_archivecleanup. Cheers, Jeff
Re: [GENERAL] "Unlogged indexes"
On Mon, May 6, 2013 at 4:43 PM, Michael Paquier wrote: > On Sat, May 4, 2013 at 5:53 AM, Yang Zhang wrote: > >> Yeah, I know that indexes for unlogged tables are unlogged. I was >> just wondering if you could do this for logged tables. (Safely, such >> that on crash recovery WAL replay won't throw up, these can be omitted >> from base backups, etc.) >> > No, you cannot create unlogged indexes on logged tables. An unlogged > tables is > truncated when a server starts after a crash, and so are its indexes that > become > empty by default. But having an unlogged index on a logged table would > mean that > you would need to truncate and regenerate the index after a crash as the > data of > the normal table is still here, > The other option would be to mark the index as invalid, rather than rebuilding it. But both of those options are hard to implement, as recovery cannot change the system catalogs, which I think would be needed to implement either one. > what would impact the performance boot of the server. > Do you have a particular use-case in mind? I cannot see advantages directly > advantages in having an unlogged index on a logged table... > If your index is small but intensely updated, then not WAL during normal use could save a lot of time; while rebuilding after an instance crash could take negligible time. But from some of Yang's other recent email, I think he is more interested in not backing up his very large indexes, and rebuilding them if media recovery is needed. That is obviously more of a trade off, but it seems like a choice people should be able to make, if it were easy to implement. Cheers, Jeff
Re: [GENERAL] How to clone a running master cluster?
On 5/11/13, Moshe Jacobson wrote: > I have a master database cluster on one server, and it is configured to > ship logs via scp to an archive directory on my slave server. The slave > server is configured for streaming replication, and also is configured to > delete the archived xlogs when they are no longer needed (using > pg_archivecleanup). > > I have a third machine on which I'd like to get another master cluster > running, and I'd like it to start with a copy of my current master. I'd > cannot restart my master, and would prefer not to restart my slave either. > > Given my xlog archive configuration, Is there a way to clone my master > cluster to another machine, including all of the necessary xlogs, without > bringing down the original master or slave? Step-by-step instructions would > be much appreciated. > > Thank you! > > -- > Moshe Jacobson > Nead Werx, Inc. | Manager of Systems Engineering > 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 > mo...@neadwerx.com | www.neadwerx.com > > "Quality is not an act, it is a habit." -- Aristotle > Moshe, if you need a detailed cookbook tailored to your specific requirements, you may need to hire a PostgreSQL expert as a consultant to write it for you. Generalized guidelines can't possibly cover every possible situation. The Binary Replication Tutorial at http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial (which is slightly out of date as it does not cover 9.2 and 9.3 improvements yet) is probably going to cover most of what you need. The 'long method' is going to be pretty much what you need, you will still need to do a pg_start_backup() and pg_stop_backup() while you copy the data directory files, but you probably won't need to restart the master to change the master configuration files since you've already got replication working to one server and you're apparently not planning to have the second slave server poll the master for updates. -- Mike Nolan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to clone a running master cluster?
I have a master database cluster on one server, and it is configured to ship logs via scp to an archive directory on my slave server. The slave server is configured for streaming replication, and also is configured to delete the archived xlogs when they are no longer needed (using pg_archivecleanup). I have a third machine on which I'd like to get another master cluster running, and I'd like it to start with a copy of my current master. I'd cannot restart my master, and would prefer not to restart my slave either. Given my xlog archive configuration, Is there a way to clone my master cluster to another machine, including all of the necessary xlogs, without bringing down the original master or slave? Step-by-step instructions would be much appreciated. Thank you! -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle
Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID
On 05/10/2013 11:38 AM, Merlin Moncure wrote: PostgreSQL configuration changes: synchronous_commit = off effective_io_concurrency = 4 checkpoint_segments = 1024 checkpoint_timeout = 10min checkpoint_warning = 8min shared_buffers = 32gb temp_buffers = 128mb work_mem = 512mb maintenance_work_mem = 1gb that's good info, but it should be noted that synchronous_commit trades a risk of some data loss (but not nearly as much risk as volatile storage) for a big increase in commit performance. Yeah but it is an extremely low risk, and probably lower than say... a bad Apache form submission. Generally the database is the most reliable hardware in the cluster. It is also not a risk for corruption which a lot of people confuse it for. One thing I would note is that work_mem is very high, that might be alright with an SSD environment because if we go out to tape sort, it is still going to be fast but it is something to consider. Another thing is, why such a low checkpoint_timout? Set it to 60 minutes and be done with it. The bgwriter should be dealing with these problems. Sincerely, JD merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID
On 10 May 2013, at 16:25, David Boreham wrote: > I've never looked at SLC drives in the past few years and don't know anyone > who uses them these days. Because SLCs are still more expensive? Because MLCs are now almost as good as SLCs for performance/endurance? I should point out that this database will be the backend for a high-transaction gaming site with very heavy database usage including a lot of writes. Disk IO on the database server has always been our bottleneck so far. Also, the database is kept comparatively very small - about 25 GB currently, and it will grow to perhaps 50 GB this year as a result of new content and traffic coming in. So whilst MLCs might be almost as good as SLCs now, the price difference for us is so insignificant that if we can still get a small improvement with SLCs then we might as well do so. > Could you post some specific drive models please ? HP probably doesn't make > the drives, and it really helps to know what devices you're using since they > are not nearly as generic in behavior and features as magnetic drives. I've asked our HP dealer for this information since unfortunately it doesn't appear to be available on the HP website - hopefully it will be forthcoming at some point. Matt. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
