[ADMIN] to --enable-locale or not to --enable-locale?
Hi! I am now in the process of upgrading a couple of pg installation to 7.2. I am a little in doubt if I should or not use the --enable-locale when ./configuring. Even though I am a Norwegian, I prefer to have the messages from the data base in english as that makes it much simpler to ask questions here or to searc for help on the web. on the other hand, I need to store character string containing letters outside the 7bit ASCII char.set (e.g. æøåäëö etc.) and I've had some problems there with my existing 7.1.3 and 7.1.2 installations. E.g what is put in as 'Tromsø' at one PC later shows up as 'Troms' at another one running the same application... Would --enable-locale or some other ./configure flags (--enable-nls?, --enable-recode, --enable-multibyte?) help here, or do I have to tweak my application (written in Delphi, using Pg-ODBC)somehow? Morten (well, I assume I have to do some data cleaning if I transfer my data to a new database with a different coding, but I prefer that to the present situation) -- Morten Sickel Norwegian Radiation Protection Authority ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] backup database under cygwin
Hi, I am using PostgreSQL under cygwin/win2k. which is the best way to schedule a backup? under Linux, you can cron a pg_dump job (with a su to manage the user/password), but i don't know how to handle this under cygwin? any idea? thanks JC _ Jean-Christophe FABRE INRA - UMR Sol Environnement tel: +33 (0)4 99 61 23 51 2, place Viala fax: +33 (0)4 67 63 26 14 34060 MONTPELLIER cedex 1 FRANCE ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] 7.2 occupy big hard disk space
Hi, I use Postgresql 7.0.2 in my data logging application system, I have to vacuum data base every 8 hours because of many UPDATE requests. The data base is going well except that a big pg_log file, anyway I can accept it. Recently, I got a new computer to porting my application system. I install PostgreSQL 7.2 + RH 7.2 ext3 file system. Everything seems fine (pg_xlog contains 7 files, size=16777216 bytes), but I find my data base occupied a terribly big space in in the hard disk (PGDATA=/by2db), it almost 4 times of the old one(7.0.2): postgreSQL 7.2 [sflee@by2cpua_1:/home/sflee]$ df Filesystem 1k-blocks Used Available Use% Mounted on /dev/sda1 6190664 3145868 2730328 54% / /dev/sda5 4134900 1131988 2792864 29% /by2db postgreSQL 7.0.2 [sflee@by2cpua:/home/sflee]$ df Filesystem 1k-blocks Used Available Use% Mounted on /dev/hda1 4059377 1264212 2585117 33% / /dev/hda5 2035606196228 1734154 10% /by2ap /dev/hda6 2035606274422 1655960 14% /by2db These two data base sever engines have the same data base,tables,fields,tuples, requested by the same application program and vacuum data base in the same time. PostgreSQL 7.0.2 has been run for almost 1 year and PostgreSQL 7.2 only 14 days. Can anyone tell me how to do it? regards, S.F. Lee __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] scheduling tasks under cygwin
Hi, to a larger way, what is the best way to schedule tasks such as dump, vacuum, analyze under cygwin? as I notice before, under Linux (and generic UNIX systems), you can use cron to do that, but under cygwin? JC _ Jean-Christophe FABRE INRA - UMR Sol Environnement tel: +33 (0)4 99 61 23 51 2, place Viala fax: +33 (0)4 67 63 26 14 34060 MONTPELLIER cedex 1 FRANCE ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[ADMIN] config new server (problems with email server)
Hi. Sorry for that duplicated e-mail, but I think we have any problem with the e-mail server. I've a new server to put the database production. The characterictics of the new server are: 4 cpu's INTEL 1Ghz. 4 Gb. RAM My database is about 125-130 Mb of size, and I think this configuration for the server: OS Debian/GNU Linux kernel 2.4 serie 2103872000 for echo 2103872000 /proc/sys/kernel/shmmax shared_buffers = 326400 sort_mem = 128672 is it right this configuration? The max_backends are 256 (this database is the column of a dynamic web site with a lot of hits). Can anybody help me please? Thank you. Have a nice day ;-) TooManySecrets ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] [CYGWIN] scheduling tasks under cygwin
I use at on NT to run .bat files Example for vacuum analyse: I first use at to run va.bat va.bat: c:\cygwin\bin\bash --login -i /va.sh va.sh: vacuumdb -a -z /dev/null 21 /dev/null Hope this helps, - Stuart -Original Message- From: Jean-Christophe FABRE [mailto:[EMAIL PROTECTED]] Sent: 15 March 2002 13:11 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [CYGWIN] scheduling tasks under cygwin Hi, to a larger way, what is the best way to schedule tasks such as dump, vacuum, analyze under cygwin? as I notice before, under Linux (and generic UNIX systems), you can use cron to do that, but under cygwin? JC _ Jean-Christophe FABRE INRA - UMR Sol Environnement tel: +33 (0)4 99 61 23 51 2, place Viala fax: +33 (0)4 67 63 26 14 34060 MONTPELLIER cedex 1 FRANCE ---(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
[ADMIN] problems with dbf-files
Hello! I run postgreSQL-7.2 on RedHat Linux 7.2, kernel 2.4. I want to import and export dbf-tables in and from postgres. I found pg2xbase library that requires xbase library. After installation, the conversion does not work (segmentation fault, exports empty tables, distortion of numeric fields in import). My questions are: 1) Did I overlook any switch or parameter to ./configure the postgres? 2) The pg2xbase recommends the xbase-1.8.1 which I cannot find. I use xbase-2.0.0. Too many zeros, can it be a problem? Can I get xbase-1.8.1. somewhere? 3) Does anybody know a better (more stable...) way of import/export of dbf-tables? Thank you for any tips. Ladislav Jirsa ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Postgres 6.4, gmake, d: command not found
Eric Scroger [EMAIL PROTECTED] writes: gmake[2]: Entering directory `/usr/share/src/pgsql/postgresql-6.4.2/src/backend/bootstrap' d bootparse.y gmake[2]: d: Command not found gmake[2]: [bootparse.c] Error 127 (ignored) Looks like the configure script has chosen d instead of bison or yacc (not clear why ... do you have YACC defined in your environment?) You'll probably get further if you install bison and reconfigure. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] to --enable-locale or not to --enable-locale?
Morten Sickel writes: Even though I am a Norwegian, I prefer to have the messages from the data base in english as that makes it much simpler to ask questions here or to searc for help on the web. on the other hand, I need to store character string containing letters outside the 7bit ASCII char.set (e.g. æøåäëö etc.) and I've had some problems there with my existing 7.1.3 and 7.1.2 installations. E.g what is put in as 'Tromsø' at one PC later shows up as 'Troms' at another one running the same application... Would --enable-locale or some other ./configure flags (--enable-nls?, --enable-recode, --enable-multibyte?) help here, or do I have to tweak my application (written in Delphi, using Pg-ODBC)somehow? OK, let's see: --enable-locale gives you two things: 1) The ability to sort text according to your local preference (i.e., a..zæøå) in Norwegian. 2) The ability to do case-insensitive text comparisons (so ø and Ø match) I would guess that you want that. --enable-multibyte gives you two things: 1) The ability to store multibyte characters in the database. Since Norwegian works with ISO 8859-1 or -15, you probably don't need that. 2) The ability to do character set conversions between client and server. If you client is all Unicode (which at least all Java and Tcl clients are), then you need this. (Or you can store everything in the database in Unicode, but then you need (1).) --enable-recode is a simplified version of part (2) of multibyte, which only works for single-byte encodings. It's mostly useful for environments where Unix and Windows use different character sets for the same language. (I think Czech was an example.) --enable-nls gives you the ability to see messages in a different language, but since no one has contributed a Norwegian translation yet, this is going to give you zero benefit. -- Peter Eisentraut [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])
[ADMIN] Errors on VACUUM
I received the following error from a VACUUM ANALYZE: NOTICE: FlushRelationBuffers(place, 454): block 231 is referenced (private 0, global 4) FATAL 1: VACUUM (vc_repair_frag): FlushRelationBuffers returned -2 and psql lost the connection right after that. This was repeatable, after the first occurrence I re-started the postmaster and tried again, same result. The particular table in question is static data, that is it was originally filled in once by COPY and there were no subsequent inserts or deletes. But I had just added a column to the table and updated it from another table to eliminate a frequently-done join, that was the reason for doing the VACUUM. Since I had the text file with the complete table contents from the original COPY, I decided to re-build the table, so next I did DROP TABLE, and I got this: NOTICE: Buffer Leak: [004] (freeNext=-3, freePrev=-3, relname=place_pid, blockNum=1, flags=0xc, refcount=2 -1) NOTICE: Buffer Leak: [005] (freeNext=-3, freePrev=-3, relname=place, blockNum=231, flags=0xc, refcount=4 -1) NOTICE: Buffer Leak: [008] (freeNext=-3, freePrev=-3, relname=place, blockNum=85, flags=0xc, refcount=3 -1) NOTICE: Buffer Leak: [011] (freeNext=-3, freePrev=-3, relname=place, blockNum=0, flags=0xc, refcount=2 -1) . . repeating about 20 more times with blockNum varying, and finally ending with the table being dropped. I then re-built the table from scratch and everything seems to be fine now, VACUUM no longer gives errors. Here is my system configuration: PostgreSQL 7.0.2 RedHat Linux 7.0 P-III 800, 768MB RAM, 80GB disk So I'm wondering what happened here, what might have caused the original error, if something else is potentially still corrupted, and if I should maybe re-build the whole database to be safe? Advice much appreciated! Bob Smith Hammett Edison, Inc. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Errors on VACUUM
Bob Smith [EMAIL PROTECTED] writes: PostgreSQL 7.0.2 Time to update ... I'm rather surprised that restarting the postmaster didn't make the error go away, but it's unlikely that anyone will care to investigate --- unless you can reproduce the problem in 7.1.3 or later. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] Question ..
How i could know the database name from directory name (/usr/local/pgsql/data/base/) Hernan Nuñez | VIA NET.WORKS Argentina NICAR-P37184 | [EMAIL PROTECTED] | local touch global reach System Administrator | Tecnología y Operaciones | http://www.vianetworks.com.ar + ICQ: 36528552 | DDI +54 11 4323 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] [GENERAL] Btree index extension question
Hi, Don't know enough about postgresql to be sure about this, but here goes: If postgresql does bitwise operations, then you can use that instead of defining new operators. Just construct a number for all the columns that need to be true and do a bitwise 'and' with the stored value. (eg. (7 stored_val) = 7) If postgresql uses an index to supply functions with their parameters, then make a function that'll do the comparison for you and use it in your query. Or make the index (on all the columns) and make a function that takes all the columns as the parameters to compare against (and ofcourse the values that you want to check against). That way you always use the columns of the index in the correct order. Somebody please check this, as I may have been hit with a stupid-stick. On Fri, 15 Mar 2002, Dmitry Tkach wrote: Hi, everybody! I was wonderring if there is somebody out there who could help me with understand how index extensions work... Let me state the problem first. I have many (15) boolean attributes and I need to be able to search the database for entries with any combination of those attributes for being true. For example - find all the entries, where a1=a2=a3=true or find all the entries where a1=a2=a4=true etc... Because there are so many of them (and the database is HUGE), putting every attribute into a separate column and creating a separate index on every possible combination, is really out of the question. So, I was thinking about creating a single int2 column, with each bit representing an attribute - so that, the first query I quoted above would look like select * from table where attributes 7 = 7, and the other query would be select * from table where attributes 11 = 11' etc... This looked so beautiful to me, but now I am stuck trying to index that table [:-(] I started off, hoping to get away with btrees. I defined an operator =(int2,int2) as 'select $1$2=$2;' It looks nice so far, but then the question is - what do I do with the other operations? By analogy with 'normal' comparison operators, I would do: (I know the name is taken [:-)] as 'select not $2 = $1' = as 'select $2 = $1' as 'select not $1 = $2' .. and leave '=' intact. But then I realized, that these set of operators, does not really define a complete order - for example, if I compare, say, 5 and 3: 5 3 = 1, 3 5 = 1, so I get BOTH 5 3 and 5 3 being true at the same time [:-(] So my question is, first of all, is that a problem? Does btree require a complete order defined? Will it work with partial order? Secondly, if it is a problem, perhaps, I am missing something here, assuming that there is no way to define a set of operations to do what I want and provide a completely ordered set (or do I need it to define a perfect complete order - what exactly is required for btree to work? Any ideas?) And finally, if there is just no way I could get away with btrees, can I make an rtree to work for me? Could somebody explain to me (or point me to a doc somewhere) the meaning of the strategies (and requirements - like transitivity etc...) I need for an rtree, and also what support functions (like comparison func in case of a btree) do I need? Thank you very much for your attention. Any input will be greatly appreciated. Dima ---(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: [ADMIN] [GENERAL] Btree index extension question
On Fri, 15 Mar 2002, Dmitry Tkach wrote: [EMAIL PROTECTED] wrote: If postgresql does bitwise operations, then you can use that instead of defining new operators. Just construct a number for all the columns that need to be true and do a bitwise 'and' with the stored value. (eg. (7 stored_val) = 7) Yeah... The thing is that I want to be able to the index. And to use the index, I need BOOLEAN operators (this seems to be the LEAST of my problems,but anyway) - so, I have to define 'wrappers' around the standard bitwise operations - e.g. a = b --- a b = a; Ok, let's see whether we understand each other: 1. Make a column that contains the bitstring of your 15 boolean columns. Let's call it bitstring. 2. bitstring is calculated on each insert or update by a trigger. 3. Make an index on bitstring. 4. Make a query to find the records that have a1, a2 and a3 set to true, like so: SELECT * FROM table where (bitstring 7) = 7; Will this not give you the correct answer and use the index on bitstring? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Errors on VACUUM
On Friday, March 15, 2002, at 09:53 , Tom Lane wrote: Bob Smith [EMAIL PROTECTED] writes: PostgreSQL 7.0.2 Time to update ... I know, I know... But I'm about two weeks from bringing a whole new server on-line with the latest version of Postgres, so I don't want to hassle with upgrading the old one. I just need to keep it limping along for a little while longer. I'm rather surprised that restarting the postmaster didn't make the error go away, but it's unlikely that anyone will care to investigate --- unless you can reproduce the problem in 7.1.3 or later. So this isn't an error that would be caused by some sort of file problem? The server got new hard drives a few months ago and files went through a backup/restore with tar, I didn't use pg_dump/pg_restore, so it occurred to me that something might have been scrambled. But until this VACUUM failure, the only problem I've _ever_ had, before or after the drive upgrade, was one occurrence of the Buffer Leak error which I couldn't reproduce. Well, I guess I just keep my fingers crossed for a couple of weeks until I have my new server up. Thanks! Bob Smith Hammett Edison, Inc. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] problems with dbf-files
I tried that program quite a while back and it didn't work at all...not even close. I export the dbf tables to tab-delimited text files and COPY them into a Pg table. --Jeremy On Wednesday, March 15, 102, at 06:46 AM, Ladislav Jirsa wrote: Hello! I run postgreSQL-7.2 on RedHat Linux 7.2, kernel 2.4. I want to import and export dbf-tables in and from postgres. I found pg2xbase library that requires xbase library. After installation, the conversion does not work (segmentation fault, exports empty tables, distortion of numeric fields in import). My questions are: 1) Did I overlook any switch or parameter to ./configure the postgres? 2) The pg2xbase recommends the xbase-1.8.1 which I cannot find. I use xbase-2.0.0. Too many zeros, can it be a problem? Can I get xbase-1.8.1. somewhere? 3) Does anybody know a better (more stable...) way of import/export of dbf-tables? Thank you for any tips. Ladislav Jirsa ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Errors on VACUUM
Bob Smith [EMAIL PROTECTED] writes: I'm rather surprised that restarting the postmaster didn't make the error go away, but it's unlikely that anyone will care to investigate --- unless you can reproduce the problem in 7.1.3 or later. So this isn't an error that would be caused by some sort of file problem? AFAICT what you have there is just dangling buffer reference counts; ie, something didn't release its reference to a disk page buffer in shared memory. We used to have quite a number of bugs of that ilk, though I've not heard of such lately. They're fairly harmless normally. It's not clear to me why a restart (which would naturally clear shared memory) didn't fix the problem. Perhaps you had some configuration on disk that tickled the original buffer-leak bug again each time you restarted; but I dunno what. VACUUM itself was never prone to such errors, so it's hard to see how a restart and immediate VACUUM would show the problem. Were you doing other stuff in between? Have you got any interesting stuff (like functional indexes, maybe) on this table? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] [GENERAL] Btree index extension question
On Fri, 15 Mar 2002, Dmitry Tkach wrote: ... Yes, and know... (Yes, it will give th ecorrect answer, and NO it will not use the index). The thing is, that it will only use a (btree) index for one of the 'comparison' operators (=, , =, , =), that compare the value in the table with the query parameter - so, bitstring = 7, or bitstring 7 etc... will work, but 'do_something_to_bitstring = 7' will not (I mean, it will, but it won't use the index). Okay, I'm recovering from my encounter with the stupid stick. I understand this: 1. You want to use a btree index because presumably it's faster than a normal index. 2. A btree index is a binary tree index that uses the order of values to find an answer quickly. 3. In your case for instance: a value of 10 should produce a resultset with bitstrings of 10, 11, 14, 26, ... So yeah, since 12 and 13 are 10, normal equality operators won't work. If a solution were to be found, I think it should base the ordering on the bits from least significant bit to most significant. So, for 1st bit go left in the tree for a 0 and right for a 1 and same for next bit. But no, this won't work since you don't care about the other boolean columns, solong as the ones your looking for are true! To clarify, if you don't care about the first column/bit, values from both the left and the right parts of the binary tree can be valid. Wild guess: base the ordering on the number of bits and then try to narrow it down in the select with an equal operator. SELECT * FROM TABLE WHERE bitstring 7 AND bitstring = 7; Wouldn't know how to get this to work though! :( Oracle has a special index type for just this case. I think they call them bitmap indexes. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Errors on VACUUM
On Friday, March 15, 2002, at 03:07 , Tom Lane wrote: It's not clear to me why a restart (which would naturally clear shared memory) didn't fix the problem. Er, um, (humble look), I just studied my syslog, and in fact the postmaster _didn't_ restart. I tried to restart it using the Red Hat control script from /etc/rc.d/init.d, but apparently that script lied to me when it said the restart succeeded. The syslog clearly shows the postmaster didn't actually restart until about 10 minutes later, _after_ the second error. So I was talking to the same postmaster every time. Arrggh. I'm reassured now that this is just a passing memory management issue and not a symptom of a more serious problem, I'm sure the error wouldn't have happened the second time if the postmaster had in fact restarted. Everything looks fine now so I'm going to stop worrying about it. Thanks much for your help! Bob Smith Hammett Edison, Inc. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
