« »

Filed under databases,linux,php,programming,webhosting

New Stack! PostgreSQL 9.1, Nginx, PHP, APC, Suhosin.

It’s been ages since I last wrote – I was ill again for some months and unable to update the site. I am now back to posting more often.

One of the big changes has been my decision to switch from MySQL to PostgreSQL. I will come back to the installation of this later, but suffice it to say it was not an easy ride given that I am running a compiled version of PHP.

 

There were several reasons:

  • PostgreSQL 9.1 (beta) supports Master/Slave replication and streaming natively with fallover. This is simply fantastic news for anyone running a high availability platform.
  • It is more future-proof especially in terms of scalability. I have read horror stories of production projects getting bigger then having to move to PostgreSQL etc.
  • I don’t trust Oracle to do right by MySQL. I already fear for Netbeans.
  • All the other normal reasons for PostgreSQL vs. MySQL
As I am using Nginx/Suhosin/PHP compiled (using Vlad’s guide) this also meant recompiling PHP to include the pgsql module. I did this by compiling PostgreSQL first, then adding –with pgsql to the PHP compile options. In both cases I would strongly recommend doing what I didn’t do first time, and reading installation instructions for both in their respective documentation.
You will also need to setup PostgreSQL with the recommended username of postgres. I didn’t the first time and it just led to more problems. PgAdmin III is a good windows interface to manage the database remotely, though it isn’t for the feint hearted, especially if you use SSH to connect to your server. So, my overall setup is:
  • Ubunti 10.04 LTS
  • Nginx 1.04
  • PHP 5.3.5 with PHP-FPM, APC & Suhosin
  • PostgreSQL 9.1 Beta
I am still wrestling with the database design, mostly due to foreign key issues and getting a design that normalises. But, I am going ahead with some site coding in order to learn more about PostgreSQL with PHP.
If anyone has a similar setup I would love to hear how it works so I can compare.
EDIT: My script is deprecated as I am now working in Ruby. See Vlad’s site for further information.

3 Comments on Jul 6th 2011

3 comments
  Livefyre
Newest | Oldest
kevin
kevin

I have now placed this on github: click here. I also have some old stuff where I compiled postgres, but you shouldn't need this now that there is a backport. I realise I should really have forked Vlad's stuff, but at the the time I knew nothing about github.

kevin
kevin

Hi Richard, It works well but I was in the middle of adding SSH and Firewall support, will comment those out. Digging it out now and will upload to my github for you - hold tight for the address :)

Richard Craig
Richard Craig

Hi Kevin Looks like what I'm about to embark upon. Would love to have a copy of your script to try. Thanks Richard