Обсуждение: how can i use SELECT to find a substring of a column's data?
hello everyone i am very new to postgresql 7.3.3 and am using it on my mac G4 OS X and i am having some trouble which i hope you can help me out with some answers to my questions: 1.) How can I get a list of users currently logged into the database? Is it possible? 2) I plan on using postgresql for a posting board database, how can I search for a certain word that is in the post's topic subject header? For example if the post subject was "Flying to the moon" and the user wants to find that post he/she enters 'moon' in the search field I made, I've used the : SELECT * FROM mytable WHERE (subject) LIKE 'moon'); command to find a one word exact match but don't know how to find just one word in a string of words using postgresql. ( web/http is not an option for my posting board. ) 3.) Is having the default user 'Postgres' a security risk? wouldn't a hacker look for that default user name and thus make it easier for him to break in? 4.) Is there a database table limit? (can I have 700 tables in my database?) 5.) is there a text length limit? as I said I plan on using postgresql and your plugin for a Posting board and also an email system for my server so I am wondering if there is a limit to the posts and email lengths? 6.) How can I backup my postgresql database and tables? is it possible to backup only certain tables and not the whole database? Is it possible to use your plugin for regular scheduled backups? 7.) how many users can a mac OS X system support at one time while maintaining decent/good performance? does postgreSQL have a limit for mac servers? i've read that windows has a limit of only about 50 simultaneous users. i have a fast G4 and if i have to will buy a G5 i suspect i'll need to support 200 simultaneous users that are actively file swapping. can a G4/G5 handle that? 8.) can anyone recommend a really good book for Mac OS X and postgreSQL? it's very hard for me to get good help/information online, it's slow and complicated to me and it also hurts my eyes. has anyone read O'reily's book 'practicle postgresql'? is it good? I know these are alot of questions but for some reason i am having a really hard time trying to figure it out on my own. thank you very much everyone.
On Friday 12 September 2003 03:34, JoshuaF wrote: > hello everyone i am very new to postgresql 7.3.3 and am using it on my > mac G4 OS X and i am having some trouble which i hope you can help me > out with some answers to my questions: > > 1.) How can I get a list of users currently logged into the database? > Is it possible? Check the table pg_stat_activity - you'll need to turn statistics on I think. See the manual for plenty of details. > 2) I plan on using postgresql for a posting board database, how can I > search for a certain word that is in the post's topic subject header? > For example if the post subject was "Flying to the moon" and the user > wants to find that post he/she enters 'moon' in the search field I > made, I've used the : SELECT * FROM mytable WHERE (subject) LIKE > 'moon'); command to find a one word exact match but don't know how to > find just one word in a string of words using postgresql. ( web/http > is not an option for my posting board. ) Like '%moon%' will work, but won't use an index. There is a full-text search system in contrib that sounds like exactly what you need though. > 3.) Is having the default user 'Postgres' a security risk? wouldn't a > hacker look for that default user name and thus make it easier for him > to break in? Not really - you can disable logins for the user anyway, it just makes admin a little more fiddly at times. If you compile from source, there's nothing to stop you making up your own username though. > 4.) Is there a database table limit? (can I have 700 tables in my > database?) Yep. I think limits are discussed in the FAQ. From memory, 1600 columns per table, terabytes for the database if you have enough disk. > 5.) is there a text length limit? as I said I plan on using postgresql > and your plugin for a Posting board and also an email system for my > server so I am wondering if there is a limit to the posts and email > lengths? The TOAST system lets you store MB of data in a text field. No problem there. > 6.) How can I backup my postgresql database and tables? is it possible > to backup only certain tables and not the whole database? > Is it possible to use your plugin for regular scheduled backups? Use pg_dump for your backups - you can backup a single table or a whole database in a variety of formats. Backups are guaranteed to be a consistent snapshot of your database. You can schedule regular backups from cron (man crontab). Look in the "client applications" section of the manual for info on pg_dump. > 7.) how many users can a mac OS X system support at one time while > maintaining decent/good performance? does postgreSQL have a limit for > mac servers? i've read that windows has a limit of only about 50 > simultaneous users. i have a fast G4 and if i have to will buy a G5 i > suspect i'll need to support 200 simultaneous users that are actively > file swapping. can a G4/G5 handle that? You'll really need to run some tests. If there are lots of updates your biggest problem will be disk I/O - plenty of fast SCSI disks and a raid controller with battery-backed write cache will help there. The performance list is the place to get more details. If you do lots of reading but few updates, then plenty of RAM is good. > 8.) can anyone recommend a really good book for Mac OS X and > postgreSQL? it's very hard for me to get good help/information online, > it's slow and complicated to me and it also hurts my eyes. has anyone > read O'reily's book 'practicle postgresql'? is it good? Well, Bruce's book is probably a little old now, but is a good guide. I think there are a list of books on http://techdocs.postgresql.org/ I also seem to remember someone producing a PDF version of the manuals, so you could print them off. > I know these are alot of questions but for some reason i am having a > really hard time trying to figure it out on my own. > thank you very much everyone. That's what the list is for. All the lists are archived at http://archives.postgresql.org with a search form too - that can be useful. -- Richard Huxton Archonet Ltd
>> 7.) how many users can a mac OS X system support at one time while >> maintaining decent/good performance? does postgreSQL have a limit for >> mac servers? i've read that windows has a limit of only about 50 >> simultaneous users. i have a fast G4 and if i have to will buy a G5 i >> suspect i'll need to support 200 simultaneous users that are actively >> file swapping. can a G4/G5 handle that? > > You'll really need to run some tests. If there are lots of updates your > biggest problem will be disk I/O - plenty of fast SCSI disks and a raid > controller with battery-backed write cache will help there. The performance > list is the place to get more details. > If you do lots of reading but few updates, then plenty of RAM is good. Restrictions in the Mac OS X implementation of shmget limit Postgres to 2MB of shared buffers. This could be a problem for large databases and/or heavy activity. -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 665-7007 voice
Richard Huxton wrote: >You'll really need to run some tests. If there are lots of updates your >biggest problem will be disk I/O - plenty of fast SCSI disks and a raid >controller with battery-backed write cache will help there. The performance >list is the place to get more details. >If you do lots of reading but few updates, then plenty of RAM is good. > > What's this about battery backud up write caches? In the disk sub system or in the main system memory architecture? Can it be added to the PC platform? How does the OS know when it reboots to not wipe that cache and how to assign it to the correct processes in Postgres?
> What's this about battery backud up write caches? In the disk sub system > or in the main system memory architecture? Can it be added to the PC > platform? How does the OS know when it reboots to not wipe that cache > and how to assign it to the correct processes in Postgres? In the disk controller. Independent of the platform. OS does not even know it's there. -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 665-7007 voice
Scott Ribe wrote: >>What's this about battery backud up write caches? >> >In the disk controller. Independent of the platform. OS does not even know >it's there. > > > > Thanks
>Restrictions in the Mac OS X implementation of shmget limit Postgres to 2MB >of shared buffers. This could be a problem for large databases and/or heavy >activity. http://www.postgresql.org/docs/7.3/interactive/kernel-resources.html These directions are sufficient to increase shared buffers significantly beyond 2MB on MacOS 10.2.6. -- Jim Crate Deep Sky Technologies, Inc.