Brandito520 Posted June 17, 2008 Share Posted June 17, 2008 Howdy, I'm in the very early stages of gathering information for a web project I am trying to develop. It is going to be a classified ads website, think autotrader.com - but better and more user friendly. I have pretty much singled in on PostgreSQL over MySQL. It seems a lot of experienced programmers truely prefer it, but there are so many different releases of PGSQL I am not sure which one to go with... so far I have found: EnterpriseDB, Greenplum Database, Mammoth PostgreSQL and of course the standard www.postgresql.com release. Are there really any advantages to the other / commercial versions? What do you guys use and recommend? Also, something that is a bit concerning is that in looking over the PHP manual there seems to be a lot less PGSQL functions and support over MySQL. Has the MySQL layer of PHP just become bloated, or is PostgreSQL not supported very well by PHP? Have you ever run into anything you couldn't do with PGSQL, that you could with MySQL? Regards, Brandon Quote Link to comment https://forums.phpfreaks.com/topic/110537-what-is-your-current-postgresql-setup/ Share on other sites More sharing options...
btherl Posted June 17, 2008 Share Posted June 17, 2008 We use standard postgresql at my workplace, and I have no experience with the others. The only thing I have found missing from the php postgres interface is an "in transaction" checker. So we use our own wrappers that track transactions (also allowing simulation of nested transactions using savepoints). Here's the two biggest disadvantages of postgres from my viewpoint - No hints. If the query optimizer picks a bad plan, you have to resort to trickery to force it to use the right one. This caused me serious trouble a year or two back, but none since. - Only primitive collation order support. Collation order is set when you create a database, and cannot be altered. You can however create custom orders (I have never done this myself). Whatever database you use, I highly recommend a memcache caching layer if you have a lot of repeated read-only queries. It can make a world of difference. Quote Link to comment https://forums.phpfreaks.com/topic/110537-what-is-your-current-postgresql-setup/#findComment-567075 Share on other sites More sharing options...
Brandito520 Posted June 17, 2008 Author Share Posted June 17, 2008 Hi, Thanks for the reply btherl. I don't completely understand the functions in the lacks for PGSQL (Still pretty new to the coding world), but I don't expect a spoon-feeding and I will research it more to see if it will be applicable to my project. About the Memcache layer though I found to seemingly similar results on Google, are you suggesting I install Memcached (http://www.danga.com/memcached/) or are you referring the built-in Memcache (http://us2.php.net/memcache) functions of PHP and interjecting them into the core PHP development? Or am I just confused about something? Regards, Brandon Quote Link to comment https://forums.phpfreaks.com/topic/110537-what-is-your-current-postgresql-setup/#findComment-567647 Share on other sites More sharing options...
btherl Posted June 18, 2008 Share Posted June 18, 2008 Those are one and the same Here the php memcache documentation links to the danga.com site. The missing functions were ones related to the state of the connection to the PG server. Eg, if you want to ask things like "Am I currently in a transaction?" or "Is the server ready to accept a command?" you might have to find other ways to do that. I believe there are new functions in PHP 5 that do some of this, like pg_transaction_status(). But these are not essential functions. All the essential functions are there. Quote Link to comment https://forums.phpfreaks.com/topic/110537-what-is-your-current-postgresql-setup/#findComment-567867 Share on other sites More sharing options...
Brandito520 Posted June 19, 2008 Author Share Posted June 19, 2008 I guess I was just lost... I don't think I'll be implementing Memcache quite yet though, as it is just one more thing I will need to learn and implement in my project. Once I get some traffic and my site is stable its definitely something I want to look into though. After looking at a few other things though, I'm not sure if I want to go with PostgreSQL... The backup software I'm looking at: R1Soft CDP, only supports MySQL for the database backups. Maybe I just need to keep looking for a solution that will work with PGSQL for backups... Quote Link to comment https://forums.phpfreaks.com/topic/110537-what-is-your-current-postgresql-setup/#findComment-568718 Share on other sites More sharing options...
btherl Posted June 19, 2008 Share Posted June 19, 2008 We use the command line tool pg_dump to do backups of our postgres databases. I can produce a text file with sql statements to reconstruct the database (useful if you want to edit it before restore), or a non-portable binary format. We're running it on linux. We use the text option, as it's much more flexible. A backup is done like this: pg_dump -hdbserver dbname | gzip > dbname_backup.gz And a restore is like this (After creating a suitable database) gzip -dc dbname_backup.gz | pgsql -hdbserver dbname The backup consists of sql statements, and pgsql executes each of those statements in sequence to restore the database. If you use mysql, you'll find a lot more software with support for it. That is the clincher for many people when choosing between them. We chose postgres because we need high performance and reliability, and we believe postgres to be good for those (That statement carefully crafter to avoid flaming from mysql lovers ) Mysql vs postgres is something of a religious war btw. Quote Link to comment https://forums.phpfreaks.com/topic/110537-what-is-your-current-postgresql-setup/#findComment-568778 Share on other sites More sharing options...
Brandito520 Posted June 19, 2008 Author Share Posted June 19, 2008 Well, I suppose that is also an option. But I would greatly prefer the automatic backups. The major advantage to the automatic backups that I see is the ammount of backups you can have going, that would be completely unpractical to attempt to do manually. Here is an example policy, from R1Soft: synchronize every 10 minutes – retain the last 48 recovery points synchronize hourly – retain the last 48 recovery points synchronize daily at midnight – retain the last 7 recovery points synchronize weekly on Sundays – retain the last 4 recovery points synchronize monthly on the 1st – retain the last 48 recovery points Clearly this provides extremely up-to-date backups, all without having to even press a button. And with the integration with Amazon S3, I can host my back-ups off site for just a couple bucks a month. In reviewing other backup services besides R1Soft though, Zmanda seems promising. Mysql vs postgres is something of a religious war btw. Why do people always get deadlocked in those kind of arguements? People should just research and decide whatever option / software suits there project best, and not be such kool-aid drinkers. Quote Link to comment https://forums.phpfreaks.com/topic/110537-what-is-your-current-postgresql-setup/#findComment-568868 Share on other sites More sharing options...
keeB Posted June 28, 2008 Share Posted June 28, 2008 Amen! I hope you realize, and I am not trying to convince you either way, but -- synchronize every 10 minutes – retain the last 48 recovery points synchronize hourly – retain the last 48 recovery points synchronize daily at midnight – retain the last 7 recovery points synchronize weekly on Sundays – retain the last 4 recovery points synchronize monthly on the 1st – retain the last 48 recovery points Can be done with a pretty simple cron job. I have exported fairly sizable database, sometimes between 10-15gb of data, relatively quickly using pg_dump. Quote Link to comment https://forums.phpfreaks.com/topic/110537-what-is-your-current-postgresql-setup/#findComment-576956 Share on other sites More sharing options...
btherl Posted June 30, 2008 Share Posted June 30, 2008 That's simple for someone experienced with unix .. for someone inexperienced, they will have to deal firstly with their crontab entries being misformatted, then with their scripts not doing what they are supposed to, then with their disks filling up because the "retain last 48" didn't work properly, etc etc And after all that, you realize that instead of databases 1 through 5, you've actually got 5 backups of database 1 Sometimes it's nice just to pay someone else to have their product do the backups for you. As long as its a decent product. In our case we prefer to do it manually anyway, as we have several databases in the 100-500GB range, and the backups impact the performance of other jobs we are running on those databases. We also have to think hard about where we store all those backups and how many we need to keep, given the space they take up. Quote Link to comment https://forums.phpfreaks.com/topic/110537-what-is-your-current-postgresql-setup/#findComment-577769 Share on other sites More sharing options...
keeB Posted June 30, 2008 Share Posted June 30, 2008 Point taken Quote Link to comment https://forums.phpfreaks.com/topic/110537-what-is-your-current-postgresql-setup/#findComment-577775 Share on other sites More sharing options...
Brandito520 Posted July 4, 2008 Author Share Posted July 4, 2008 Hey, sorry for the delayed reply, I've been out of town. Thanks for the tips, but I don't think I could really pull it off manually. I would have no idea how to setup a cron job, and really don't know how to use the command line... I want to learn though. And I'm looking into getting Red Hat Training: https://www.redhat.com/training/ What do you guys think of the RHCE, certification? Or even then RHCT? Quote Link to comment https://forums.phpfreaks.com/topic/110537-what-is-your-current-postgresql-setup/#findComment-581423 Share on other sites More sharing options...
btherl Posted July 7, 2008 Share Posted July 7, 2008 No comment from me on Red Hat training .. I haven't taken it and am self taught. I'm sure it looks good on your resume though If a hirer has 100 candidates and only 10 have Red Hat certification, that's an easy criterion to thin the herd on. Quote Link to comment https://forums.phpfreaks.com/topic/110537-what-is-your-current-postgresql-setup/#findComment-583194 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.