Re: [HACKERS] Hard to Use WAS: Hard limit on WAL space
On 15 June 2013 14:43, Craig Ringer cr...@2ndquadrant.com wrote: The #1 question I see on Stack Overflow has to be confusion about pg_hba.conf, mostly from people who have no idea it exists, don't understand how to configure it, etc. The totally non-obvious name of the file probably has something to do with that. It should be called 'auth.conf'. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hard to Use WAS: Hard limit on WAL space
On 06/15/2013 02:08 PM, Brendan Jurd wrote: On 15 June 2013 14:43, Craig Ringer cr...@2ndquadrant.com wrote: The #1 question I see on Stack Overflow has to be confusion about pg_hba.conf, mostly from people who have no idea it exists, don't understand how to configure it, etc. The totally non-obvious name of the file probably has something to do with that. It should be called 'auth.conf'. Not convinced; since it only controls one facet of auth - it doesn't define users, passwords, grants, etc - that'd probably be just as confusing. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hard to Use WAS: Hard limit on WAL space
On 15 June 2013 16:18, Craig Ringer cr...@2ndquadrant.com wrote: On 06/15/2013 02:08 PM, Brendan Jurd wrote: On 15 June 2013 14:43, Craig Ringer cr...@2ndquadrant.com wrote: The #1 question I see on Stack Overflow has to be confusion about pg_hba.conf, mostly from people who have no idea it exists, don't understand how to configure it, etc. The totally non-obvious name of the file probably has something to do with that. It should be called 'auth.conf'. Not convinced; since it only controls one facet of auth - it doesn't define users, passwords, grants, etc ... When somebody is setting up postgres for the first time, and they list the contents of the config directory, you want them to have some idea what each of the files is for. If they see something called 'auth.conf', they'll get the right general idea. An understanding of the nuances (like that it doesn't control user accounts) will come once they open up the file -- which they may well do, because it is called 'auth.conf', and 'auth' is a thing you want to configure. If they see something called 'pg_hba.conf', they may very reasonably assume that it is some internal/advanced stuff that they don't need to worry about just yet, because what the heck is a 'pg_hba'? The 'pg' is unnecessary and the 'hba' is an internal jargon term that we've ill-advisedly allowed to leak out into the filename. If you really feel that 'auth.conf' is too imprecise, maybe something like 'conn-auth.conf' would be more your style. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hard to Use WAS: Hard limit on WAL space
On 2013-06-15 08:44 CEST, Brendan Jurd wrote: On 15 June 2013 16:18, Craig Ringer ... wrote: On 06/15/2013 02:08 PM, Brendan Jurd wrote: On 15 June 2013 14:43, Craig Ringer ... wrote: The #1 question I see on Stack Overflow has to be confusion about pg_hba.conf, mostly from people who have no idea it exists, don't understand how to configure it, etc. The totally non-obvious name of the file probably has something to do with that. It should be called 'auth.conf'. Not convinced; since it only controls one facet of auth - it doesn't define users, passwords, grants, etc ... When somebody is setting up postgres for the first time, and they list the contents of the config directory, you want them to have some idea as they may not have read up to section 19.1 The pg_hba.conf File inside chapter 19 Client Authentication of part III. Server Administration :-?, which states (as of 9.2.4): Client authentication is controlled by a configuration file, which traditionally is named pg_hba.conf and is stored in the database cluster's data directory. (HBA stands for host-based authentication.) A default pg_hba.conf file is installed when the data directory is initialized by initdb. It is possible to place the authentication configuration file elsewhere, however; see the hba_file configuration parameter. ... ;-) thanks to hyperlinks this is quite close to the start, but I was surprised to not find it by skimming the text and following the hyperlinks but by knowing the filename instead and entering it (pg_hba.conf) into the Search Documentation text field on the top right corner of http://www.postgresql.org/docs/9.2/interactive/index.html. Maybe we could find a better place of the whatever-then-name inside the part of the docs even the TL;DR mood people might read? A paragraph or two spiced up with some catchy StackOverflow-inspired terms people with a need to configure this authentication aspect might have expected could also be expected in INSTALL like docs or directly observable on the hyperlinked way from part I. Tutorial chapter 1 Getting Started section 1.1 Installation all down to chapter 15. Installation from Source Code. But of course only, if this is wanted behavior. If I read the section 1.1 Installation (again 9.2.4) I have the impression, that it more transports the message in our case, that you are the site admin, deal with it, read the docs, or don't I read it right? (I am a non-native English reader) what each of the files is for. If they see something called 'auth.conf', they'll get the right general idea. An understanding of the nuances (like that it doesn't control user accounts) will come once they open up the file -- which they may well do, because it is called 'auth.conf', and 'auth' is a thing you want to configure. that may well be, I do not know, how people that prefer reading folder and filenames over manuals written for them grok text, as I read the docs, promised ;-) If they see something called 'pg_hba.conf', they may very reasonably assume that it is some internal/advanced stuff that they don't need to worry about just yet, because what the heck is a 'pg_hba'? The 'pg' is unnecessary and the 'hba' is an internal jargon term that we've ill-advisedly allowed to leak out into the filename. at around 1995 when I started using Postgres95 it sure took some time to find that pg_hba.conf file, but I then perceived it to be very well documented, and also felt a bit guilty, as it's name occured in the INSTALL file cf. ftp://ftp-archives.postgresql.org/pub/source/v7.2/postgresql-7.2.tar.gz and the INSTALL file. Therein burried inside Step 1 of If You Are Upgrading ... If you really feel that 'auth.conf' is too imprecise, maybe something like 'conn-auth.conf' would be more your style. I think you guys did and still do a fantastic job with PostgreSQL and eps. it's documentation, but in this case I doubt, that any renaming of config files will really have an impact on usability in the shady area of TL;DR - at least for the next twenty years or so - as it still holds, that from a false start (eg. not reading documentation written) anything may follow. But as usability is a practical concern I (as a user) would be +0 on renaming it if people not finding it bearing the old name, but then editing it is really wanted behavior. All the best, Stefan. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hard to Use WAS: Hard limit on WAL space
On 06/14/2013 11:18 PM, Craig Ringer wrote: On 06/15/2013 02:08 PM, Brendan Jurd wrote: On 15 June 2013 14:43, Craig Ringer cr...@2ndquadrant.com wrote: The #1 question I see on Stack Overflow has to be confusion about pg_hba.conf, mostly from people who have no idea it exists, don't understand how to configure it, etc. The totally non-obvious name of the file probably has something to do with that. It should be called 'auth.conf'. Not convinced; since it only controls one facet of auth - it doesn't define users, passwords, grants, etc - that'd probably be just as confusing. Yeah this one is not making the grade. pg_hba is just that host based auth but I think we are bikeshedding now. JD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hard to Use WAS: Hard limit on WAL space
On 06/14/2013 11:44 PM, Brendan Jurd wrote: If they see something called 'pg_hba.conf', they may very reasonably assume that it is some internal/advanced stuff that they don't need to worry about just yet, because what the heck is a 'pg_hba'? The 'pg' Only the uneducated. Look, I am not trying to be an ass but seriously. Read the docs. I will argue vigorously against the idea of us designing a system that has people NOT reading the docs. JD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hard to Use WAS: Hard limit on WAL space
On 06/15/2013 03:53 PM, Joshua D. Drake wrote: Yeah this one is not making the grade. pg_hba is just that host based auth but I think we are bikeshedding now. Agreed... Even as I posted, I realised I shouldn't have mentioned the last point, since everything else has been ignored. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hard to Use WAS: Hard limit on WAL space
On Sat, Jun 15, 2013 at 12:43:10PM +0800, Craig Ringer wrote: Bloat -- Table bloat. Table bloat has been a major issue with PostgreSQL users/admins for years. Anyone care to explain to me in a simple paragraph how to find out if you have table or index bloat issues in your database and what to do about it? (Maybe we need pg_catalog.pg_index_bloat and pg_catalog.pg_table_bloat views including FILLFACTOR correction?) I think I'll draft up a patch to add exactly that. Nice list btw. I monitor this by using the excellent check_progres nagios plugin, which has stuff to check for things like this. Which makes me think that it might be possible to add some other checks like this, in for example pg_ctl. A big fat warning 'your data may be eaten' might get noticed at startup. (A minor annoyance is that in recent version of PostgreSQL you have to give check_postgres admin rights, otherwise it can't warn you about idle in transaction problems.) Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [HACKERS] Hard to Use WAS: Hard limit on WAL space
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/15/2013 05:57 PM, Martijn van Oosterhout wrote: On Sat, Jun 15, 2013 at 12:43:10PM +0800, Craig Ringer wrote: Bloat -- Table bloat. Table bloat has been a major issue with PostgreSQL users/admins for years. Anyone care to explain to me in a simple paragraph how to find out if you have table or index bloat issues in your database and what to do about it? (Maybe we need pg_catalog.pg_index_bloat and pg_catalog.pg_table_bloat views including FILLFACTOR correction?) I think I'll draft up a patch to add exactly that. Nice list btw. I monitor this by using the excellent check_progres nagios plugin, which has stuff to check for things like this. It's been a vital tool for me too. It probably serves as a reasonable guide for some things we could really usefully expose in system views. Once in core we can document them in the main user manual, making them reasonably discoverable. Argh. I think my TODO has some kind of horrible disease, it keeps growing uncontrollably. Which makes me think that it might be possible to add some other checks like this, in for example pg_ctl. A big fat warning 'your data may be eaten' might get noticed at startup. The users who have this kind of issue aren't the ones running pg_ctl. They'll usually be using launchd, systemctl, upstart, sysv init scripts, etc ... whatever, something that sends the warning straight to the system logs that they likely never read. I don't have tons of sympathy for these people, but I do think making fsync=off so easy to set without understanding it is kind of like handing a grenade to a toddler. - -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.13 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQEcBAEBAgAGBQJRvD/8AAoJELBXNkqjr+S2aG8H/jnjATe1M+4O6k8iaS6Okgux HQU14nDaBh7tbRaNQZUlNFDaVoQA6cynN8Xzq8k5lqJxGnuNRR7SNw8+cZZiZmMe pS2f9q2IyOMz3T/mBNPuAFcPTbp6pjYrBNpMEGF6FYDhmUMSEfhf4Cp1Ns4FG0kx o5dIXnhgDpCCTBK4XiYqbijFGe0pqbOH98fTQJLXb2ItgE17t4jU0YoYPJovjjT8 xKnDggN+H3uPMmNTcxn0VL6XcrjM6oDeBQPtzCiePWWxYD4nwP3d0ZIok13jZSHm KC3NWgYQ7uP8/NJitnqewMQ8RArQjAWsW94deZt28jNDeaKp/vovQlZtrU2M6dQ= =aysr -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hard to Use WAS: Hard limit on WAL space
fsync=off synchronous_commits=off replaced fsync=off in almost every use case where fsync=off might have been useful. The only remaining use case is for the initial build of a database. In that case what the user really wants is to turn off WAL logging entirely though. Having a WAL log and not fsyncing it is kind of pointless. I guess it lets you replicate the database but it doesn't let you use the WAL log for recovery locally. Bloat -- Table bloat. Table bloat has been a major issue with PostgreSQL users/admins for years. Anyone care to explain to me in a simple paragraph how to find out if you have table or index bloat issues in your database and what to do about it? (Maybe we need pg_catalog.pg_index_bloat and pg_catalog.pg_table_bloat views including FILLFACTOR correction?) A nice view that exposes a summary of information from the fsm per table would be pretty handy. In general there's a lot of data tied up in things like the fsm that could be usefully exposed to users. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hard to Use WAS: Hard limit on WAL space
On 06/12/2013 02:03 PM, Joshua D. Drake wrote: What concerns me is we seem to be trying to make this easy. It isn't supposed to be easy. This is hard stuff. Smart people built it and it takes a smart person to run it. When did it become a bad thing to be something that smart people need to run? 1997, last I checked. Our unofficial motto: PostgreSQL: making very hard things possible, and simple things hard. It *is* hard. But that's because we've *made* it hard to understand and manage, not because the problem is inherently hard. For example: can you explain to me in 10 words or less how to monitor to see if archiving is falling behind? I'll bet you can't, and that's because we've provided no reliable way to do so. It's normal when you're developing features for the ability to utilize them to go from hacker -- high-end user -- regular user. We suck at moving to that last stage, partly because whenever someone on this list introduces the idea of making a feature not just great but easy to use, people actually object to the idea that anything should be easy to use. It's like we're afraid of being polluted by the unwashed DevOps masses. In the meantime, Mongo kicks our butts a new user adoption. Why? Their features suck, but the features they do have are easy to use. You'd think we would have learned something from MySQL. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hard to Use WAS: Hard limit on WAL space
On 06/14/2013 11:16 AM, Josh Berkus wrote: On 06/12/2013 02:03 PM, Joshua D. Drake wrote: What concerns me is we seem to be trying to make this easy. It isn't supposed to be easy. This is hard stuff. Smart people built it and it takes a smart person to run it. When did it become a bad thing to be something that smart people need to run? 1997, last I checked. Our unofficial motto: PostgreSQL: making very hard things possible, and simple things hard. It *is* hard. But that's because we've *made* it hard to understand and manage, not because the problem is inherently hard. For example: can you explain to me in 10 words or less how to monitor to see if archiving is falling behind? I'll bet you can't, and that's because we've provided no reliable way to do so. Hey, I never said we shouldn't have a complete feature set. I agree with you. IMO it should not have even been committed without the ability to actually know what is going on and we have had it since (in theory) 8.1? My primary concern is: Don't make it stupid. I liked Claudio's comment, More than easy, it should be obvious.. It should be obvious from a review of the documentation how to manage this stuff. It isn't, and worse even if we wrote the documentation it still isn't because the feature is not complete. With great power comes great responsibility :P JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hard to Use WAS: Hard limit on WAL space
On 06/15/2013 02:16 AM, Josh Berkus wrote: On 06/12/2013 02:03 PM, Joshua D. Drake wrote: What concerns me is we seem to be trying to make this easy. It isn't supposed to be easy. This is hard stuff. Smart people built it and it takes a smart person to run it. When did it become a bad thing to be something that smart people need to run? 1997, last I checked. Our unofficial motto: PostgreSQL: making very hard things possible, and simple things hard. It *is* hard. But that's because we've *made* it hard to understand and manage, not because the problem is inherently hard. I have to agree with all this... Pg has some of the best docs around, a really nice SQL level interface, and some truly shocking usability outside that nice zone. Once a user steps into the admin zone they're confronted with a lot of settings they'll really struggle to understand and manage. I don't want this to be used as an argument not to commit early stages of work, though. I think iterative development with exposure to real-world testing and experience is necessary when you're getting to the complexity of things that are now going in to Pg. It's more that commited != done; right now, once its usable at that power-user stage further management and improvement gets farmed out to external tools and the usability of the core feature stays rather ... rough. Some examples: fsync=off We have a giant foot-cannon in the config files, fsync with the off option neatly documented alongside all the others. No note saying setting fsync=off is equivalent to setting yes_you_can_eat_my_data=on. No WARNING in the logs, not that a user who'd set that without understanding it would look at the logs. The fsync section of http://www.postgresql.org/docs/current/static/runtime-config-wal.html http://www.postgresql.org/docs/9.2/static/runtime-config-wal.html is ok, though it could do with a more prominent warning... but the user needs to know where to look. I've repeatedly asked to change this - usually after yet another user comes onto -general with data loss due to not understanding fsync=off - and haven't been able to get agreement on even a config file comment. Proposed fix 9.3, config file comment saying Warning, fsync=off may cause data loss, see the user manual. Proposed fix 9.4+: Remove fsync=off from docs. Leave the GUC enum there but have the postmaster FATAL when it sees it with a message saying fsync=off has been replaced with unsafe_writes=on, please change your postgresql.conf. Add the corresponding new GUC. max_connections max_connections is another one. I see systems with max_connections=3000 in the wild... performing terribly, as you'd expect. Yet there's no indication (even in the docs) that this is often a terrible idea, and that you should really look into a connection pooler if you're going above a few hundred (hardware/workload dependent). http://www.postgresql.org/docs/current/static/runtime-config-connection.html http://www.postgresql.org/docs/9.1/static/runtime-config-connection.html doesn't mention it, there's no config file comment, etc. Proposed fix: Comment in the config file saying something like See the documentation before raising this above a few hundred. In the docs, a note about the perf impact of high max_connections with a brief mention of external connection pooling and links to pgbouncer/pgpool-II, mention that many app frameworks have built-in connection pools. Brief comment about there being an optimum workload-and-hardware dependent level of concurrency above which performance degrades. I'll be happy to write a draft patch for this if there's agreement on the idea. vacuum/autovacuum --- autovaccum tuning. We've just had this conversation and there seems to be agreement that it needs some love, but unlike the above two there's no easy fix and it's an ongoing process. I don't have any right to complain about it unless I do more to help fix it. Bloat -- Table bloat. Table bloat has been a major issue with PostgreSQL users/admins for years. Anyone care to explain to me in a simple paragraph how to find out if you have table or index bloat issues in your database and what to do about it? (Maybe we need pg_catalog.pg_index_bloat and pg_catalog.pg_table_bloat views including FILLFACTOR correction?) I think I'll draft up a patch to add exactly that. Dump/restore and globals -- Dump and restore. The standard advice I give is to do a pg_dumpall --globals-only followed by a pg_dump -Fc of each database, since we don't have pg_dumpfall -Fc. Users often seem to do single-DB dumps then find themselves having trouble restoring them due to missing user accounts, etc. Or they do a pg_dumpall then want to restore just one DB/table. There's also a lot of confusion around restoring dumps due to the different formats. This has improved now that pg_restore tells the user to restore a SQL dump using psql: $
