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.