Re: [HACKERS] Brain dump: btree collapsing
Tom, Sound excellent. Index growth has been something that always bothered me (not the disk space usage, but the slow searches). I believe it's best to have pages marked dead at the time the last key contained in the page is deleted (you didn't discuss how efficient this is), because this will somehow improve the three depth. The same functionality should be available in VACUUM (just in case). Thus we should 'free' the index pages with one VACUUM run, instead of two. In the spirit of my ramblings about automatic statistics/suggestions by PostgreSQL for optimizations, could you also implement a NOTICE when the index becomes too 'thin'? I believe this will help avoid severe performance degradation if the process of removing the dead tuples becomes automatic. It also occurs to me, that if such statistics are available, PostgreSQL might run VACUUM automatically, on specific tables/indexes - all this controlled by a CUG variable. Daniel ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Brain dump: btree collapsing
Justin Clift said: snip In theory, if we find recyclable page(s) at the physical end of the index, we could truncate the file (ie, give the space back to the filesystem) instead of reporting these pages to FSM. I am not sure if this is worth doing --- in most cases it's likely that little space can be released this way, and there may be some tricky locking issues. Sounds like this would be beneficial for environments with high update/delete transaction volumes, perhaps on smaller amounts of live/valid data. But if dead pages are removed (returned to FSM?) as soon as last item is removed from the page, the page usage will remain small. Or perhaps not? That is, it's unlikely to collect large number of dead/free pages at the end of the physical storage except if doing all this in single VACUUM session. Daniel ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PostgreSQL Benchmarks
Christopher Kings-Lynne wrote: Hrm. I just saw that the PHP ADODB guy just published a bunch of database benchmarks. It's fairly evident to me that benchmarking PostgreSQL on Win32 isn't really fair: http://php.weblogs.com/oracle_mysql_performance *sigh* Not fair, perhaps. But if you look, you'll see that *Cygwin* PostgreSQL beat most everything on the Win32 platform except MySQL and Oracle with PL/SQL. Read further and you'll see that Cygwin PostgreSQL came *really* close (within 10% or something) to MS-SQL. Considering that they weren't even running a native version of PostgreSQL, I think the results were surprisingly *good*. But yes, we really do want to be the fastest. :-) -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL Tuning Results
On Wed, 12 Feb 2003, [ISO-8859-1] Hans-J$B|(Brgen Sch$Bv(Bnig wrote: (B (B Be careful with sort_mem - this might lead to VERY unexpected results. I (B did some testing on my good old Athlon 500 with a brand new IBM 120 Gigs (B HDD. Reducing the sort_mem gave me significantly faster results when (B sorting/indexing 20.000.000 randon rows. (B (BActually, the results are completely expected once you know what's (Bexactly is going on. I found it weird that my sorts were also slowing (Bdown with more sort memory until Tom or Bruce or someone pointed out to (Bme that my stats said my sorts were swapping. (B (BIf I'm understanding this correctly, this basically meant that my sort (Bresults would start hitting disk becuase they were being paged out to (Bswap space, but then once the block was sorted, it would be read in (Bagain from disk, and then written out to disk again (in a different (Bplace), creating a lot more I/O than was really necessary. (B (BThis strikes me, too, as another area where mmap might allow the system (Bto do a better job with less tuning. Basically, the sort is getting (Bsplit into a bunch of smaller chunks, each of which is individually (Bsorted, and then you merge at the end, right? So if all those individual (Bchunks were mmaped, the system could deal with paging them out if and (Bwhen necessary, and for the sorts you do before the merge, you could (Bmlock() the area that you're currently sorting to make sure that it (Bdoesn't thrash. (B (BIf the VM system accepts hints, you might also get some further (Boptimizations because you can tell it (using madvise()) when you're (Bdoing random versus sequential access on a chunk of memory. (B (Bcjs (B-- (BCurt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org (BDon't you know, in this new Dark Age, we're all light. --XTC (B (B---(end of broadcast)--- (BTIP 4: Don't 'kill -9' the postmaster
[HACKERS] set_ps_display on solaris x86
Our students are (unfortunately) on solaris x86 (unfortunate because I have to do another round of testing before I deploy pgsql code for them to hack). Sadly, set_ps_display does not seem to have any effect in solaris x86. At least ps only reports multiple postmaster processes and arguments. Is there some magic incantation to be chanted to get this to work ? Thanks ! -- Pip-pip Sailesh http://www.cs.berkeley.edu/~sailesh ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] location of the configuration files
Well, to the extent that you're serious, you understand that a lot of people feel that /usr/local should be reserved for stuff that's installed by the local sysadmin, and your vendor/distro isn't supposed to be messing with it. Which means if the the vendor installed Postgresql (say, the Red Hat Database) you'd expect config files to be in /etc. If the postgresql is compiled from source by local admin, you might look somewhere in /usr/local. Indeed. For better or worse, there is a Filesystem Hierarcy Standard, and most of the important Linux distros, BSDs and some legacy Unixen stick to it, so so should we. Configuration files should be in /etc/postgresql/, or at the very least symlinked from there. Martin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] location of the configuration files
On Wed, 12 Feb 2003, Peter Bierman wrote: What do you gain by having the postmaster config and the database data live in different locations? You can then standardize a location for the configuration files. Everybody has room in /etc for another 10K of data. Where you have room for something that might potentially be a half terrabyte of data, and is not infrequently several gigabytes or more, is pretty system-depenendent. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Brain dump: btree collapsing
tom lane initially wrote: Restructuring the tree during page deletion --- We will delete only completely-empty pages. If we were to merge nearly-empty pages by moving data items from one page to an adjacent one, this would imply changing the parent's idea of the bounding key between them --- which is okay if we are just deleting an internal key in the parent, but what if the pages have different parent pages? and a bit later wrote: My feeling is that what we need to fix now is index bloat during normal operation. How about doing deletion of partial pages with reorganization among sibling pages only (where the parent pages are the same)? This avoids the messiness of propogating the deletes to differing parent pages but gets most of the value of reorganization. ISTM, that a VACUUM that only reclaims empty pages will be helpful in certain cases but won't help much at all in many other common normal operation cases which would be helped by partial reorganization. - Curtis ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] location of the configuration files
In the last exciting episode, [EMAIL PROTECTED] (Curt Sampson) wrote: On Wed, 12 Feb 2003, Peter Bierman wrote: What do you gain by having the postmaster config and the database data live in different locations? You can then standardize a location for the configuration files. Everybody has room in /etc for another 10K of data. Where you have room for something that might potentially be a half terrabyte of data, and is not infrequently several gigabytes or more, is pretty system-depenendent. Ah, but this has two notable problems: 1. It assumes that there is a location for the configuration files for /the single database instance./ If I have a second database instance, that may conflict. 2. It assumes I have write access to /etc If I'm a Plain Old User, as opposed to root, I may only have read-only access to /etc. These conditions have both been known to occur... -- If this was helpful, http://svcs.affero.net/rm.php?r=cbbrowne rate me http://www.ntlug.org/~cbbrowne/rdbms.html Some people, when confronted with a Unix problem, think I know, I'll use sed. Now they have two problems. -- Jamie Zawinski ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [INTERFACES] postgresql 7.3 versus 7.2
On Wed, 2003-02-12 at 16:02, Ulf Rehmann wrote: Hi! Is there any way to adjust dumps from postgresql 7.3 for use by version 7.2 and vice versa ? Or is there a way to transport a full database via csv or in any other kind to 7.2 and vice versa ? We are running a couple of servers/mirrors at various locations in the world, handling data based on postgresql, and now we have trouble to transport data from and to sites with different versions. Create a schema only dump with the 7.2 pg_dump. Create a data only dump (possibly with INSERT output) with the 7.3 pg_dump. Assuming you haven't done anything too wacky, you should be able to drop your 7.2 database, reload the 7.2 schema, then load up the 7.3 data. As always, test this out before doing it on a production system. Robert Treat ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] location of the configuration files
Christopher Browne wrote: In the last exciting episode, [EMAIL PROTECTED] (Curt Sampson) wrote: On Wed, 12 Feb 2003, Peter Bierman wrote: What do you gain by having the postmaster config and the database data live in different locations? You can then standardize a location for the configuration files. Everybody has room in /etc for another 10K of data. Where you have room for something that might potentially be a half terrabyte of data, and is not infrequently several gigabytes or more, is pretty system-depenendent. Ah, but this has two notable problems: 1. It assumes that there is "a location" for "the configuration files for /the single database instance./" If I have a second database instance, that may conflict. 2. It assumes I have write access to /etc If I'm a Plain Old User, as opposed to root, I may only have read-only access to /etc. These conditions have both been known to occur... These are not issues at all. You could put the configuration file anywhere, just as you can for any UNIX service. postmaster --config=/home/myhome/mydb.conf I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system. What's really annoying is when you have to find the data directory when someone else set up the system. Configuring postgresql via a configuration file which specifies all the data, i.e. data directory, name of other configuration files, etc. is the right way to do it. Even if you have reasons against it, even if you think it is a bad idea, a bad standard is almost always a better solution than an arcane work of perfection. Personally, however, I think the configuration issue is a no-brainer and I am amazed that people are balking. EVERY other service on a UNIX box is configured in this way, why not do it this way in PostgreSQL? The patch I submitted allowed the configuration to work as it currently does, but allowed for the more standard configuration file methodology. I just don't understand what the resistance is, it makes no sense.
Re: [HACKERS] location of the configuration files
On Thu, 13 Feb 2003, Christopher Browne wrote: 1. It assumes that there is a location for the configuration files for /the single database instance./ No; it assumes that there's a location for the default instance. If you have more than one, you could have one default and one elsewhere, or just do what I often do, which is put in an empty config file except for a comment saying we have several instances of xxx on this machine; look in yyy for them. 2. It assumes I have write access to /etc If I'm a Plain Old User, as opposed to root, I may only have read-only access to /etc. Right. It's dependent on the sysadmin to create /etc/postgres/ and make it writeable, or set up proper symlinks, or whatever. Fortunately, the files in /etc are only the defaults, to be used if they're not overridden on the command line. If you're in a situation like #2, you're basically stuck where we are now all the time: you have to just put it somewhere and hope that, if someone else needs to find it, they can. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] location of the configuration files
On Thursday 13 February 2003 08:32, Christopher Browne wrote: In the last exciting episode, [EMAIL PROTECTED] (Curt Sampson) wrote: Everybody has room in /etc for another 10K of data. Where you have room for something that might potentially be a half terrabyte of data, and is not infrequently several gigabytes or more, is pretty system-depenendent. 1. It assumes that there is a location for the configuration files for /the single database instance./ If I have a second database instance, that may conflict. If you run multiple servers of any kind, the second and subsequent servers must have a command line switch specifying the location of the config file. This is the way named, sendmail, et al do it. I have run multiple nameds on a single box, by using alternate config file locations. 2. It assumes I have write access to /etc If I'm a Plain Old User, as opposed to root, I may only have read-only access to /etc. So you start postmaster with a config file switch pointing to your config file in your tree. Or you specify the default location with a configure switch at compile time. Or you do it the same way you would run any other typical daemon as a regular user. How does Apache, AOLserver (my favorite), sendmail, jabberd, named, or any other typical daemon do it? For example, AOLserver can easily be installed and run as a plain user (just not on port 80). The command line switch '-t' specifies the tcl configuration script's location. There is no default. The configuration script then specifies pageroot and the like -- and a webserver is very much like running PostgreSQL -- you have configuration, you have logs, and you have the spool (database or pageroot). All can be in different locations at the discretion of the admin. And hardcoding dependencies like this stifles the discretion of the admin. These conditions have both been known to occur... Just because the situation is known to occur doesn't mean the whole direction of a project should hinge on those corner cases. They should be allowed but not forced. For better or for worse, thanks to Karl DeBisschop, the latest RPMs have the ability to start multiple postmasters with their data trees and configurations in different places. This is all done in the startup script, and required no new functionality from postmaster. I personally think it is for the better; YMMV. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Brain dump: btree collapsing
Daniel Kalchev [EMAIL PROTECTED] writes: ... Thus we should 'free' the index pages with one VACUUM run, instead of two. Bear in mind that the only thing that deletes index entries is ... VACUUM. Thus what we're really discussing is whether VACUUM should delete an index page at the same time it deletes the last entry thereon, or in a separate pass over the index. This is a minor implementation detail, not something the user will really notice in terms of having to run VACUUM multiple times to get the same effect. Also, it will definitely take two VACUUM runs (minimum) before a formerly-live page can enter FSM. Once the page is marked dead (in one run) we still need to wait for the drain interval before we can give it to FSM (in a later run). This is more or less analogous to the fact that VACUUM can't remove recently-deleted heap tuples, even though they are committed dead. You have to wait till there's no one left that might want to access that tuple (or index page). regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] location of the configuration files
On Thu, 13 Feb 2003, Curt Sampson wrote: On Thu, 13 Feb 2003, Christopher Browne wrote: 1. It assumes that there is a location for the configuration files for /the single database instance./ No; it assumes that there's a location for the default instance. If you have more than one, you could have one default and one elsewhere, or just do what I often do, which is put in an empty config file except for a comment saying we have several instances of xxx on this machine; look in yyy for them. 2. It assumes I have write access to /etc If I'm a Plain Old User, as opposed to root, I may only have read-only access to /etc. Right. It's dependent on the sysadmin to create /etc/postgres/ and make it writeable, or set up proper symlinks, or whatever. Fortunately, the files in /etc are only the defaults, to be used if they're not overridden on the command line. If you're in a situation like #2, you're basically stuck where we are now all the time: you have to just put it somewhere and hope that, if someone else needs to find it, they can. It doesn't follow this line of argument directly but it's to do with this thread... Is everyone forgetting that wherever the configuration file is stored and whether or not it needs a command line argument to specify it the database is not going to start up automatically unless at least part of the installation is done as root anyway? As I like to install software as a non root user normally anyway I am happy that the config file lives somewhere not requiring write access by the installer. However, I think having it in an etc directory is a good thing (tm). So, colour me an uncommited, fence sitter :) I'm not talking distribution/package installation here but just plain system administration. Being an untrusting soul I do _not_ want to type make install as root and find things installed outside of where I say I want things placed. That includes configuration files. Doing this as a normal user protects the system from bad software which assumes things about the host system. It also simplifies switching between versions of software, try doing that if your config is /etc/postgresql/postgres.conf. -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] set_ps_display on solaris x86
Sailesh Krishnamurthy [EMAIL PROTECTED] writes: Sadly, set_ps_display does not seem to have any effect in solaris x86. At least ps only reports multiple postmaster processes and arguments. IIRC, you have to use the other version of ps to see the process status on Solaris. I forget where it lives exactly, /usr/ucb maybe? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Brain dump: btree collapsing
Curtis Faith [EMAIL PROTECTED] writes: ISTM, that a VACUUM that only reclaims empty pages will be helpful in certain cases but won't help much at all in many other common normal operation cases which would be helped by partial reorganization. Got any evidence to back that up? I was relying on [Johnson89] Johnson, T. and Shasha, D. Utilization of B-trees with Inserts, Deletes and Modifies ACM Symp. on PODS, 235-246, 1989. which provides a ton of math and simulations leading up to the conclusion that collapsing btree pages before they are fully empty doesn't really gain anything meaningful in terms of storage utilization, in most scenarios. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [INTERFACES] postgresql 7.3 versus 7.2
Robert Treat [EMAIL PROTECTED] writes: On Wed, 2003-02-12 at 16:02, Ulf Rehmann wrote: Is there any way to adjust dumps from postgresql 7.3 for use by version 7.2 and vice versa ? Create a schema only dump with the 7.2 pg_dump. Create a data only dump (possibly with INSERT output) with the 7.3 pg_dump. Assuming you haven't done anything too wacky, you should be able to drop your 7.2 database, reload the 7.2 schema, then load up the 7.3 data. As always, test this out before doing it on a production system. I think he'll have trouble even with that :-( ... 7.3 pg_dump will try to use column-name-list syntax in its COPY commands, which the 7.2 server won't like. AFAICS the only answer is to run the data dump through a filter (sed or perl or whatever) to edit the COPY commands to a format 7.2 will take. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] location of the configuration files
On Thu, Feb 13, 2003 at 15:03:09 +, Nigel J. Andrews [EMAIL PROTECTED] wrote: Is everyone forgetting that wherever the configuration file is stored and whether or not it needs a command line argument to specify it the database is not going to start up automatically unless at least part of the installation is done as root anyway? Users can use cron to start there own instance. Your cron script can check if the server is running every (say) 15 minutes and start the server if it isn't. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] location of the configuration files
On Thu, Feb 13, 2003 at 09:23:20 -0500, mlw [EMAIL PROTECTED] wrote: Personally, however, I think the configuration issue is a no-brainer and I am amazed that people are balking. EVERY other service on a UNIX box is configured in this way, why not do it this way in PostgreSQL? The patch I submitted allowed the configuration to work as it currently does, but allowed for the more standard configuration file methodology. If you are interested in reading a contrary position, you can read Berstein's arguments for his recommended way to install services at: http://cr.yp.to/unix.html ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [INTERFACES] postgresql 7.3 versus 7.2
On Thu, 2003-02-13 at 10:42, Tom Lane wrote: I think he'll have trouble even with that :-( ... 7.3 pg_dump will try to use column-name-list syntax in its COPY commands, which the 7.2 server won't like. If you used an insert-only, data-only dump, that might work... Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 09:23, mlw wrote: I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system. What's really annoying is when you have to find the data directory when someone else set up the system. find / -name postgresql.conf -print you now know where all of your configuration files are and where the data for each of those servers is as well. (Not I'm not against the idea...) Robert Treat ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] location of the configuration files
Robert Treat wrote: On Thu, 2003-02-13 at 09:23, mlw wrote: I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system. What's really annoying is when you have to find the data directory when someone else set up the system. find / -name postgresql.conf -print LOL, That is NOT an option. It can take hours on some systems. Specifically, one of the systems is freedb server, it has over 300,000 files in a directory tree.
Re: [HACKERS] location of the configuration files
Bruno Wolff III wrote: On Thu, Feb 13, 2003 at 09:23:20 -0500, mlw [EMAIL PROTECTED] wrote: Personally, however, I think the configuration issue is a no-brainer and I am amazed that people are balking. EVERY other service on a UNIX box is configured in this way, why not do it this way in PostgreSQL? The patch I submitted allowed the configuration to work as it currently does, but allowed for the more standard configuration file methodology. If you are interested in reading a contrary position, you can read Berstein's arguments for his recommended way to install services at: http://cr.yp.to/unix.html Where, specificaly are his arguements against a configuration file methodology?
Re: [HACKERS] location of the configuration files
On Thu, 13 Feb 2003, mlw wrote: Robert Treat wrote: On Thu, 2003-02-13 at 09:23, mlw wrote: I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system. What's really annoying is when you have to find the data directory when someone else set up the system. You realize that the actual code feature doesn't necessarily help this case, right? Putting configuration in /etc and having a configuration file option on the command line are separate concepts. I think the feature is worthwhile, but I have some initial condition functionality questions that may have been answered in the previous patch, but I don't remember at this point. Mostly these have to deal with initial creation. Does the user specify an output location to initdb, do they just specify a data dir as now where the configuration goes but then they need to move it somewhere, does initdb now do nothing relating to configuration file and the user should make one on his own. Related, is the admin expected to have already made (say) /etc/postgresql to stick the config in and set the permissions correctly (since initdb doesn't run as root)? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] location of the configuration files
Stephan Szabo wrote: On Thu, 13 Feb 2003, mlw wrote: Robert Treat wrote: On Thu, 2003-02-13 at 09:23, mlw wrote: I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system. What's really annoying is when you have to find the data directory when someone else set up the system. You realize that the actual code feature doesn't necessarily help this case, right? Putting configuration in /etc and having a configuration file option on the command line are separate concepts. I think the feature is worthwhile, but I have some initial condition functionality questions that may have been answered in the previous patch, but I don't remember at this point. Mostly these have to deal with initial creation. Does the user specify an output location to initdb, do they just specify a data dir as now where the configuration goes but then they need to move it somewhere, does initdb now do nothing relating to configuration file and the user should make one on his own. Related, is the admin expected to have already made (say) /etc/postgresql to stick the config in and set the permissions correctly (since initdb doesn't run as root)? My patch only works on the PostgreSQL server code. No changes have been made to the initialization scripts. The patch declares three extra configuration file parameters: hbafile= '/etc/postgres/pg_hba.conf' identfile='/etc/postgres/pg_ident.conf' datadir='/RAID0/postgres' The command line option is a capital 'C,' as in: postmaster -C /etc/postgresql.conf I have no problem leaving the default configuration files remaining in the data directory as sort of a maintenance / boot strap sort of thing, so I don't see any reason to alter the installation. As for this feature helping or not, I think it will. I think it accomplishes two things: (1) Separates configuration from data. (2) Allows an administrator to create a convention across multiple systems regardless of the location and mount points of the database storage. (3) Lastly, it is a familiar methodology to DBAs not familiar with PostgreSQL. Again, I don't see a valid reason for not including the patch. Yes, if you don't want to configure PostgreSQL that way, then so be it, but why not add the functionality for those who do? I can envision the configuration file methodology of managing a database becoming the "preferred" approach over time as it is a more familiar and standard way of configuring servers on UNIX.
Re: [HACKERS] horology and time failures on freebsd/alpha
Christopher Kings-Lynne [EMAIL PROTECTED] writes: I'm still getting failure, but it gets weirder: Fixed --- turns out the bug is that timetz_out was scribbling on its input (thereby changing the table) in the --enable-integer-datetimes path. I back-patched the change into 7.3, although I believe the bug cannot occur in just this form in 7.3. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Brain dump: btree collapsing
tom lane wrote: Got any evidence to back that up? I was relying on [Johnson89] Johnson, T. and Shasha, D. Utilization of B-trees with Inserts, Deletes and Modifies ACM Symp. on PODS, 235-246, 1989. which provides a ton of math and simulations leading up to the conclusion that collapsing btree pages before they are fully empty doesn't really gain anything meaningful in terms of storage utilization, in most scenarios. Well, I don't have that specific paper handy but I do have [JS93] Theodore Johnson , Dennis Shasha, B-trees with inserts and deletes: why free-at-empty is better than merge-at-half which appears to be their later thinking on the same subject. Note the following: A merge-at-half B-tree will always have a space utilization of at least 50%. When all operations are modify operations, or when the number of insert operations is the same as the number of delete operations, then the utilization will be about 60%. In contrast, a free-at-empty B-tree has a 0% lower bound on its space utilization, and will have about 39% utilization on a pure-modify instruction mix. However, the space utilization of a free-at-empty B-tree remains high if there are just a few more insert operations than delete operations. Thus, merge-at-half usually buys little in terms of space utilization. In Figure 6, we showed that the restructuring rate of a merge-at-half B-tree is significantly larger than the restructuring rate of a free-at-empty B-tree for all values of q * :1. For many concurrent B-tree algorithms used in practice [4, 13], restructuring causes a serialization bottleneck. Thus, one simple but important way to increase concurrency in B-tree operations is to reduce the probability of restructuring. Since merge-at-half buys little space utilization but is expensive in terms of restructuring, we recommend that B-trees (especially concurrently accessed ones) use free-at-empty. I don't dispute their conclusions in that context and under the circumstances they outline of random distribution of deletion and insertion values for the index keys. However, as [Jannink]: Implementing Deletion in B+-trees. SIGMOD RECORD, v.24, n.1, p.33-38, 1995 points out that assumption doesn't hold under other possibly common circumstances, specifically circumstances where the deletes are taking place in significant sections of the index at a much higher rate than inserts. Consider from [Jannink95]: There has been some research on the acceptability of relaxing the constraint of minimum node size to reduce the number of so-called unsafe tree operations, i.e., those which contain node splitting and merging [ZH89]. The debate has culminated in analysis of a weaker form of the deletion algorithm which we call lazy deletion, that imposes no constraints on the number of entries left in the nodes, allowing them to empty completely before simply removing them. According to [GR93], most database system implementations of B+-trees have adopted this approach. Its most effective use is when it is vital to allow concurrent access to the tree [JS93b], and excessive splitting and merging of nodes would restrict concurrency. [JS89] derives some analytic solutions calculating memory utilization for B+-trees under a mix of insertions and lazy deletions, based on previous research which considered insertions only [BY89]. The simulations in [JS89] support its analysis to show that in typical situations, where deletions don't outnumber insertions in the mix of operations, the tree nodes will contain acceptable percentages of entries. One of the work's assumptions [JS93a] is that the keys and tree operations are chosen uniformly from a random distribution. This assumption is unreasonable in certain realistic situations such as one described below. Allowing interior nodes with only a single pointer to exist in a B+-tree creates the possibility for arbitrarily unbalanced trees of any height, which are virtually empty, and in which access times have degenerated from the logarithmic bound B+-trees are meant to guarantee to a worst case unbounded access time. Since nodes are not removed until they are completely empty, the lazy deletion algorithm does not regulate tree height effectively. Jannink then illustrates an example where an index is created based on a timestamp where the basic assumption of Johnson and Sasha does not hold since it is not a random distribution but a monotonically increasing value. His example is an extreme one but I believe there are many instances where a timestamp, sequence or some other monotonically increasing value is used in an index and where deletes are taking place much more frequently for largely older values. Since sequences are often used as foreign keys a significant number of indexes fit into this category. Consider a web site that tracked users and that deleted inactive accounts. There are many real-world scenarios where the number of inactive accounts is very high as a percentage of
Re: [HACKERS] Brain dump: btree collapsing
Curtis Faith [EMAIL PROTECTED] writes: I don't dispute their conclusions in that context and under the circumstances they outline of random distribution of deletion and insertion values for the index keys. [But the random-distribution assumption doesn't always hold.] That's a fair point. Nonetheless, we have little choice: we cannot move keys around during concurrent operations. If we push keys to the right, we may cause an indexscan moving left to miss them, and vice versa. So we can only eliminate empty pages. We could possibly allow VACUUM FULL to collapse partly-full pages, since in that case we know there are no concurrent scans. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Someone's broken psql's connection-failure error reporting
In CVS tip, I'm getting only an empty string from psql where it should print connection-failure messages. psql 7.3 does this: $ psql -p psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.? $ CVS tip does this: $ psql -p psql: $ Have not dug to see if this is the fault of libpq or psql; but there's been a fair amount of hacking lately in libpq's connection handling ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] parse_expr.c another type produced by transformations?
Greg Stark [EMAIL PROTECTED] writes: Ran into this in cvs tip checked out as of yesterday: db= SELECT sum(CASE WHEN (upper(substr((select 'en'::varchar),1,1)) not between 'A' and 'Z') THEN 1 ELSE 0 END) AS n FROM tab; ERROR: transformExpr: does not know how to transform node 309 (internal error) 309 is T_FuncExpr. Is this a node type that should be added as described by the comment? Yeah, you're right, and three other node types too. I missed this spot while changing the representation of Expr the other day. Thanks for catching it. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] location of the configuration files
On Thu, 13 Feb 2003, mlw wrote: Stephan Szabo wrote: On Thu, 2003-02-13 at 09:23, mlw wrote: I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system. What's really annoying is when you have to find the data directory when someone else set up the system. You realize that the actual code feature doesn't necessarily help this case, right? Putting configuration in /etc and having a configuration file option on the command line are separate concepts. Re-read my statement and yours about the case you were mentioning. ;) Sure, putting the files in /etc lets you find them easily. However, if you're doing things like finding configuration made by someone else and said configuration isn't in /etc (which if they wanted to they could do now with symlinks I believe - yes symlinks aren't a complete solution, but I think they're reasonable on most of our current ports) then you still have to search the system for the configuration file, except now it might not even be postgresql.conf. That's why I said the two issues aren't the same. I think the feature is worthwhile, but I have some initial condition functionality questions that may have been answered in the previous patch, but I don't remember at this point. Mostly these have to deal with initial creation. Does the user specify an output location to initdb, do they just specify a data dir as now where the configuration goes but then they need to move it somewhere, does initdb now do nothing relating to configuration file and the user should make one on his own. Related, is the admin expected to have already made (say) /etc/postgresql to stick the config in and set the permissions correctly (since initdb doesn't run as root)? My patch only works on the PostgreSQL server code. No changes have been made to the initialization scripts. The patch declares three extra configuration file parameters: hbafile= '/etc/postgres/pg_hba.conf' identfile='/etc/postgres/pg_ident.conf' datadir='/RAID0/postgres' The command line option is a capital 'C,' as in: postmaster -C /etc/postgresql.conf I have no problem leaving the default configuration files remaining in the data directory as sort of a maintenance / boot strap sort of thing, so I don't see any reason to alter the installation. As for this feature helping or not, I think it will. I think it accomplishes two things: (1) Separates configuration from data. (2) Allows an administrator to create a convention across multiple systems regardless of the location and mount points of the database storage. (3) Lastly, it is a familiar methodology to DBAs not familiar with PostgreSQL. I agree on all these points (I think the feature is worthwhile, but...). I just wonder if we were going to do this, we might as well look at all of the various things people want and decide what we want to do, for example, people commenting on default configuration locations through configure, how does this interact with what we have now, etc. I'd rather have a month spent arguing out a behavior rather than just adding a new behavior that we'll need to possibly revisit again in the future. :) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] location of the configuration files
On 13 Feb 2003, Martin Coxall wrote: Well, to the extent that you're serious, you understand that a lot of people feel that /usr/local should be reserved for stuff that's installed by the local sysadmin, and your vendor/distro isn't supposed to be messing with it. Which means if the the vendor installed Postgresql (say, the Red Hat Database) you'd expect config files to be in /etc. If the postgresql is compiled from source by local admin, you might look somewhere in /usr/local. Indeed. For better or worse, there is a Filesystem Hierarcy Standard, and most of the important Linux distros, BSDs and some legacy Unixen stick to it, so so should we. Configuration files should be in /etc/postgresql/, or at the very least symlinked from there. So, how do we handle things like installing three or four versions at the same time. This isn't the same thing as /etc/fstab. While we only would likely need to have one fstab or whatever, with postgresql, it's not unreasonable to want to intall more than one copy or version for various reason. Generally things that live in /etc are owned and operated by the OS. Postgresql, by it's definition is a userspace program, not an OS owned one. I've found having a $PGDATA var where EVERYTHING lives to be a huge advantage when you need to run a half dozen instances of pgsql under different accounts or for different versions on the same box. Now, if we could do it like X, where the base stuff is all in the /etc/X11R6 directory, but your own personal config lives in your home directory, then we're right as rain. but what parts of postgresql would always be common to all flavors that might need to be run at the same time? Not much. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] location of the configuration files
I don't see why we can't keep everyone happy and let the users choose the setup they want. To wit, make the following, probably simple, changes: 1) Have postgresql default to using /etc/postgresql.conf 2) Add a setting in postgresql.conf specifying the data directory 3) Change the meaning of -D to mean use this config file 4) In the absence of a specified data directory in postgresql.conf, use the location of the postgresql.conf file as the data directory I see several advantages: 1) Anyone who doesn't want to change doesn't have to - leaving the data directory spec out of postgresql.conf and starting with -D will be essentially identical to how things are now (except it would be -D /foo/bar/postgresql.conf instead of -D /foo/bar/ - even this could be overcome with a bit of bailing wire saying if -D specifies a directory, look for postgresql.conf in that directory). 2) Postgresql will be more familiar to those who expect or desire configs to be in /etc. 3) Adding a postgresql.conf line for data location sets the stage for being able to specify directories for all sorts of files (WAL, index, etc.) without the need for symlinks. 4) Multiple config files could be more easily managed for testing/benchmarking/etc. Cheers, Steve On Wednesday 12 February 2003 10:14 pm, Peter Bierman wrote: At 12:31 AM -0500 2/13/03, mlw wrote: The idea that a, more or less, arbitrary data location determines the database configuration is wrong. It should be obvious to any administrator that a configuration file location which controls the server is the right way to do it. Isn't the database data itself a rather significant portion of the 'configuration' of the database? What do you gain by having the postmaster config and the database data live in different locations? -pmb ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Changing the default configuration (was Re:
On Wed, 12 Feb 2003, Bruce Momjian wrote: And part of the reason is because some/most BSD's map the page tables into physical RAM (kernel space) rather than use some shared page table mechanism. This is good because it prevents the shared memory from being swapped out (performance disaster). Well, it'll only be swapped out if it's not being used... In any case you can use madvise() to try to avoid that, but it doesn't seem likely to be a problem since they would probably be the most heavily used pages in postgres. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Brain dump: btree collapsing
tom lane wrote: Curtis Faith [EMAIL PROTECTED] writes: I don't dispute their conclusions in that context and under the circumstances they outline of random distribution of deletion and insertion values for the index keys. [But the random-distribution assumption doesn't always hold.] That's a fair point. Nonetheless, we have little choice: we cannot move keys around during concurrent operations. If we push keys to the right, we may cause an indexscan moving left to miss them, and vice versa. So we can only eliminate empty pages. We could possibly allow VACUUM FULL to collapse partly-full pages, since in that case we know there are no concurrent scans. Couldn't we do an exclusive lock call on both leaf pages and the parent using a new LockBuffer type function, named something like LockBufferNoWait, that uses LWLockConditionalAcquire instead of LWLockAcquire, in the event that all three exclusive locks cannot be obtained release all three locks, sleep, and try again for N retries. (Actually, this would probably be four locks since the sibling pointer of one of the siblings would have to be updated to point to the new merged page instead of the to-be-deleted page.) Having exclusive locks on all three pages prior to a merge would ensure that no scans were interrupted during that merge. Releasing all the exclusive locks in the event of failure to obtain any of the locks will eliminate the possibility of creating deadlocks. After N retries, VACCUUM could abort leaving the merge to be picked up in another future VACUUM run. I would think that this would be fairly easy to implement and that except for very heavily scanned indexes, most of the time the locks could be acquired and the merge would take place without causing any concurrency problems. - Curtis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
On 13 Feb 2003, Oliver Elphick wrote: On Thu, 2003-02-13 at 18:45, Bruce Momjian wrote: Oliver Elphick wrote: On Thu, 2003-02-13 at 17:52, Vince Vielhaber wrote: Seems to me that if FHS allows such a mess, it's reason enough to avoid compliance. Either that or those of you who build for distributions are making an ill advised change. Simply because the distribution makes the decision to add PostgreSQL, or some other package, to it's distribution doesn't make it a requirement to change the location of the config files. ... I really don't see why there is such a not-invented-here mentality about this issue. I say again, standards-compliance is the best way. It makes life easier for everyone if standards are followed. Don't we pride ourselves on being closer to the SQL spec than other databases? Any way, if PostgreSQL stays as it is, I will continue to have to ensure that initdb creates symlinks to /etc/postgresql/, as happens now. It doesn't have anything to do with not-invented-here, which is a common refrain by people who don't like our decisions, like Why don't you use mmap()? Oh, it's because I thought of it and you didn't. Does anyone seriously believe that is the motiviation of anyone in this project! I certainly don't. My apologies. I withdraw the comment, which was provoked mostly by Vince's response, quoted above. I agree that it is not characteristic of the project. I certainly wasn't trying to provoke anything. It just seems odd to me that when the distribution installs a package and places it's config files in /etc and later the admin happens to upgrade by the instructions with the package, it's acceptable for the config files to now be in two places and you don't find it confusing. What happens when a new admin comes on and tries to figure out which config file is which? Ever try to figure out where the hell Pine's config really is? Vince. -- Fast, inexpensive internet service 56k and beyond! http://www.pop4.net/ http://www.meanstreamradio.com http://www.unknown-artists.com Internet radio: It's not file sharing, it's just radio. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] location of the configuration files
On Thu, 13 Feb 2003, mlw wrote: Christopher Browne wrote: In the last exciting episode, [EMAIL PROTECTED] (Curt Sampson) wrote: On Wed, 12 Feb 2003, Peter Bierman wrote: What do you gain by having the postmaster config and the database data live in different locations? You can then standardize a location for the configuration files. Everybody has room in /etc for another 10K of data. Where you have room for something that might potentially be a half terrabyte of data, and is not infrequently several gigabytes or more, is pretty system-depenendent. Ah, but this has two notable problems: 1. It assumes that there is a location for the configuration files for /the single database instance./ If I have a second database instance, that may conflict. 2. It assumes I have write access to /etc If I'm a Plain Old User, as opposed to root, I may only have read-only access to /etc. These conditions have both been known to occur... These are not issues at all. You could put the configuration file anywhere, just as you can for any UNIX service. postmaster --config=/home/myhome/mydb.conf I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system. What's really annoying is when you have to find the data directory when someone else set up the system. Really? I would think it's easier to do this: su - pgsuper cd $PGDATA pwd Than to try to figure out what someone entered when they ran ./configure --config=... Configuring postgresql via a configuration file which specifies all the data, i.e. data directory, name of other configuration files, etc. is the right way to do it. Even if you have reasons against it, even if you think it is a bad idea, a bad standard is almost always a better solution than an arcane work of perfection. Wrong, I strongly disagree with this sentament. Conformity to standards for simple conformity's sake is as wrong as sticking to the old way because it's what we're all comfy with. Personally, however, I think the configuration issue is a no-brainer and I am amazed that people are balking. EVERY other service on a UNIX box is configured in this way, why not do it this way in PostgreSQL? The patch I submitted allowed the configuration to work as it currently does, but allowed for the more standard configuration file methodology. If I do a .tar.gz install of apache, I get /usr/local/apache/conf, which is not the standard way you're listing. If I install openldap from .tar.gz, I get a /usr/local/etc/openldap directory, close, but still not the same. The fact is, it's the packagers that put things into /etc and whatnot, and I can see the postgresql RPMs or debs or whatever having that as the default, but for custom built software, NOTHING that I know of builds from source and uses /etc without a switch to tell it to, just like postgresql can do now. I just don't understand what the resistance is, it makes no sense. I agree, but from the other side of the fence. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] location of the configuration files
scott.marlowe wrote: These are not issues at all. You could put the configuration file anywhere, just as you can for any UNIX service. postmaster --config=/home/myhome/mydb.conf I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system. What's really annoying is when you have to find the data directory when someone else set up the system. Really? I would think it's easier to do this: su - pgsuper cd $PGDATA pwd Than to try to figure out what someone entered when they ran ./configure --config=... Why do you think PGDATA would be set for root? Configuring postgresql via a configuration file which specifies all the data, i.e. data directory, name of other configuration files, etc. is the right way to do it. Even if you have reasons against it, even if you think it is a bad idea, a bad standard is almost always a better solution than an arcane work of perfection. Wrong, I strongly disagree with this sentament. Conformity to standards for simple conformity's sake is as wrong as sticking to the old way because it's what we're all comfy with. It isn't conformity for conformitys sake. It is following an established practice, like driving on the same side of the road or stopping at red lights. Personally, however, I think the configuration issue is a no-brainer and I am amazed that people are balking. EVERY other service on a UNIX box is configured in this way, why not do it this way in PostgreSQL? The patch I submitted allowed the configuration to work as it currently does, but allowed for the more standard configuration file methodology. If I do a .tar.gz install of apache, I get /usr/local/apache/conf, which is not the standard way you're listing. If I install openldap from .tar.gz, I get a /usr/local/etc/openldap directory, close, but still not the same. The fact is, it's the packagers that put things into /etc and whatnot, and I can see the postgresql RPMs or debs or whatever having that as the default, but for custom built software, NOTHING that I know of builds from source and uses /etc without a switch to tell it to, just like postgresql can do now. You are confusing the default location of a file with the ability to use the file. The default I have proposed all along was to use the existing practice of keeping everything in the $PGDATA directory. The change I wish to make to the code allows this to be changed. Most admins want configuration and data separate. Most admins do not want to use symlinks because they are dangerous in a production environment. I would rather have a simpler solution sooner than a perfect solution never.
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 15:08, mlw wrote: Stephan Szabo wrote: Re-read my statement and yours about the case you were mentioning. ;) Sure, putting the files in /etc lets you find them easily. However, if you're doing things like finding configuration made by someone else and said configuration isn't in /etc (which if they wanted to they could do now with symlinks I believe - yes symlinks aren't a complete solution, but I think they're reasonable on most of our current ports) then you still have to search the system for the configuration file, except now it might not even be postgresql.conf. That's why I said the two issues aren't the same. Actually, I'd almost go so far as to say it will make it worse. In the current system, if you can figure out where $PGDATA is, you've found everything you need for that installation. In the new system, there's no telling where people will put things, and it certainly won't be any easier to find it. THinking on the above Stephan, you'd almost have to require that the config file be called postgresql.conf in order to run, anything else leads to real scary scenario's. On Thu, 13 Feb 2003, mlw wrote: I have absolutely no problem debating and augmenting the feature. None what so ever, I am more pushing to get momentum to actually do it. Stick with it, I think most of us here can see the value in the option, but there are valid concerns that it be implemented correctly. Personally I think a postgresql installation is much more like an apache installation, which generally contains all of the files (data and config) under /usr/local/apache. Maybe someone can dig more to see if that system is more appropriate a comparison than something like bind. Robert Treat ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] plpython attypmod broken in MODIFY trigger returns
Bradley McLean [EMAIL PROTECTED] writes: Here's a patch that fixes my issue. I have not extensively developed tests for it. Patch applied, thanks! regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] java access to the backend (non jdbc)?
Hi All, I was wondering if there is a libq or libqxx type library written in java. Even if it is done through jni. I would like to do some experiments, perhaps make a utility that reads server information such as currently executing query, current locks, amount of disk/ram taken up by pgsql. Basically some very useful information for admins. I've forgotten most of C, C++ (from college days), but would love to try my hand at such a utility...I'm sure there are others who would like to use java to access such a backend. Any pointers? Shahbaz C. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
On Thu, Feb 13, 2003 at 05:59:17PM -0500, Robert Treat wrote: On Thu, 2003-02-13 at 15:08, mlw wrote: Stephan Szabo wrote: On Thu, 13 Feb 2003, mlw wrote: I have absolutely no problem debating and augmenting the feature. None what so ever, I am more pushing to get momentum to actually do it. Stick with it, I think most of us here can see the value in the option, but there are valid concerns that it be implemented correctly. Personally I think a postgresql installation is much more like an apache installation, which generally contains all of the files (data and config) under /usr/local/apache. Maybe someone can dig more to see if that system is more appropriate a comparison than something like bind. I think you are making a pretty uninformed, if not just plain wrong generalization. I've run exactly one system with apache configuration files in /usr/local/apache, and even then, the data was not there. A quick straw poll of the people I know who actually do run real systems also mentioned that they use packaging systems like encap or rpm to manage upgrades, and would almost never put datafiles into /usr/local. RedHat (7.3 at least)'s default httpd datafiles go in /var/www/html and config goes in /etc/httpd One OpenBSD user I talked to puts his in /home/www and config files in /etc/httpd. The defaults are /var/www and /var/www/conf Another user reports: On systems that I set up I have /web/{apache|httpd}/ and put all the config info there. And /web/sites/name/ holds site data. What does this mean? People will put things in different places, and there are typically very good reasons for this. This is ESPECIALLY true when one wants to have configuration files, at least the base ones in a common place such as /etc or /usr/local/etc in order to make backup of configuration easy and clean, while leaving data somewhere else for performance or magnitude of partition reasons. It just makes sense to ME to have postgresql.conf reside in /etc, yet put my data in /var/data/postgresql, yet retain the option to put my data in /raid/data/postgresql at a later date, when the new hardware comes in. Yes, symlinks are an option on most systems. No, they are not a good one on most systems. What _I_ would like to see: o. a default postgresql.conf location of $PREFIX/data/postgresql.conf o. a default PGDATA location of whatever directory postgresql.conf is in (this should maintain backward compatibility) o. a ./configure - time option to override the location of the postgresql.conf o. a run-time option to override the location of the postgresql.conf o. options in postgresql.conf to specify the location of PGDATA and PID files. ($PREFIX is already settable at ./configure - time) This would allow: o. Config files in /usr/local/pgsql/data, /etc, /usr/local/etc, ~postgresql, or /dev/.hidden-node, whichever you prefer, so long as you either know the compile-time default, or are willing to specify it at startup. o. Datafiles to be in /usr/local/pgsql/data, /var/data, /raid0, /nfs/bigmount or whichever you prefer, so long as you either know the compile-time default, or are willing to specify it in a config file that you specify at startup. Does it add complexity to the system? Sure -- a very little bit, IMHO, especially compared to the BTREE-folding that I see being bantered about. Is it some work? Sure -- a very little bit, and it seems that it has already been done. However, this seems, to me, to be a very small addition that has some real-world (and yes, we need to start paying attention to the real world) advantages. And finally, don't go telling me that I'm wrong to put my data and config files where I am. You can offer advice, but I'm probably going to ignore it because I like where they are and don't need to explain why. -- Adam Haberlach | Because manholes are round. [EMAIL PROTECTED] | http://mediariffic.com | ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PostgreSQL Tuning Results
(B (BActually, the results are completely expected once you know what's (Bexactly is going on. I found it weird that my sorts were also slowing (Bdown with more sort memory until Tom or Bruce or someone pointed out to (Bme that my stats said my sorts were swapping. (B (B (B (Bthis way my first expectation but since the machine was newly booted and (Bhad 1/2 gig of ram (nothing running but PostgreSQL) I couldn't believe (Bin that theory ... (BMaybe but I couldn't verify that ... (BOf course swapping is worse than anything else. (B (BThis strikes me, too, as another area where mmap might allow the system (Bto do a better job with less tuning. Basically, the sort is getting (Bsplit into a bunch of smaller chunks, each of which is individually (Bsorted, and then you merge at the end, right? So if all those individual (Bchunks were mmaped, the system could deal with paging them out if and (Bwhen necessary, and for the sorts you do before the merge, you could (Bmlock() the area that you're currently sorting to make sure that it (Bdoesn't thrash. (B (B (BAs far as I have seen in the source code they use Knuth's tape (Balgorithm. It is based on dividing, sorting, and merging together. (B (BIf the VM system accepts hints, you might also get some further (Boptimizations because you can tell it (using madvise()) when you're (Bdoing random versus sequential access on a chunk of memory. (B (Bcj (B (B (Bit is an interesting topic. the result of the benchmark is very clock (Bspeed depedent (at least in case my of my data structure). (B (BHans (B (B-- (BCybertec Geschwinde . Schoenig (BLudo-Hartmannplatz 1/14; A-1160 Wien (BTel.: +43/1/913 68 09 oder +43/664/233 90 75 (BURL: www.postgresql.at, www.cybertec.at, www.python.co.at, www.openldap.at (B (B (B (B---(end of broadcast)--- (BTIP 3: if posting/reading through Usenet, please send an appropriate (Bsubscribe-nomail command to [EMAIL PROTECTED] so that your (Bmessage can get through to the mailing list cleanly
Re: [HACKERS] [INTERFACES] postgresql 7.3 versus 7.2
| Create a schema only dump with the 7.2 pg_dump. | Create a data only dump (possibly with INSERT output) with the 7.3 | pg_dump. Assuming you haven't done anything too wacky, you should be | able to drop your 7.2 database, reload the 7.2 schema, then load up the | 7.3 data. As always, test this out before doing it on a production | system. | Thanks a lot for this quick explanation, this is very helpful! By the way, is there a possibility to just do incremental dumps/restores? Say, since a given date? Ulf ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [INTERFACES] postgresql 7.3 versus 7.2
Is there any way to adjust dumps from postgresql 7.3 for use by version 7.2 and vice versa ? Or is there a way to transport a full database via csv or in any other kind to 7.2 and vice versa ? We are running a couple of servers/mirrors at various locations in the world, handling data based on postgresql, and now we have trouble to transport data from and to sites with different versions. (E.g. http://genealogy.math.ndsu.nodak.edu/ http://genealogy.mathematik.uni-bielefeld.de/ ) Some have upgraded and others cannot immediately follow, since an upgrade from 7.2 to 7.3 seems to require an upgrade of php4 and maybe even of apache, which takes time etc. So it would be helpful to have an easy tool to convert between data for both versions. Any help or recommendation is very much appreciated! I live with PostgreSQL from 6.5 and for independance every my application write in such a manner: database description is written using m4 macros. short example (qbqe - quote open and quote close - they are unprintable): CREATE_SECTIONqbqednl CREATE_TABLE(istat_d, rs, normal) ( date date NOT NULL , wclass int NOT NULL , count int8 NOT NULL ) INHERITS(ifaces) ; TUNE_SECTIONqbqednl CREATE UNIQUE INDEX istat_d_idx ON istat_d(router, iface, date, wclass); ALTER TABLE istat_d ADD FOREIGN KEY(router) REFERENCES router; ALTER TABLE istat_d ADD FOREIGN KEY(router, iface) REFERENCES iface; COMMENT ON TABLE istat_d IS '? ? ???'; COMMENT ON COLUMN istat_d.date IS '? ??'; END_SECTIONqbqednl m4 creates different files: for shema creation: CREATE TABLE istat_d ( date date NOT NULL , wclass int NOT NULL , count int8 NOT NULL ) INHERITS(ifaces) ; for content restore (:1 - directory): \set istat_d '\\copy istat_d FROM \'' :1 '/istat_d\'' :istat_d for last pass (I call it 'tune'): GRANT SELECT ON istat_d TO ispdb; GRANT INSERT ON istat_d TO ispdb; GRANT SELECT ON istat_d TO GROUP ispdbuser; CREATE UNIQUE INDEX istat_d_idx ON istat_d(router, iface, date, wclass); ALTER TABLE istat_d ADD FOREIGN KEY(router) REFERENCES router; ALTER TABLE istat_d ADD FOREIGN KEY(router, iface) REFERENCES iface; COMMENT ON TABLE istat_d IS '? ? ???'; COMMENT ON COLUMN istat_d.date IS '? ??'; for content dump: \set istat_d '\\copy istat_d TO \'' :1 '/istat_d\'' :istat_d for shema deletion: DROP TABLE istat_d; for empty database initialisation - void in this example. '?' in example are symbols of my language. So every database replication is: - content dump (not PostgreSQL dump) - shema creation - content restore - tune pass This unlock me from PostgreSQL version and ease me to upgrade application version (with possible 'up' pass in addition) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Do we always need the socket file?
I have Postgres 7.2.1 configured to listen on TCP/IP port. When the server starts up it still creates the socket file in /tmp. Removing this file manually does not seem to cause any problem for the application. Is there a way to prevent postmaster from creating this file? Is this really safe to remove the socket file, or would it create some problem that I won't necessarily see? Thanks, Mike. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Location of the configuration files, round 2
Wow, there's been a lot of discussion on this issue! While it won't address some of the issues that have been brought up, there is one very simple thing we can do that will help sysadmins quite a lot: eliminate the postmaster's use of $PGDATA, and force the data directory to be specified on the command line. It's fine if the shell scripts still use $PGDATA, but the postmaster should not. The reason is that at least it'll always be possible for administrators to figure out where the data is by looking at the output of 'ps'. While I'd prefer to also see a GUC variable added to the config file that tells the postmaster where to look for the data, the above will at least simplify the postmaster's code (since the logic for dealing with $PGDATA can be eliminated) while eliminating some of the trouble administrators currently have with it. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Version 7.2.3 Vacuum abnormality
Good evening, tonight while running my routine vacuum, the following came up on my screen: ---8--- NOTICE: Rel xamefiles: Uninitialized page 708135 - fixing NOTICE: Rel xamefiles: Uninitialized page 708136 - fixing NOTICE: Rel xamefiles: Uninitialized page 708137 - fixing NOTICE: Rel xamefiles: Uninitialized page 708138 - fixing NOTICE: Rel xamefiles: Uninitialized page 708599 - fixing ---8--- There were a lot more than this, several hundered. Here's the general details of the DB: - Approximately 30,000,000 rows - No triggers - Table consists strictly of text, integers and one key ( SERIAL ) - PostgreSQL 7.2.3 ( yes, i'll update to 7.2.4 in the next day or so ) - Pentium III 1.2Ghz with 1Gb RAM running RedHat 8.0 ( Not my machine! ) Here's the lead up to events: - Two days ago I DELETE'd approximately 7 million rows - I proceeded to vacuum, but it was 'terminated' by another admin approximately 12 hours later - I restarted the vacuum, which resulted in the following stats: NOTICE: Pages 701193: Changed 8459, Empty 0; Tup 21042082: Vac 0, Keep 0, UnUsed 32056923 - I ran the DELETE script again which purged another 200,000 rows ( approx ) - I proceeded to vacuum, the table in question returned the following stats: NOTICE: Pages 704754: Changed 9599, Empty 0; Tup 14385034: Vac 0, Keep 207650, UnUsed 38884420. - Today I dropped an index off the table, ran the DELETE again, removing 457,636 rows - Vacuum dumped the above NOTICES. Final output for the table is: NOTICE: Index xamefiles_k_key: Pages 187175; Tuples 14521716: Deleted 666334. CPU 8.92s/14.11u sec elapsed 1306.20 sec. - The vacuum is still going along fine. Backups are proceeding without incident ( using pg_dump ) DELETE's are proceeding without incident Hope this is enough information. Kind Regards. -- Paul L Danielshttp://www.pldaniels.com Linux/Unix systemsInternet Development ICQ#103642862,AOL:cinflex,IRC:inflex A.B.N. 19 500 721 806 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
Bruce Momjian wrote: I don't think separate params for each config file is good. At the most, I think we will specify the configuration _directory_ for all the config files, perhaps pgsql/etc, and have pgdata default to ../data, or honor $PGDATA. That might be the cleanest. Of course, that now gives us $PGCONFIG and $PGDATA, and possible intraction if postgresql.conf specifies a different pgdata from $PGDATA. As you can see, it could get messy. Uh...why are we having to mess with environment variables at all? It's one thing for shell scripts to make use of them, but another thing entirely for an executable like the postmaster to do the same. Seems logical to me to eliminate the use of $PGDATA in the postmaster entirely. It usually gets started from a shell script, so let the shell script pass the appropriate parameter telling the postmaster where to find the data, or the config files, or whatever. And, if you specify pgdata in postgresql.conf, it prevents you from using that file by different postmasters. Not at all. Don't GUC variables that are specified on the command line override the ones in the configuration file? My best guess would be to not specify pgdata in postgresql.conf, and have a new $PGCONFIG param to specify the configuration directory, but if we do that, $PGDATA/postgresql.conf becomes meaningless, which could also be confusing. Maybe we don't allow those files to exist in $PGDATA if $PGCONFIG is used, _and_ $PGCONFIG is not the same as $PGDATA. See, I am getting myself confused. :-) I think the solution is real simple: 1. Eliminate the use of $PGDATA in the postmaster. It causes far more headaches than it's worth. Instead, require that -D be passed on the command line. It's fine if the postmaster *sets* $PGDATA in order to minimize any changes that need to be made elsewhere, but the postmaster should not use it until it sets it. The postmaster right now reads all the config files (including postgresql.conf) from the directory specified by the -D option. Keep it that way. 2. Add a GUC variable that specifies where the data is. If this variable is not defined either on the command line or in the config file, then assume that the data is in the same place as the config file. Obviously files like PG_VERSION are associated with the data and not with the config, so they get treated appropriately. The above addresses *everyone's* concerns that I've seen thus far, I think. Thoughts? -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] location of the configuration files
Vince Vielhaber wrote: On Thu, 13 Feb 2003, Lamar Owen wrote: On Thursday 13 February 2003 18:07, Vince Vielhaber wrote: Actually FHS says the opposite. If the distribution installs PostgreSQL then the config files belong in /etc/postgresql. If the admin does then they belong in /usr/local/etc/postgresql. FHS is out of their tree. If PostgreSQL or any other package is not critical to the basic operation of the operating system, it's config files shouldn't be polluting /etc. PostgreSQL is as critical as PHP, Apache, or whatever other package is being backended by PostgreSQL. If the package is provided by the distributor, consider it part of the OS. If it isn't, well, it isn't. You completely miss my point, but lately you've been real good at that. Can the system boot without PHP, Apache, PostgreSQL, Mysql and/or Pine? Yep. Can the root user log in without PHP, Apache, PostgreSQL, Mysql and/or Pine? Hopefully. Can any user log in without PHP, Apache, PostgreSQL, Mysql and/or Pine? That depends, doesn't it? There exist PAM modules that allow authentication against a database, for instance. If you're using them and the database doesn't come up, the users can't log in. So suddenly the database config files belong in /etc? The mission of the box is what counts. If the mission of the box is to be a web server then I'm probably not going to care whether non-root users can log into it: that simply doesn't factor into the mission profile. The web server process is going to be as critical to the mission of the box as almost anything else on it, as will anything the web server process depends on -- which may well include a database. Note, I'm not even including an MTA here. I said BASIC OPERATION. So by your reasoning sendmail.cf doesn't belong in /etc?? I dare say that's news to most of us. Where, then, *does* it belong? If a package is not critical as I just outlined, it shouldn't matter who installed it. Oh, it matters a great deal, because people upgrade their OS installs from time to time. Many OS distributions come with a lot of packages that aren't critical as you define them but which nevertheless will cause much pain and suffering for the sysadmin if they install themselves over what the sysadmin has previously built by hand. The purpose for differentiating between a package that was compiled and installed from the source by the sysadmin and a prebuilt package that was provided to the sysadmin by the vendor is to keep them from stepping on each other -- if the sysadmin went to the trouble of compiling and installing a package from the source instead of using a prebuilt version from the vendor, then he probably did so for a very good reason, and is going to be *really* annoyed if an OS upgrade blows away his work. There are some good reasons for putting all the config files in /etc, one of them being that it gives you *one* directory full of config files to worry about backing up instead of many. If you've got other ideas I'm certainly interested in hearing the reasoning behind them. But from the point of view of maintaining a widely deployed package like PostgreSQL, the conventions the distributions and sysadmins use matter a great deal, whether or not you happen to agree with those conventions. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] More benchmarking of wal_buffers
I don't think this is based on a useful test for wal_buffers. The wal_buffers setting only has to be large enough for the maximum amount of WAL log data that your system emits between commits, because a commit (from anyone) is going to flush the WAL data to disk (for everyone). So a benchmark based on short transactions is just not going to show any benefit to increasing the setting. Yes, I guess the TPC-B test does many, very short transactions. Each transaction bascially comprises a single update, so I guess it wouldn't really test it. One proof that has come out of this is that wal_buffers does not affect SELECT only performance in any way. Coulda told you that without testing ;-). Read-only transactions emit no WAL entries. I knew that as well, that's why I said proof ;) Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] More benchmarking of wal_buffers
I don't think this is based on a useful test for wal_buffers. The wal_buffers setting only has to be large enough for the maximum amount of WAL log data that your system emits between commits, because a commit (from anyone) is going to flush the WAL data to disk (for everyone). So a benchmark based on short transactions is just not going to show any benefit to increasing the setting. Here's a question then - what is the _drawback_ to having 1024 wal_buffers as opposed to 8? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [HACKERS] More benchmarking of wal_buffers
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Here's a question then - what is the _drawback_ to having 1024 wal_buffers as opposed to 8? Waste of RAM? You'd be better off leaving that 8 meg available for use as general-purpose buffers ... What I mean is say you have an enterprise server doing heaps of transactions with lots of work. If you have scads of RAM, could you just shove up wal_buffers really high and assume it will improve performance? Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Changing the default configuration (was Re: [pgsql-advocacy]
Josh Berkus wrote: Uh ... do we have a basis for recommending any particular sets of parameters for these different scenarios? This could be a good idea in the abstract, but I'm not sure I know enough to fill in the details. Sure. Mostly-Read database, few users, good hardware, complex queries: = High shared buffers and sort mem, high geqo and join collapse thresholds, moderate fsm settings, defaults for WAL. Same as above with many users and simple queries (webserver) = same as above, except lower sort mem and higher connection limit High-Transaction Database = Moderate shared buffers and sort mem, high FSM settings, increase WAL files and buffers. Workstation = Moderate to low shared buffers and sort mem, moderate FSM, defaults for WAL, etc. Low-Impact server = current defaults, more or less. Okay, but there should probably be one more, called Benchmark. The real problem is what values to use for it. :-) -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] More benchmarking of wal_buffers
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Here's a question then - what is the _drawback_ to having 1024 wal_buffers as opposed to 8? Waste of RAM? You'd be better off leaving that 8 meg available for use as general-purpose buffers ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [HACKERS] More benchmarking of wal_buffers
Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: I've just spent the last day and a half trying to benchmark our new database installation to find a good value for wal_buffers. The quick answer - there isn't, just leave it on the default of 8. I don't think this is based on a useful test for wal_buffers. The wal_buffers setting only has to be large enough for the maximum amount of WAL log data that your system emits between commits, because a commit (from anyone) is going to flush the WAL data to disk (for everyone). What happens when the only transaction running emits more WAL log data than wal_buffers can handle? A flush happens when the WAL buffers fill up (that's what I'd expect)? Didn't find much in the documentation about it... -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] [HACKERS] More benchmarking of wal_buffers
Kevin Brown [EMAIL PROTECTED] writes: What happens when the only transaction running emits more WAL log data than wal_buffers can handle? A flush happens when the WAL buffers fill up (that's what I'd expect)? Didn't find much in the documentation about it... A write, not a flush (ie, we don't force an fsync). Also, I think it writes only a few blocks, not all the available data. Don't recall the details on that. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [HACKERS] More benchmarking of wal_buffers
Christopher Kings-Lynne [EMAIL PROTECTED] writes: What I mean is say you have an enterprise server doing heaps of transactions with lots of work. If you have scads of RAM, could you just shove up wal_buffers really high and assume it will improve performance? There is no such thing as infinite RAM (or if there is, you paid *way* too much for your database server). My feeling is that it's a bad idea to put more than you absolutely have to into single-use buffers. Multi-purpose buffers are usually a better use of RAM. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PG_TEMP_FILES_DIR
Quick question for the group I'm assuming that the PG_TEMP_FILES_DIR for BufFile temps is automatically under the PGDATA directory. Is that correct ? -- Pip-pip Sailesh http://www.cs.berkeley.edu/~sailesh ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Changing the default configuration (was Re: [pgsql-advocacy]
Bruce Momjian wrote: We could prevent the postmaster from starting unless they run pg_tune or if they have modified postgresql.conf from the default. Of course, that's pretty drastic. If you're going to do that, then you may as well make the defaults something that will perform reasonably well under the widest circumstances possible and let the postmaster fail when it can't acquire the resources those defaults demand. What I'd do is go ahead and make the defaults something reasonable, and if the postmaster can't allocate, say, enough shared memory pages, then it should issue an error message saying not only that it wasn't able to allocate enough shared memory, but also which parameter to change and (if it's not too much trouble to implement) what it can be changed to in order to get past that part of the initialization (this means that the postmaster has to figure out how much shared memory it can actually allocate, via a binary search allocate/free method). It should also warn that by lowering the value, the resulting performance may be much less than satisfactory, and that the alternative (to increase SHMMAX, in this example) should be used if good performance is desired. That way, someone whose only concern is to make it work will be able to do so without having to do a lot of experimentation, and will get plenty of warning that the result isn't likely to work very well. And we end up getting better benchmarks in the cases where people don't have to touch the default config. :-) -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] location of the configuration files
Tom Lane wrote: I don't see any great value in a separate postgresql.conf parameter for each secondary config file; that just means clutter to me, especially if we add more such files in future. I am also distinctly not in favor of eliminating the PGDATA environment variable; that reads to me as we are going to force you to do it our way rather than the way you've always done it, even if you like the old way. The scripts needn't ignore PGDATA at all. Only postmaster. Since the vast majority of people start the postmaster from a script, this winds up being a minor issue, except for the fact that without PGDATA administrators will be able to count on looking at the output of 'ps' to determine where the postmaster is looking for either the config file or the data directory. In other words, they'll have somewhere to start from without having to poke through scripts that might not even have been used (what happens when a user defines PGDATA and starts a postmaster? The administrator will have to go to more extreme lengths, like using lsof, to figure out where the data directory is. Not all systems have such tools). Comments? I agree with your assessment for the most part, except for PGDATA. There's no good reason I can think of for the postmaster to look at it. It's fine if it sets it for processes it forks to inherit, but it shouldn't pay attention to it on startup. Some people might complain, but there's little difference in doing a postmaster -D $PGDATA and just postmaster, and people who are starting things by hand hopefully aren't so inflexible as to demand that PGDATA remain treated as-is. People who really care can create a simple little 'pm.sh' script that simply does a postmaster -D $PGDATA, which will save them typing even over just doing a postmaster from the command line. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Offering tuned config files
OK, Here's a stab at some extra conf files. Feel free to shoot them down. If we can come up with at least _some_ alternative files that we can put somewhere for them to see when postgres is installed, then at least people can see what variables will affect what... I didn't see the point of a 'workstation' option, the default is fine for that. Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Kevin Brown Sent: Friday, 14 February 2003 11:26 AM To: PostgresSQL Hackers Mailing List; [EMAIL PROTECTED] Subject: Re: [HACKERS] Changing the default configuration (was Re: [pgsql-advocacy] Josh Berkus wrote: Uh ... do we have a basis for recommending any particular sets of parameters for these different scenarios? This could be a good idea in the abstract, but I'm not sure I know enough to fill in the details. Sure. Mostly-Read database, few users, good hardware, complex queries: = High shared buffers and sort mem, high geqo and join collapse thresholds, moderate fsm settings, defaults for WAL. Same as above with many users and simple queries (webserver) = same as above, except lower sort mem and higher connection limit High-Transaction Database = Moderate shared buffers and sort mem, high FSM settings, increase WAL files and buffers. Workstation = Moderate to low shared buffers and sort mem, moderate FSM, defaults for WAL, etc. Low-Impact server = current defaults, more or less. Okay, but there should probably be one more, called Benchmark. The real problem is what values to use for it. :-) -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] postgresql.conf.sample-olap Description: Binary data postgresql.conf.sample-web Description: Binary data postgresql.conf.sample-writeheavy Description: Binary data ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] location of the configuration files
Kevin Brown [EMAIL PROTECTED] writes: I agree with your assessment for the most part, except for PGDATA. There's no good reason I can think of for the postmaster to look at it. The other side of that coin is, what's the good reason to remove it? There's a long way between I don't want my setup to depend on PGDATA and I don't think your setup should be allowed to depend on PGDATA. If you don't want to use it, then don't use it. Why do you need to tell me how I'm allowed to run my installation? ... people who are starting things by hand hopefully aren't so inflexible as to demand that PGDATA remain treated as-is. Yes, I could reconfigure my scripts to not depend on this. You have not given me an adequate argument why I should have to. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] location of the configuration files
Apache explicitly supports a number of different layouts for files out of the box (and provides support for you to roll your own very easily). From the manual: The second, and more flexible way to configure the install path locations for Apache is using the config.layout file. Using this method, it is possible to separately specify the location for each type of file within the Apache installation. The config.layout file contains several example configurations, and you can also create your own custom configuration following the examples. The different layouts in this file are grouped into Layout FOO.../Layout sections and referred to by name as in FOO. --enable-layout=LAYOUT Use the named layout in the config.layout file to specify the installation paths. Maybe pg could benefit from something similar? cheers andrew - Original Message - From: scott.marlowe [EMAIL PROTECTED] Sent: Thursday, February 13, 2003 4:07 PM [snip] If I do a .tar.gz install of apache, I get /usr/local/apache/conf, which is not the standard way you're listing. If I install openldap from .tar.gz, I get a /usr/local/etc/openldap directory, close, but still not the same. The fact is, it's the packagers that put things into /etc and whatnot, and I can see the postgresql RPMs or debs or whatever having that as the default, but for custom built software, NOTHING that I know of builds from source and uses /etc without a switch to tell it to, just like postgresql can do now. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] location of the configuration files
On Thursday 13 February 2003 21:13, Bruce Momjian wrote: Lamar Owen wrote: It isn't without precedent to have a directory under /var/run. Maybe /var/run/postgresql. Under this one could have a uniquely named pid file. But how do you handle the default then, where you have postmaster.pid in /data? Do we rename it to postmaster.pid.5432 so it can sit in /var/run/postgresql alone with other backends? Well, you can have the default as 'postmaster.pid' if it wasn't named. But more thought is needed. I'll have to admit; the wisdom of AOLserver having a full-fledged tcl config script is beginning to look better and better. Another issue is that pg_ctl looks at that file, so moving it around is going to be tricky. pg_ctl could be interesting. I am now wondering if we even want pg_hba_dir and pg_ident_dir. Seems we can assume they are in the same directory as postgresql.conf. That leaves only data_dir as new for postgresql.conf. Ok, if we're going this far already, tell me exactly why we have three config files. Why not really Unify things and fulfil the full promise of Grand Unified Configuration by rolling hba and ident into postgresql.conf. Is there a compelling reason not to do so? The structure of that configuration data would have to change, for sure. Although I seem to remember this being suggested once before, but my mind draws a blank trying to recall it. Just a suggestion; maybe not even a good one, but something that crossed my mind. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] location of the configuration files
Oliver Elphick [EMAIL PROTECTED] writes: I'm not entirely sure why SE Linux has a problem, seeing that postgres needs read-write access to all the files in $PGDATA, but assuming the need is verified, I could do this by moving the pid file from $PGDATA/postmaster.pid to /var/run/postgresql/5432.pid and similarly for other ports. This would also have the benefit of being more FHS compliant What do people think about that? No chance at all. Breaking the connection between the data directory and the postmaster.pid file means we don't have an interlock against starting two postmasters in the same data directory. I do not see the argument for moving the pid file anyway. Surely no one's going to tell us that the postmaster shouldn't have write access to the data directory? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] location of the configuration files
On Thursday 13 February 2003 21:49, Tom Lane wrote: Oliver Elphick [EMAIL PROTECTED] writes: need is verified, I could do this by moving the pid file from $PGDATA/postmaster.pid to /var/run/postgresql/5432.pid and similarly for other ports. This would also have the benefit of being more FHS compliant What do people think about that? No chance at all. Breaking the connection between the data directory and the postmaster.pid file means we don't have an interlock against starting two postmasters in the same data directory. It's not a pid file in the /var/run sense, really. It's an interlock for PGDATA. So it might be argued that postmaster.pid is misnamed. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
Lamar Owen wrote: I am now wondering if we even want pg_hba_dir and pg_ident_dir. Seems we can assume they are in the same directory as postgresql.conf. That leaves only data_dir as new for postgresql.conf. Ok, if we're going this far already, tell me exactly why we have three config files. Why not really Unify things and fulfil the full promise of Grand Unified Configuration by rolling hba and ident into postgresql.conf. Is there a compelling reason not to do so? The structure of that configuration data would have to change, for sure. Although I seem to remember this being suggested once before, but my mind draws a blank trying to recall it. Just a suggestion; maybe not even a good one, but something that crossed my mind. postgresql.conf is var=val, while the others are column-based. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] location of the configuration files
Lamar Owen [EMAIL PROTECTED] writes: Ok, if we're going this far already, tell me exactly why we have three config files. Why not really Unify things and fulfil the full promise of Grand Unified Configuration by rolling hba and ident into postgresql.conf. Is there a compelling reason not to do so? Lack of backwards compatibility; unnecessary complexity. Unifying those files would create a big headache in terms of having to unify their syntax. And there are some basic semantic differences too. For instance, order matters in pg_hba.conf, but not in postgresql.conf. Another reason not to do it is that there are differences in the security requirements of these files. postgresql.conf probably doesn't contain anything that needs to be hidden from prying eyes, but I'd be inclined to want to keep the other two mode 600. --- Okay, I've been laying low all day, but here are my thoughts on the discussion: I do see the value in being able to (as opposed to being forced to, please) keep hand-edited config files in a separate location from the machine-processed data files. We have already gone some distance in that direction over the past few releases --- there's much less in the top $PGDATA directory than there once was. It makes sense to let people keep hand-edited files away from what initdb will overwrite. I would favor a setup that allows a -C *directory* (not file) to be specified as a postmaster parameter separately from the -D directory; then the hand-editable config files would be sought in -C not -D. In the absence of -C the config files should be sought in -D, same as they ever were (thus simplifying life for people like me who run many postmasters and don't give a darn about FHS ;-)). I don't see any great value in a separate postgresql.conf parameter for each secondary config file; that just means clutter to me, especially if we add more such files in future. I am also distinctly not in favor of eliminating the PGDATA environment variable; that reads to me as we are going to force you to do it our way rather than the way you've always done it, even if you like the old way. To make the RPM packagers happy, I guess that the default -C directory has to be settable via configure. We do not currently have a default -D directory, and I didn't hear anyone arguing in favor of adding one. So that leaves the following possible combinations that the postmaster might see at startup, for which I propose the following behaviors: 1. No -C switch, no -D switch, no PGDATA found in environment: seek postgresql.conf in the default -C directory established at configure time. Use the 'datadir' specified therein as -D. Fail if postgresql.conf doesn't define a datadir value. 2. No -C switch, no -D switch, PGDATA found in environment: use $PGDATA as both -C and -D. (Minor detail: if the postgresql.conf in the $PGDATA directory specifies a different directory as datadir, do we follow that or raise an error? I'd be inclined to say follow it but maybe there is an argument for erroring out.) (In all the following cases, any environment PGDATA value is ignored.) 3. No -C switch, -D switch on command line: use -D value as both -C and -D, proceed as in case 2. 4. -C switch, no -D switch on command line: seek postgresql.conf in -C directory, use the datadir it specifies. 5. -C and -D on command line: seek postgresql.conf in -C directory, use -D as datadir overriding what is in postgresql.conf (this is just the usual rule that command line switches override postgresql.conf). Cases 2 and 3 are backwards-compatible with our historical behavior, so that anyone who likes the historical behavior will not be unhappy. Cases 1 and 4 I think will make mlw and our packagers happy. Case 5 is just the logical conclusion for that combination. In all cases, pg_hba.conf and pg_ident.conf would be sought in the same directory as postgresql.conf. The other stuff in the toplevel $PGDATA directory should stay where it is, IMHO. I would venture that the configure-time-default for -C should be ${prefixdir}/etc if configure is not told differently, while the packagers would probably set it to /etc/postgresql/ (ie, the config files should live in a subdirectory that can be owned by postgres user). I'm not wedded to that though. Another interesting question is whether the installed-by-default postgresql.conf should specify a datadir value, and if so what. If initdb installs it, it can and probably should insert the actual datadir location the user gave to initdb into the file. But should initdb install any config files at all anymore? I'm leaning to the thought that initdb should store default config files into $PGDATA same as it ever did, and then it's up to the user (or package install scripts) to move them to the desired -C directory if appropriate. Or I suppose we could add a -C parameter to initdb to tell it where to put 'em. Comments? regards, tom lane ---(end
Re: [HACKERS] Changing the default configuration
Tatsuo, Sigh. People always complain pgbench does not reliably producing repeatable numbers or something then say that's because pgbench's transaction has too much contention on the branches table. So I added -N option to pgbench which makes pgbench not to do any UPDATE to the branches table. But still people continue to complian... Hey, pg_bench is a good start on a Postgres performance tester, and it's much, much better than what there was before you came along ... which was nothing. Thank you again for contributing it. pg_bench is, however, only a start on a performance tester, and we'd need to build it up before we could use it as the basis of a PG tuner. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Configuration file patch
This patch allows using PostgreSQL with a command line configuration file parameter instead of the data directory. If no configuration is specified, postmaster operates as it always has. The configuration file is specified with the -C parameter, as: postmaster -C /somepath/somefile.conf Within the configuration file, there are three new parameters: data_dir, hba_conf, and ident_conf. They are used as: hba_conf = 'pathanme_to_pg_hba.conf' ident_conf='pathname_to_pg_ident.conf' data_dir='path_to_data' If the above parameters are not specified, then the default is to look for these file in the PGDATA directory. Command line arguments take precedent over configuration file. This patch is not a be-all end-all of configuration. It should be able to fit PostgreSQL into a FHS with the exception of the '/var/run' requirement. diff -u -r postgresql-7.3.2/src/backend/libpq/hba.c postgresql-7.3.2.ec/src/backend/libpq/hba.c --- postgresql-7.3.2/src/backend/libpq/hba.cSat Dec 14 13:49:43 2002 +++ postgresql-7.3.2.ec/src/backend/libpq/hba.c Thu Feb 13 12:15:16 2003 @@ -35,6 +35,7 @@ #include miscadmin.h #include nodes/pg_list.h #include storage/fd.h +#include utils/guc.h #define IDENT_USERNAME_MAX 512 @@ -837,10 +838,20 @@ if (hba_lines) free_lines(hba_lines); - /* Put together the full pathname to the config file. */ - bufsize = (strlen(DataDir) + strlen(CONF_FILE) + 2) * sizeof(char); - conf_file = (char *) palloc(bufsize); - snprintf(conf_file, bufsize, %s/%s, DataDir, CONF_FILE); + /* Explicit HBA in config file */ + if(explicit_hbafile strlen(explicit_hbafile)) + { + bufsize = strlen(explicit_hbafile)+1; + conf_file = (char *) palloc(bufsize); + strcpy(conf_file, explicit_hbafile); + } + else + { + /* put together the full pathname to the config file */ + bufsize = (strlen(DataDir) + strlen(CONF_FILE) + 2) * sizeof(char); + conf_file = (char *) palloc(bufsize); + snprintf(conf_file, bufsize, %s/%s, DataDir, CONF_FILE); + } file = AllocateFile(conf_file, r); if (file == NULL) @@ -979,10 +990,20 @@ if (ident_lines) free_lines(ident_lines); - /* put together the full pathname to the map file */ - bufsize = (strlen(DataDir) + strlen(USERMAP_FILE) + 2) * sizeof(char); - map_file = (char *) palloc(bufsize); - snprintf(map_file, bufsize, %s/%s, DataDir, USERMAP_FILE); + /* Explicit IDENT in config file */ + if(explicit_identfile strlen(explicit_identfile)) + { + bufsize = strlen(explicit_identfile)+1; + map_file = (char *) palloc(bufsize); + strcpy(map_file, explicit_identfile); + } + else + { + /* put together the full pathname to the map file */ + bufsize = (strlen(DataDir) + strlen(USERMAP_FILE) + 2) * sizeof(char); + map_file = (char *) palloc(bufsize); + snprintf(map_file, bufsize, %s/%s, DataDir, USERMAP_FILE); + } file = AllocateFile(map_file, r); if (file == NULL) diff -u -r postgresql-7.3.2/src/backend/postmaster/postmaster.c postgresql-7.3.2.ec/src/backend/postmaster/postmaster.c --- postgresql-7.3.2/src/backend/postmaster/postmaster.cWed Jan 15 19:27:17 2003 +++ postgresql-7.3.2.ec/src/backend/postmaster/postmaster.c Thu Feb 13 22:53:08 +2003 @@ -421,7 +421,7 @@ opterr = 1; - while ((opt = getopt(argc, argv, A:a:B:b:c:D:d:Fh:ik:lm:MN:no:p:Ss-:)) != -1) + while ((opt = getopt(argc, argv, A:a:B:b:C:c:D:d:Fh:ik:lm:MN:no:p:Ss-:)) != +-1) { switch (opt) { @@ -441,6 +441,9 @@ case 'b': /* Can no longer set the backend executable file to use. */ break; + case 'C': // MLW + explicit_pgconfig = optarg; + break; case 'D': potential_DataDir = optarg; break; @@ -564,13 +567,23 @@ ExitPostmaster(1); } - /* -* Now we can set the data directory, and then read postgresql.conf. -*/ - checkDataDir(potential_DataDir);/* issues error messages */ - SetDataDir(potential_DataDir); - - ProcessConfigFile(PGC_POSTMASTER); + if(explicit_pgconfig) + { + ProcessConfigFile(PGC_POSTMASTER); + if(!potential_DataDir pgdatadir) + potential_DataDir = pgdatadir; + checkDataDir(potential_DataDir);/* issues error messages */ + SetDataDir(potential_DataDir); + } + else + { + /* +
Re: [pgsql-advocacy] [HACKERS] Changing the default configuration
HEY PEOPLE! How about we take this discussion to the Performance List, where it belongs? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] index corruption?
Laurette Cisneros [EMAIL PROTECTED] writes: This is the error in the pgsql log: 2003-02-13 16:21:42 [8843] ERROR: Index external_signstops_pkey is not a btree This says that one of two fields that should never change, in fixed positions in the first block of a btree index, didn't have the right values. I am not aware of any PG bugs that could overwrite those fields. I think the most likely bet is that you've got hardware issues ... have you run memory and disk diagnostics lately? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 23:06, mlw wrote: Bruce Momjian wrote: Can non-root write to /var/run? Shouldn't be able too But it should be able to write under /var/run/postgresql, which the distribution will set up with the correct permissions. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C God be merciful unto us, and bless us; and cause his face to shine upon us. Psalms 67:1 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 22:53, Bruce Momjian wrote: Oliver Elphick wrote: What your comments strongly suggest to me is that projects like PostgreSQL and pine, along with everything else, should comply with FHS; then there will be no confusion because everyone will be following the smae standards. Messes arise when people ignore standards; we have all seen the dreadful examples of MySQL and the Beast, haven't we? Can the FHS handle installing PostgreSQL as non-root? Certainly. It is only necessary to set permissions correctly in /etc/postgresql and /var/run/postgresql. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C God be merciful unto us, and bless us; and cause his face to shine upon us. Psalms 67:1 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] location of the configuration files
On Fri, 2003-02-14 at 02:49, Tom Lane wrote: Oliver Elphick [EMAIL PROTECTED] writes: I'm not entirely sure why SE Linux has a problem, seeing that postgres needs read-write access to all the files in $PGDATA, but assuming the need is verified, I could do this by moving the pid file from $PGDATA/postmaster.pid to /var/run/postgresql/5432.pid and similarly for other ports. This would also have the benefit of being more FHS compliant What do people think about that? No chance at all. Breaking the connection between the data directory and the postmaster.pid file means we don't have an interlock against starting two postmasters in the same data directory. Yes; that would take a lot of effort to get round. Not worth it, I think. I do not see the argument for moving the pid file anyway. Surely no one's going to tell us that the postmaster shouldn't have write access to the data directory? I'm waiting for a response on that one; I don't understand it either. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C God be merciful unto us, and bless us; and cause his face to shine upon us. Psalms 67:1 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] location of the configuration files
On Fri, 2003-02-14 at 02:45, [EMAIL PROTECTED] wrote: 3.7.1 Purpose /etc contains configuration files and directories that are specific to the current system. 3.7.4 Indicates that Host-specific configuration files for add-on application software packages must be installed within the directory /etc/opt/package, where package is the name of the subtree in /opt where the static data from that package is stored. 3.12 indicates: /opt is reserved for the installation of add-on application software packages. A package to be installed in /opt must locate its static files in a separate /opt/package directory tree, where package is a name that describes the software package. ... It would make most sense, based on FHS, for PostgreSQL information to assortedly reside in: - /etc/opt/postgresql or /etc/postgresql, for static config information; I feel that /opt (and therefore /etc/opt) are intended for the use of vendors; so commercial packages designed to fit in with FHS should use those. I don't think they are for locally built stuff. No matter; it illustrates the main point, which is that these things should be easily configurable. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C God be merciful unto us, and bless us; and cause his face to shine upon us. Psalms 67:1 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] loading libraries on Postmaster startup
Peter Eisentraut wrote: Joe Conway writes: So I wrote a quick hack to load and initialize the library on postmaster startup. On glibc systems you can probably do this using the environment variable LD_PRELOAD. I guess others have a similar mechanism. Hmmm. I could try that. But I found during testing that the loading was actually not the slow part, it was running the initialization function for the interpreter that was. I wonder if there is there any way to get an initialization function to automatically execute? Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Brain dump: btree collapsing
Tom Lane kirjutas N, 13.02.2003 kell 20:10: Curtis Faith [EMAIL PROTECTED] writes: I don't dispute their conclusions in that context and under the circumstances they outline of random distribution of deletion and insertion values for the index keys. [But the random-distribution assumption doesn't always hold.] That's a fair point. Nonetheless, we have little choice: we cannot move keys around during concurrent operations. If we push keys to the right, we may cause an indexscan moving left to miss them, and vice versa. So we can only eliminate empty pages. But if we would allow the scans to find the same keys twice without ill effects (as was suggested earlier, for using btrees to index arrays), then we could possibly 1) copy the key to the right 2) wait for all right-to-left scans that have fallen between old and new values to pass and only then 3) delete the old left key. This could solve the concurrency issue as well. We could possibly allow VACUUM FULL to collapse partly-full pages, since in that case we know there are no concurrent scans. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] location of the configuration files
All I see here is an arbitrary break with our past practice. I do not see any net improvement. FreeBSD Port Maintainer Well, given that there's a trend to make PostgreSQL more usable, I can say with absolute certainty, that an FAQ that I get about once a week is (and granted only from new users) where is the postgresql.conf? I don't see it in ${LOCALBASE}/etc/. PostgreSQL is one of a few ports in an extreme minority that uses a local configuration directive and it violates the policy of least surprise for sysadmins. PS LOCALBASE/PREFIX is /usr/local 99.999% of the time /FreeBSD Port Maintainer With my DBA hat on, however, here are a few reasons that I'd like to see the conf moved out of the data directory: 1) pg_dumpall foo rm -rf $PGDATA initdb As a DBA I don't have to worry about backing up my config file when doing upgrades since the config file is located in an external directory. 2) Backing up config files in ${LOCALBASE}/etc is a pretty common practice. Having to make a special case for postgresql's kind of a PITA. Suggestions: 1) gmake install installs a default configuration file in ${LOCALBASE}/etc/postgresql.conf.default. Promote that DBAs should diff postgresql.conf.default with postgresql.conf and make adjustments as they see fit, but gmake install will _not_, under any circumstances, touch postgresql.conf (by default, it should cp postgresql.conf.default to postgresql.conf that way things just work out of the box). 2) Leave the current functionality in place. Being able to have multiple databases on the same machine is a _really_ nice feature of PostgreSQL. If you want multiple databases, having the config file in $PGDATA makes some sense because with multiple installations, you want to keep everything together... though it doesn't make much sense if you have only one installation per server... and really, the only reason to have multiple installations is to handle username collisions (hint hint). 3) In the absence of a PGDATA environment variable (don't want to break backward compatible installations) being set, the future behavior allow for a default location of a config file (if no CLI switch is specified for an explicit location) that points to a config file. The path would be ${PREFIX}/etc and would provide most admins with a standard launching off point for running/tuning their databases. The config file would have to specify the data directory as well as the path to the hba.conf, which should be outside of the datadir as well (speaking of the hba.conf, am I the only one who things that hba.conf should be converted into a system catalog? ::shrug::) Just some random thoughts from someone who's had to deal with this on all of the mentioned levels (new users, single installations, multiple installations, and multiple copies running via daemontools). -sc PS If there is no huge press for this, I should have the time do do this in a few weeks if someone doesn't beat me to it. -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 21:21, Vince Vielhaber wrote: I certainly wasn't trying to provoke anything. It just seems odd to me that when the distribution installs a package and places it's config files in /etc and later the admin happens to upgrade by the instructions with the package, it's acceptable for the config files to now be in two places and you don't find it confusing. What happens when a new admin comes on and tries to figure out which config file is which? Ever try to figure out where the hell Pine's config really is? I've not used pine, and there doesn't seem to be an official Debian package, (it doesn't allow any changes to its source, I believe, which makes it ineligible). But if it were an official package, I know I should look in /etc/pine. If the admin installs a local build of something he has installed as a package, he will presumably take care to separate the two. If his local build is to replace the package, he should purge the installed package, so that there are no traces of it left. Since he is administering a distribution installation, it is certainly his responsibility to understand the difference between local and distributed packages, as well as the different places that each should put their configuration files. (Incidentally, Debian's changes from the upstream configuration are documented in the package.) In the end, though, when we package for a distribution, we expect people to use the packages. If they want to build from source, the packages system lets them do it. Anyone who is building from the upstream source must be presumed to know what he is doing and take responsibility for it. What your comments strongly suggest to me is that projects like PostgreSQL and pine, along with everything else, should comply with FHS; then there will be no confusion because everyone will be following the smae standards. Messes arise when people ignore standards; we have all seen the dreadful examples of MySQL and the Beast, haven't we? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C The earth is the LORD'S, and the fullness thereof; the world, and they that dwell therein. Psalms 24:1 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 14:28, Bruce Momjian wrote: Robert Treat wrote: On Thu, 2003-02-13 at 14:06, mlw wrote: I will be resubmitting my patch for the 7.3.2 tree. I'm no core developer, but surely this wont be included in the 7.3.x branch. Any change needs to be made against CVS head. I assume he meant he will repost his 7.3.2-based patch and we will merge it into CVS HEAD if it is accepted. IIRC he originally wrote the patch for a pre 7.3 version, so it seems like he'd be reworking it for 7.3.x with the above statement. I'm only suggesting he rework it against CVS head if he doesn't have plans to do so already. Course if yall are willing to merge it in for him, none of this really matters does it? :-) Robert Treat ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 14:43, Bruce Momjian wrote: Robert Treat wrote: On Thu, 2003-02-13 at 12:13, mlw wrote: My patch only works on the PostgreSQL server code. No changes have been made to the initialization scripts. The patch declares three extra configuration file parameters: hbafile= '/etc/postgres/pg_hba.conf' identfile='/etc/postgres/pg_ident.conf' datadir='/RAID0/postgres' If we're going to do this, I think we need to account for all of the files in the directory including PG_VERSION, postmaster.opts, postmaster.pid. In the end if we can't build so that we are either fully FHS compliant and/or LSB compliant, we've not done enough work on it. Woh, how do we move some of those files into /etc or /var/run if we aren't running as root? We certainly don't want to require that. I guess /etc/postgresql will work if that directory is owned by the PostgreSQL superuser, but /var/run will be a problem. Seems like some are saying one of the problems with the current system is it doesn't follow FHS or LSB. If those are valid reasons to change the system, it seems like a change which doesn't actually address those concerns would not be acceptable. (Unless those really aren't valid concerns...) Robert Treat ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 14:51, mlw wrote: Robert Treat wrote: On Thu, 2003-02-13 at 12:13, mlw wrote: My patch only works on the PostgreSQL server code. No changes have been made to the initialization scripts. The patch declares three extra configuration file parameters: hbafile= '/etc/postgres/pg_hba.conf' identfile='/etc/postgres/pg_ident.conf' datadir='/RAID0/postgres' If we're going to do this, I think we need to account for all of the files in the directory including PG_VERSION, postmaster.opts, postmaster.pid. In the end if we can't build so that we are either fully FHS compliant and/or LSB compliant, we've not done enough work on it. postmaster.opts, PG_VERSION, and postmaster.pid are not configuration parameters. So? I'm not saying they all have to be moved, just they all need to be accounted for. PG_VERSION is VERY important, it is how you know the version of the database. Postmaster.pid is a postgres writable value AFAIK, postmaster.opts is also a postgres writable value. IIRC the postmaster.pid file should be in /var/run according to FHS, I'm not sure about postmaster.opts though... Again, if we're going to make a change, let's make sure we think it through. Robert Treat ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
Oliver Elphick wrote: What your comments strongly suggest to me is that projects like PostgreSQL and pine, along with everything else, should comply with FHS; then there will be no confusion because everyone will be following the smae standards. Messes arise when people ignore standards; we have all seen the dreadful examples of MySQL and the Beast, haven't we? Can the FHS handle installing PostgreSQL as non-root? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] location of the configuration files
Robert Treat wrote: IIRC the postmaster.pid file should be in /var/run according to FHS, I'm not sure about postmaster.opts though... Again, if we're going to make a change, let's make sure we think it through. Can non-root write to /var/run? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Incremental backup
Patrick Macdonald wrote: But why would someone want incremental backups compared to PITR? The backup would be mixture of INSERTS, UPDATES, and DELETES, right? Seems pretty weird. :-) Yeah, it's a different method of producing a similar outcome. However, many companies do not want to be concerned with the management (and space) of archived logs. Incremental backup allows them the option of performing a full backup and then only backing up the modifications on a regular basis. When it's time to restore, they'll restore the full backup and then the proper sequence of incremental backups. Wow, I never even thought that was possible. Do other db's support that feature? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [pgsql-advocacy] [HACKERS] Changing the default configuration
To auto-tune, you would need to monitor swap usage and other stuff that may vary too much based on load from other systems. Only the admin knows how to answer some of those questions. --- Jason Hihn wrote: Pardon my ignorance, but there's no way to auto-tune? Ship it with a thread that gathers statistics and periodically re-tunes the database parameters. Of course, be able to turn it off. People that actually take the time to run tune manually will turn it off as to not have the overhead or interruption. Those that don't care about pg_tune shouldn't care about having a thread around retuning. Those that will care will tune manually. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bruce Momjian Sent: Thursday, February 13, 2003 2:22 PM To: Daniel Kalchev Cc: PostgresSQL Hackers Mailing List; [EMAIL PROTECTED] Subject: Re: [pgsql-advocacy] [HACKERS] Changing the default configuration I imagined they could run pgtune anytime after install to update those performance parameters. It gives them a one-stop location to at least do minimal tuning, and as their load changes, they can run it again. --- Daniel Kalchev wrote: Bruce Momjian said: [...] For example, we can ask them how many rows and tables they will be changing, on average, between VACUUM runs. That will allow us set the FSM params. We can ask them about using 25% of their RAM for shared buffers. If they have other major apps running on the server or have small tables, we can make no changes. We can basically ask them questions and use that info to set values. Bruce, this is an very good idea and such tool would simplify setup for the me-too type of DBA - we should definitely try to attract them. However, how could one possibly answer the above question, if they setup their database for the first time? What is more, these settings are on a per-installation, not per-database - which means, that if you have several small, but active databases and one large database the requirements will be very different. Nobody likes answering such questions when installing new software. You might enjoy it the first few times, but then learn the 'answers' and don't even think what the question is. (we all know the answer :) Perhaps indeed a better idea is to have PostgreSQL itself collect usage statistics, and from time to time print 'suggestions' to the log file (best in my opinion), or have these available via some query. These suggestions should best reflect the of course require minimal intervention to the database system, such as restart etc. Daniel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] location of the configuration files
Bruce Momjian wrote: Robert Treat wrote: IIRC the postmaster.pid file should be in /var/run according to FHS, I'm not sure about postmaster.opts though... Again, if we're going to make a change, let's make sure we think it through. Can non-root write to /var/run? Shouldn't be able too
Re: [HACKERS] Incremental backup
Wow, I never even thought that was possible. Do other db's support that feature? Isn't that basically what the current replication kits for Postgresql do -- via triggers and log tables? -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] location of the configuration files
On 13 Feb 2003, Oliver Elphick wrote: What your comments strongly suggest to me is that projects like PostgreSQL and pine, along with everything else, should comply with FHS; then there will be no confusion because everyone will be following the smae standards. Messes arise when people ignore standards; we have all seen the dreadful examples of MySQL and the Beast, haven't we? Actually FHS says the opposite. If the distribution installs PostgreSQL then the config files belong in /etc/postgresql. If the admin does then they belong in /usr/local/etc/postgresql. FHS is out of their tree. If PostgreSQL or any other package is not critical to the basic operation of the operating system, it's config files shouldn't be polluting /etc. Vince. -- Fast, inexpensive internet service 56k and beyond! http://www.pop4.net/ http://www.meanstreamradio.com http://www.unknown-artists.com Internet radio: It's not file sharing, it's just radio. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Brain dump: btree collapsing
Hannu Krosing [EMAIL PROTECTED] writes: But if we would allow the scans to find the same keys twice without ill effects (as was suggested earlier, for using btrees to index arrays), How is returning the same data twice not an ill effect? then we could possibly 1) copy the key to the right 2) wait for all right-to-left scans that have fallen between old and new values to pass and only then 3) delete the old left key. How will you wait for scans that you know nothing of to go past? Especially when they are going to be blocked by your own write lock on the left page? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] location of the configuration files
Robert Treat wrote: On Thu, 2003-02-13 at 14:51, mlw wrote: Robert Treat wrote: On Thu, 2003-02-13 at 12:13, mlw wrote: My patch only works on the PostgreSQL server code. No changes have been made to the initialization scripts. The patch declares three extra configuration file parameters: hbafile= '/etc/postgres/pg_hba.conf' identfile='/etc/postgres/pg_ident.conf' datadir='/RAID0/postgres' If we're going to do this, I think we need to account for all of the files in the directory including PG_VERSION, postmaster.opts, postmaster.pid. In the end if we can't build so that we are either fully FHS compliant and/or LSB compliant, we've not done enough work on it. postmaster.opts, PG_VERSION, and postmaster.pid are not configuration parameters. So? I'm not saying they all have to be moved, just they all need to be accounted for. OK, what was the point? PG_VERSION is VERY important, it is how you know the version of the database. Postmaster.pid is a postgres writable value AFAIK, postmaster.opts is also a postgres writable value. IIRC the postmaster.pid file should be in /var/run according to FHS, I'm not sure about postmaster.opts though... Again, if we're going to make a change, let's make sure we think it through. I'm not a big fan of the "/var/run" directory convention, especially when we expect multiple instances of the server to be able to run concurrently. I suppose it can be a parameter in both the configuration file and command line.
Re: [HACKERS] Brain dump: btree collapsing
Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: But if we would allow the scans to find the same keys twice without ill effects (as was suggested earlier, for using btrees to index arrays), How is returning the same data twice not an ill effect? then we could possibly 1) copy the key to the right 2) wait for all right-to-left scans that have fallen between old and new values to pass and only then 3) delete the old left key. How will you wait for scans that you know nothing of to go past? Especially when they are going to be blocked by your own write lock on the left page? I think we should skip any pages where we can't get a lock. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] location of the configuration files
On Thursday 13 February 2003 17:53, Bruce Momjian wrote: Oliver Elphick wrote: What your comments strongly suggest to me is that projects like PostgreSQL and pine, along with everything else, should comply with FHS; then there will be no confusion because everyone will be following the smae standards. Messes arise when people ignore standards; we have all seen the dreadful examples of MySQL and the Beast, haven't we? Can the FHS handle installing PostgreSQL as non-root? Once again, no one is trying to make an FHS install the default 'let's force everyone to think our way or no way' coercion. We just want the option. For those who wish to do non-root installs, nothing would need to change. You can still put it into /usr/local/pgsql (assuming you have permissions to put it there) or your home directory, or wherever. I deal with RPMs; Oliver deals with .deb's. Neither can be installed as non-root. The daemon can of course run as non-root (and it does, which is exactly correct); but the installation of the files is done as root _always_ in an RPM or deb environment. So I really don't care about non-root installs; sorry. I wonder what percentage of our users are not the administrator of the machine on which they are running PostgreSQL? I dispute the statement made earlier in the thread (not by Bruce) that PostgreSQL is by definition not an OS service. This is false, and needs to be realized by this community. PostgreSQL is becoming an essential OS core service in many cases: virtually all Linux distributions (the lion's share of our current distribution) include PostgreSQL as a core service. Many of our new users see PostgreSQL as 'SQL server' in the Red Hat installation menu. Now, on a Win32 server, what is PostgreSQL going to be considered? It is probably going to run as a service, right? So you need to be Administrator there to perform the install, right? This isn't the same environment, Bruce, that you got into back when it was still Postgres95. We are in the big leagues OS-wise, and we need to act like it. Assuming that we are a 'userspace' program (which is a misnomer anyway, as _anything_ non-kernel is 'userspace') is not going to cut it anymore. So we need to fit in to an OS environment, whether it is FreeBSD, OS/X, Win32, Solaris, or Linux. In FreeBSD, as the ports maintainer excellently posted, PostgreSQL should live in LOCALBASE. We should make that easy. In Win32, configuration might be better stored in the system registry (Argh! Did I actually say THAT! Yuck!) -- we should make even that easy. In OS/X we should use the OS/X paradigm (whatever that is). And we should make it easy to make PostgreSQL LSB-compliant for our very large Linux user community. We should be adaptable to the accepted administration paradigm on whatever system we are running -- this should be a minimum. These concerns vastly outweigh the occasional non-root install from source, in my mind at least. I am not opposed to that way even being the default; after all, leaving the default the same as now agrees with the principle of least surprise (although we really don't ascribe to that; witness the 7.2-7.3 migration fiasco -- 7.3 should have been 8.0 to warn people of the major changes going on in client connections). But I do advocate _allowing_ the configuration options Mark has enumerated -- although I really wish we could use the lowercase c instead, for consistency with other OS services. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] location of the configuration files
On Thursday 13 February 2003 18:07, Vince Vielhaber wrote: Actually FHS says the opposite. If the distribution installs PostgreSQL then the config files belong in /etc/postgresql. If the admin does then they belong in /usr/local/etc/postgresql. FHS is out of their tree. If PostgreSQL or any other package is not critical to the basic operation of the operating system, it's config files shouldn't be polluting /etc. PostgreSQL is as critical as PHP, Apache, or whatever other package is being backended by PostgreSQL. If the package is provided by the distributor, consider it part of the OS. If it isn't, well, it isn't. This is so that local admin installed (from source -- not from binary package) files don't get clobbered by the next operating system binary upgrade. In that context the FHS (LSB) mandate makes lots of sense. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
On Thu, 13 Feb 2003, Lamar Owen wrote: On Thursday 13 February 2003 18:07, Vince Vielhaber wrote: Actually FHS says the opposite. If the distribution installs PostgreSQL then the config files belong in /etc/postgresql. If the admin does then they belong in /usr/local/etc/postgresql. FHS is out of their tree. If PostgreSQL or any other package is not critical to the basic operation of the operating system, it's config files shouldn't be polluting /etc. PostgreSQL is as critical as PHP, Apache, or whatever other package is being backended by PostgreSQL. If the package is provided by the distributor, consider it part of the OS. If it isn't, well, it isn't. You completely miss my point, but lately you've been real good at that. Can the system boot without PHP, Apache, PostgreSQL, Mysql and/or Pine? Can the root user log in without PHP, Apache, PostgreSQL, Mysql and/or Pine? Can any user log in without PHP, Apache, PostgreSQL, Mysql and/or Pine? Note, I'm not even including an MTA here. I said BASIC OPERATION. If a package is not critical as I just outlined, it shouldn't matter who installed it. After the last go around with you Lamar, this will be my last response to you on this. Vince. -- Fast, inexpensive internet service 56k and beyond! http://www.pop4.net/ http://www.meanstreamradio.com http://www.unknown-artists.com Internet radio: It's not file sharing, it's just radio. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Someone's broken psql's connection-failure error reporting
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: In CVS tip, I'm getting only an empty string from psql where it should print connection-failure messages. [ it works here ] Speculation time: do you have the IPv6 code compiled in? I don't. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
On Thursday 13 February 2003 18:41, Vince Vielhaber wrote: On Thu, 13 Feb 2003, Lamar Owen wrote: PostgreSQL is as critical as PHP, Apache, or whatever other package is being backended by PostgreSQL. If the package is provided by the distributor, consider it part of the OS. If it isn't, well, it isn't. You completely miss my point, but lately you've been real good at that. No, Vince, I understand your point. But understand mine: it does matter who installed it. Note, I'm not even including an MTA here. I said BASIC OPERATION. If a package is not critical as I just outlined, it shouldn't matter who installed it. 'Critical' is in the eye of the admin of the system in question. For my servers, if, for instance, sshd doesn't come up, then there's a major problem, as they are all headless. If the webserver doesn't come up, I have other problems, as OpenACS is mission-critical here. So what's critical is a question for the individual sysadmin. So, to continue your point, what is 'critical' to the 'basic operation' of the system shouldn't pollute /etc. So, let's eliminate the /etc/mail, /etc/samba, /etc/xinetd.d, /etc/X11, /etc/httpd, and the other subtrees foung in at least Red Hat 8. While we're at it, many other files in /etc need to go: named.conf for one. It depends on what you consider 'critical'. PostgreSQL is at least as critical on my systems as some of the other things that already 'pollute' /etc. After the last go around with you Lamar, this will be my last response to you on this. Aw Vince, I don't know what your problem is with conflicting opinions. But that's your choice. And Open Source is about _choice_. You are free to admin your systems your way, and I'm free to do so my way. And all's well. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster