Jump to content

What is your current PostgreSQL setup?


Brandito520

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

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 :P

 

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.