I know a lot of people use prefer the
LAMP (Linux
Apache MySQL PHP) stack but for me I like
PostgreSQL. The main reason I prefer PostgreSQL to MySQL (recently purchased by
Sun) is I come from a SQL Server/Oracle background and feel that PostgreSQL has a better implementation of the
SQL (Standard Query Language) standard. That being said I also am a .NET/CF programmer so prefer
Mono/
Tomcat if when I host web based applications on non windows systems. So anyway when I got my new Dual-Opteron server I wanted to load PostgreSQL on it but its more complicated than just running a make install from ports in
FreeBSD.
First being that I like to separate application data from the operating system I generally setup a separate partition for my apps to store data on. In this case I have a 18GB partition labeled
/data upon which I will store the database files. Unfortunately this box is a 1U server and only has space for two drives (RAID-1) so I didn't have the option to put the database data on its own drives which I have found is helpful in windows systems.
Next I ran the
make install from the postgresqlxx-server directory with options being the optimized build and 64-bit date support. Upon completion of the install I was notified that to support more connections I should rebuild my kernel with some options to increase shared memory.
options SYSVSHM
options SYSVSEM
options SYSVMSG
options SHMMAXPGS=65536
options SEMMNI=40
options SEMMNS=240
options SEMUME=40
options SEMMNU=120
I generally use a slightly modified
GENERIC kernel, I use to heavily modify it but found that the performance gain was rather small, so the first three options were already in the kernel. After I added the bottom five options I ran
buildkernel and
installkernel which is covered in my previous
post and rebooted the server.
After the server restarted I logged back in and proceeded to edit the
rc.conf file in
/etc to add the following lines:
postgresql_enable="YES"
postgresql_data="/data/pgsql"
The first line tells the operating system to start PostgreSQL when the server is started, the second tells PostgreSQL to use the
/data/pgsql folder to store database data. At this point I thought all i needed to do to be up in running was run
/usr/local/etc/rc.d/postgresql initdb and
/usr/local/etc/rc.d/postgresql start. Well I was almost correct I forgot that the pgsql user did not have write access to the
/data partition. So I created the folder
/data/pgsql using the
mkdir command and then gave the pgsql user ownership of the folder using
chown pgsql:pgsql -R /data/pgsql. Next I re-ran the
initdb command which executed successfully. Last I decided it would be a good idea to restart the server to make sure everything came up correctly which it did.
Now all i need to do is start the joyous of task of migrating databases, normally not a big deal but I have one thats rather large and will require me to schedule an outage. Undoubtedly this will mean I will have to do it at some awful hour in the morning, most likely on a weekend. Yeah me.