The FreeBSD Diary is a very useful site for various FreeBSD installation and configuration tips and I found their PostgreSQL installation notes particularly helpful.
Generally my preference is to install applications/packages via the Ports tree, as this ensures you have current versions and allows you to control the configuration of your installs. Like using all all convienient installation procedures this also means that lots of default options have been selected and you might not end up with exactly what you want. A way of controlling this is to understand the dependencies amougst the packages and building the various dependent modules seperarately before you build the main application. This will result in the application then building using the configured dependents and so you can control how the overall application is created.
A specific example of this is PHP. PHP is dependent on alot of sub-systems and libraries, including Apache. By default it builds an Apache 1.3 installation, rather than an Apache 2 installation. I like Apache 2 as it includes SSL and has lastest fixes and enhancements.
Also by default PHP will build with MYSQLND MySQL driver embedded, which just bloats the image if you use PostgreSQL as your preferred RDBMS. Irrespective of whether you use MySQL, PostgreSQL or some other database I would strongly recommend that you use PHP Data Objects (PDO) library as this allows you to isolate your PHP code from the particular RDBMS and so saves the headache of worrying about converting code later. should you need to change the underlying database.
The following instructions provide details of the build order to create a PHP installation which uses the desired RDBMS configuration with PDO support and Apache version.
The key ports packages involved are:
SSL, Apache and PHP are all dependent on Perl, an early decision you must make is whether to create a "Threaded" Perl library. The need to create thread safe libraries will propogate across all your builds. As thread are now ubiquitous and there is really no penalty for creating thread safe version of libraries this should be a simple decsion. Build your PHP and Apache solution Thread Safe!
You can achieve this by choicing Thread safe perl as part of SSL build (item 2 following) or explicity create a thread safe Perl instance. Also if you need to have a particular version of Perl installed then this can be achieved by do explicit Perl Ports install.
The Perl 5 libaries are found in /usr/ports/lang/perl-*.
NOTE: Current ports have moved all the perl 5 ports to /usr/ports/lang/perl5.x where x == the desired version
This first step in optional, based on needing a particular Perl version, otherwise just go to step 2
|2||If you are going to store encrypted password data in your PostgreSQL database, the you will need to have the SSL libraries installed on the same machine as the RDBMS server, so build the SSL library before you start compiling the PostgreSQL server, as this will ensure you pick up the right crypto libraries.|
If are going to have the RDBMS running on the same machine as the Apache/PHP server then you should build the appropriate version of the PostgreSQL server. If you intend to run the RDBMS server on a seperate machine then you should build the build the PostgresSQL client only. To ensure that the client can connect to the remote RDBMS server you need to set the permissions correctly on the server machines pg_hba.conf file and that the postgresql.conf has the machine listening on both local host and public socket (listen_addresses = '*', as by default PostgreSQL will only listen on localhost). Both pg_hba.conf and postgresql.conf are in /usr/local/pgsql/data.
The other configuration item that must be completed is to do su to pgsql and create a new "user account" that can be used by remote host. Depending on your application code, this user will require different permission levels.
For client only PostgreSQL installation build the client library only. The ports directory to find PostgreSQL is /usr/ports/databases and the following the naming convention is used for the versions and types: postgresql<ver>-server and postgres<ver>-client.
For the client you can provide client connection configuration information within the file /usr/local/etc/pg_service.conf
Build Apache Portable Runtime is built with PostgreSQL support. For FreeBSD, you can build a PostgreSQL APR library by going into /usr/ports/devel/apr1 and building from there).
NOTE: The APR PostgreSQL library is loaded dynamically as a result of the directive:
Build Apache version
Build PHP and optionally disable the MYSQLND libary module
NOTE: By default PHP will always build with MYSQLND library module. However if you are going to use only PostgreSQL and do not have MYSQL installed then the only way to remove the MYSQL is by editing the Makefile. The relevant line to look for and remove is:
Build various PDO modules...
To build the PostgreSQL PDO module, go to /usr/ports/databases/php-pdo_pgsql and do make and make install from there. If you want to build other PDO modules then find the equivalent database specific libraries in /usr/ports/databases
Install the PostgreSQL pgcrypto module into the database that is being used to hold the users information. This is not required if you are directly putting encrypted data values into the tables, but if you want to create some identity and registration funtionality on top of your PostgresSQL credential repository, then you will need this module.
Its installation is a multi-step process. I followed the process documented here, as it describes what is needed to build the library from the PostgresSQL server ports area. The only difference I took from this was due to using PostgreSQL version 9.0 and it mentions changing the Makefile to pickup the OpenSSL crypto library. If you have done step (2) above and installed the OpenSSL libraries in /usr/local/lib then you do not need to change the Makefile as the /usr/local libraries will get picked up automatically.
To use the pgcrypto module you need to then add the SQL functions into each database that needs them. The specific steps were:
All materials are copyright (C) 2011, 2012, 2013Graphica Software/Dokmai Pty Ltd
Send any questions or comments regarding this
site to info at graphica.com.au
Last Updated: Sunday 29th December, 2013.