Re: [GENERAL] "Out of memory" errors..
Hi Lim, "Lim Berger" <[EMAIL PROTECTED]> writes: Wow, you are right! The "su - postgres" showed up with wildly different values! Most notably, the "max user processes" is only 20!! Whereas in the regular user stuff it was above 14000. Would you know how to change this in a CentOS Linux machine? Where can I find the startup settings for postgresql? Yipes, that's pretty bogus. The most likely culprit would be a .profile or .bashrc script belonging to the postgres user --- poke around in its home directory. It might also be in /etc/security/limits.conf. Good luck, Sander ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] "Out of memory" errors..
Hi Lim, It might also be in /etc/security/limits.conf. Thanks. I see these two lines in that file: postgressoftnofile 8192 postgreshardnofile 8192 How should I change these values? I am not sure how this reflects the "ulimit" options. Those are limits to the allowed number of open files (ulimit -n). I think 8192 should be enough for PostgreSQL. The problem you had were related to other settings, so if only the "nofile" setting is changed your strange ulimits do not come from here :-) - Sander ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] "Out of memory" errors..
Hi, > Now if I want a "maintenance_work_mem" of 64M for Postgresql, what > should the "max user processes" setting be in my ulimit, or the "open > files" setting etc? Is there a Postgresql help or doc page I can read > to see how these values map? I'd like to be more educated in how I > test to tweak these OS level values! If this is only a PostgreSQL database server, don't limit the postgres user. Don't tweak these limits unless you know exactly what you are doing. - Sander PS: "maintenance_work_mem" is completely unrelated to "max user processes" or "open files", it's related to the allowed memory size. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL clustering (shared disk)
Hi, > On the other side of the coin, I have little confidence in DRBD > providing the storage semantics we need (in particular guaranteeing > write ordering). So that path doesn't sound exactly risk-free either. DRBD seems to enforce strict write ordering on both sides of the link according to the docs. I didn't look at the code, but my plug-pulling tests on a busy PostgreSQL server didn't cause any problems. No conclusive evidence, but useful at lease in my use-case. (And yes: I make ps_dumps often just in case) - Sander ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER
Hi, > Can I add SHM with merely by managing the entry in sysctl.conf? My > current values: > > kernel.shmmax = 536870912 > kernel.shmall = 536870912 > > My "shared_buffers" in postgresql.conf is "2". From the website > http://www.desknow.com/kb/idx/12/061/article/ I notice that shmmax > should be sharedbuffer*8192, so I suppose my shmmax can be much lower > than the above, but I raised it for performance. Am I wrong to do so? You need to configure the kernel so it allows processes to use more shared memory. This does not mean that a process automatically uses it. For PostgreSQL you will need to increase shared_buffers to make it use the extra available shared memory. With your shared memory settings you can probably increase shared_buffers to about 65000. With the 'ipcs' command you can see how much shared memory PostgreSQL uses. Look under 'Shared Memory Segments' to memory owned by user postgres. - Sander ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER
Hi, > Now, I can merrily increase the shared_buffers, but the manual warns > me against increasing the value too much because it is "per > transaction" value. Shared_buffers is not per-transaction. Where did you find this information? - Sander ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] IP addresses
Hi, - Original Message - From: "Harald Fuchs" <[EMAIL PROTECTED]> To: Sent: Monday, November 19, 2007 7:21 PM Subject: Re: [GENERAL] IP addresses In article <[EMAIL PROTECTED]>, "Tom Allison" <[EMAIL PROTECTED]> writes: I am planning on doing a LOT of work with ip addresses and thought that the inet data type would be a great place to start. Forget inet. Check out http://pgfoundry.org/projects/ip4r/ and be happy. I would be happy if it would support IPv6 :-) Are there plans to make ip6r or something like that? Thanks, Sander ---(end of broadcast)--- TIP 1: 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: [GENERAL] IP addresses
Hi Tom, > "Sander Steffann" <[EMAIL PROTECTED]> writes: > > From: "Harald Fuchs" <[EMAIL PROTECTED]> > >> Forget inet. Check out http://pgfoundry.org/projects/ip4r/ and be > happy. > > > I would be happy if it would support IPv6 :-) Are there plans to > > make ip6r or something like that? > > What's the point? You might as well use the regular inet type if you > need to handle ipv6. Well, the OP said to forget about inet, and I like the ip4r range type. I hoped there was something better/nicer/shinier :-) Thanks, Sander ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] foreign key constraints and inheritence
Hi, http://www.postgresql.org/docs/8.0/static/ddl-inherit.html "A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint." I would realy like to see this fixed. But I guess if it was easy then someone would already have fixed it... - Sander ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] foreign key constraints and inheritence
Hi, >"A serious limitation of the inheritance feature is that indexes >(including unique constraints) and foreign key constraints only >apply to single tables, not to their inheritance children. This >is true on both the referencing and referenced sides of a foreign >key constraint." I would realy like to see this fixed. But I guess if it was easy then someone would already have fixed it... It's on the developers' TODO list. I'm sure a patch would be welcome :-) I wish I knew enough about the internals of PostgreSQL to write one :-) Sander ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [Pgsqlrpms-hackers] Re: [GENERAL] AMD 64 RPM?
Hi, > I double-checked and realized that we have no servers to > build 64-bits RPMs for RHEL 4. I've built ones for RHEL 3.0, > but they may not work for you. > > If you can succeed building the RPMs as Joshua guided, please > let me know and we can upload the binaries to FTP site. As promised: I put them on http://opensource.nederland.net/PostgreSQL/ - Sander. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Anyone using "POSIX" time zone offset capability?
Hi, The POSIX timezone notation as understood by the zic code includes the possibility of zoneabbrev[+-]hh[:mm[:ss]] but the meaning is that hh:mm:ss *is* the offset from GMT, and zoneabbrev is being defined as the abbreviation for that offset. What the datetime.c code is doing is trying to find the zoneabbrev in a built-in timezone table, and then adding the two together. This is simply wacko. I think that if anyone has ever tried to use this notation they would have noticed this misinterpretation of the specs. Given where the code stands now, I think the best solution is to rip out DecodePosixTimezone and instead pass the syntax off to the zic code (which can handle it via tzparse()). Since the datetime input parser is ultimately only interested in the GMT offset value, this would mean that the zoneabbrev part would become a noise word. Sounds like a good idea to me. Sander ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Anyone using "POSIX" time zone offset capability?
Hi, "Sander Steffann" <[EMAIL PROTECTED]> writes: What the datetime.c code is doing is trying to find the zoneabbrev in a built-in timezone table, and then adding the two together. This is simply wacko. I think that if anyone has ever tried to use this notation they would have noticed this misinterpretation of the specs. Well, it'd work without surprise for the case of "GMT+-n", which is undoubtedly the most common case ... H. I hadn't thought of that, but then: with the changes you proposed they would still get what they expect. Even though that notation would not conform to the POSIX docs. Still seems like a good idea :) Sander ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?
Hi, Sure, but the check digit does not need to be stored, as it can be regenerated on demand. The user interface just verifies the check digit, then throws it away. $ SET GEEZER $ WRITE SYS$OUTPUT "THAT'S JUST EXTRA CYCLES WASTED BY THE" $ WRITE SYS$OUTPUT "CLIENT. BETTER TO USE THEM FOR SOME OTHER" $ WRITE SYS$OUTPUT "MORE PRODUCTIVE PURPOSE." $ SET NOGEEZER That's the VAX/VMS in me oozing out. But seriously, regenerate it on demand??? That's not how it works. This isn't a CRC or hash function. Well, a check digit _is_ a kind of CRC. It is redundant information. For every number there is only one correct check digit, which means that the check digit does not add extra information to the number. So why store it? You will need to add the check digit on most (all?) output that is interpreted by humans. The software itself can just use the number itself (assuming you don't need to check the integrity of the software). If you store the number in the database, I would suggest making the db check the number on all input too. Otherwise you might end up with invalid data in the database. - Sander ---(end of broadcast)--- TIP 1: 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: [GENERAL] default index created for primary key
Hi, > I want to turn off the default setting in postgres for index > creation on primary key of a table. Is it possible and how? That is not possible, because the index is used to guarantee the uniqueness of the primary key. What is the reason you want to turn it off? Sander. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] default index created for primary key
Hi, I am actually migrating indexes from oracle database to postgres. I wanted to turn it off so that index on the same columns is not created again (index created for primary key of a table). I'll probably need to check in that case and not create the index if it is on the primary key of the table since that will be created by default. That is the most simple sollution I think. I am still not clear on why postgres has this restriction? By uniqueness, you mean to say that if later anyone wants to add a primary key constraint on a table which already has a primary key defined, postgres will use this index to determine that there is already a primary key defined and would not allow to add this constraint since a table cannot have two primary keys?? No, PostgreSQL uses the index to check that the same value can not occur twice in the primary key field. A pretty important part of primary keys :-) Sander. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] RAID 5 and postgresql
Hi, I would suppliment this with just saying that your controller card is your performance, the only cards I've seen score well on linux, and people have expressed on this list for SCSI are the LSI card, for SATA, LSI, 3ware (now AMCC) and Areca claim good linux support and seem to work well. Steer full clear of Adaptec, Dell and Compaq controllers, and their linux support is abysmal, and the performance reflects that, particularly in RAID 5. Dell has used (and rebranded) Adaptec and LSI controllers for their PERC series, and I agree that the Adaptec controllers perform badly. As far as I know the LSI based controllers are quite good (and some come with 256MB battery backed cache, which is nice :-) Sander. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] RAID 5 and postgresql
Hi, > How about software RAID? > > Linux software RAID appears to perform better than most RAID > controllers except perhaps those that can do read interleaving > for RAID1 (I believe some 3ware controllers can do it). Linux > RAID mirroring doesn't do read interleaving, only read > balancing, which may not be so good for a single sequential > read, but pretty good for concurrent sequential reads - each > drive in a mirror set can handle one sequential read. Don't forget the battery backed cache for write performance. And because the controller doesn't know about the RAID array booting can become a problem when your first drive breaks. > I find many of these RAID controllers fail significantly more > than basic SCSI controllers (which hardly ever fail). And the > support under Linux for such controllers can be a bit patchy > sometimes - you want to be able to easily know if a drive has > died. > > It just seems strange to pay a fair bit for something that > doesn't perform well and is less reliable. The Dell OpenManage tools can help you with that, and if the controller fails (never happened yet here) you can just call Dell support, and within 4 hours they bring you a new one. (I had some strange problems with a server, and Dell replaced the mainboard and memory very quickly) I still choose the Dell LSI-based PERC4/Di where possible. - Sander ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Changing DB ownership
Hi, > Why would you want to do that? > > Why not do it an easier way and dump the database and restore it into > your new database? > > There's got to be a lot of stuff to consider when doing something as > radical as renaming a database. He is not talking about renaming his database, he is talking about changing the OWNER of the database. > >UPDATE pg_database SET datdba = 504 WHERE datname='chris'; This is how I change the owner of the database too. It's not that diffucult, but it would be nice if it could be changed using an ALTER statement. I have noticed in the past that the dumps produced by pg_dump are difficult to restore if the datdba you change to has no rights to create databases. I haven't tested this with recent releases though. I suspect that this has already been fixed in pg_dump. Bye, Sander. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] 7.3.4 RPM
Hi,
> Before anyone can make an rpm for you they will need some more
information.
>
> What type of CPU are you using ? {SPARC, ALPHA, Pentium ...}
>
> What kernel, and libraries are you using?
I will build them for RedHat 6.2 and 7.3 this afternoon. You can find them
in a few hours at http://opensource.nederland.net/, and maybe Lamar can put
them on ftp.postgresql.org.
Bye,
Sander
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] State of Beta (2)
Hi, > Command Prompt will set up an escrow account online at www.escrow.com. > When the Escrow account totals 2000.00 and is released, Command Prompt > will dedicate a programmer for one month to debugging, documenting, > reviewing, digging, crying, screaming, begging and bleeding with the > code. At the end of the month and probably during depending on how > everything goes Command Prompt will release its findings. The findings > will include a project plan on moving forward over the next 5 months > (if that is what it takes) to produce the first functional pg_upgrade. > > If the project is deemed as moving in the right direction by the > community members and specifically the core members we will setup > milestone payments for the project. > > What does everyone think? Sounds good. It provides a safe way for people to fund this development. I can't promise anything yet on behalf of my company, but I'll donate at least $50,- personally. Sander. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] RPM RH9.0 conflict with unixODBC
Hi, > Something is certainly unusual here. Sander, can you rebuild the RH9 set and > see why it is so large? For some reason, I missed how many places were in > there, and missed the fact that there were multiple megabytes difference. > Debugging symbols or no, this is big. The difference in size is 100% related to the stripped/unstripped binaries. These are the RH9 files in /usr/bin as they are in the RPM: -rwxr-xr-x1 planeet sander 208173 Nov 23 01:40 clusterdb -rwxr-xr-x1 planeet sander 208816 Nov 23 01:40 createdb -rwxr-xr-x1 planeet sander 258577 Nov 23 01:40 createlang -rwxr-xr-x1 planeet sander 208161 Nov 23 01:40 createuser -rwxr-xr-x1 planeet sander 205248 Nov 23 01:40 dropdb -rwxr-xr-x1 planeet sander 257890 Nov 23 01:40 droplang -rwxr-xr-x1 planeet sander 205322 Nov 23 01:40 dropuser -rwxr-xr-x1 planeet sander 739671 Nov 23 01:40 pg_dump -rwxr-xr-x1 planeet sander 176362 Nov 23 01:40 pg_dumpall -rwxr-xr-x1 planeet sander 32745 Nov 23 01:41 pg_encoding -rwxr-xr-x1 planeet sander 36674 Nov 23 01:40 pg_id -rwxr-xr-x1 planeet sander 539901 Nov 23 01:40 pg_restore -rwxr-xr-x1 planeet sander 908106 Nov 23 01:40 psql -rwxr-xr-x1 planeet sander 143996 Nov 23 01:40 vacuumdb And after stripping them: -rwxr-xr-x1 planeet sander 22936 Nov 26 01:15 clusterdb -rwxr-xr-x1 planeet sander 22944 Nov 26 01:15 createdb -rwxr-xr-x1 planeet sander 28160 Nov 26 01:15 createlang -rwxr-xr-x1 planeet sander 23000 Nov 26 01:15 createuser -rwxr-xr-x1 planeet sander 20772 Nov 26 01:15 dropdb -rwxr-xr-x1 planeet sander 27564 Nov 26 01:15 droplang -rwxr-xr-x1 planeet sander 20836 Nov 26 01:15 dropuser -rwxr-xr-x1 planeet sander 160776 Nov 26 01:15 pg_dump -rwxr-xr-x1 planeet sander 28824 Nov 26 01:15 pg_dumpall -rwxr-xr-x1 planeet sander 4464 Nov 26 01:15 pg_encoding -rwxr-xr-x1 planeet sander 4592 Nov 26 01:15 pg_id -rwxr-xr-x1 planeet sander 77120 Nov 26 01:15 pg_restore -rwxr-xr-x1 planeet sander 152344 Nov 26 01:15 psql -rwxr-xr-x1 planeet sander 13012 Nov 26 01:15 vacuumdb This makes the difference between 4.1M (before) and 644K (after). I just noticed that I disabled the debug-package that RH9 builds by default. That very probably causes this difference. I will rebuild the RPMs with the debug-package enabled to see what happens. Sander. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] RPM RH9.0 conflict with unixODBC
Hi, It turns out that preventing RH9 from building the debuginfo package also prevented it from stripping the binaries. This was what caused the big difference in filesize. I have rebuilt the RPMs for RH9 and put them on http://opensource.nederland.net/. I had to make a small modification to the specfile (again) because it seems that macro's work differently for each RPM / RedHat version. There have been no other changes to the sources or specfile, so the end-result is the same. Sorry for the inconvenience I caused by disabling the debuginfo package! Sander. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] 7.1 Release Date
Hi Lamar, > I was afraid you'd say that. :-) As long as I can get questions > answered here about the gory details, and without laughing too hard at > my missteps, I'll see if I can tackle this. I think you would make a lot of people very happy with this! Sander.
Re: [GENERAL] quota's ?
Hi, > On Sat, Apr 07, 2001 at 01:46:48PM -0400, Doug McNaught wrote: > > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > > > > Doug McNaught writes: > > > > > > > Doing this would almost certainly result in a corrupted database once > > > > you ran up against the limit. > > > > > > I think you can give PostgreSQL a little more credit than that. ;-) > > > > I'm very glad to hear it. A lot of applications don't cope with > > filesystem-full/quota-exceeded very well at all. ;) > > I've not been following postgres-hackers as closely as I should, but > as of the first betas of 7.1, it would corrupt your database horribly > if you ran out of space. > > I think this has been fixed, but you might wanna check with hackers or > release nots. Just to make sure: what DOES happen if PostgreSQL runs out of space? Sander. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
