Installing PostgreSQL database on OS X Lion 10.7 configure phpPGadmin

View Comments

This tutorial is a guide to install PostgreSQL on OS X 10.7 Lion client and the client app pgAdmin and browser tool phpPgAdmin to interact with it.

OS X Lion Server is the first OS X operating system to make use of PostgreSQL database replacing MySQL, there are two chains of thought on this, Apple's explanation is that PostgreSQL is a more versatile and powerful database that just suits their needs more and the conspiracists view is that since now MySQL is owned by Oracle it needs to be kept at arms length.

 Installing PostgreSQL

Regardless both database systems run on OS X effortlessly. To get PostgreSQL running on a OSX Lion client machine download the latest point and click binary from PostgreSQL headquarters

Download, uncompress and run the installer:

 - postgresql-9.1.3-1-osx.app

 - First thing is that PostgreSQL needs to adjust how memory is allocated and creates a file 'sysctl.conf in /etc, the system needs to be rebooted for the setting to take effect, then decompress the downloaded dmg and run the installer again.

postgresql-memory-file

 

postgresql-install
 - Run the installer app after the reboot.

 

postgresql-install-directory-data
- Set the install and data directory, the defaults are just fine

 

postgresql-install-password-root
- Set the postgres password  - which is separate from any account of the operating system

 

postgresql-install-port
- Set the port number - 5432 is the one
- Leave the locale on default

 


 - Once the installer has finished, leave the stack builder on to install additional components.

 

postgresql-install-stackbuilder-choice
If you want to add any extras at this stage just select your install from the dropdown. 

 

postgresql-install-stackbuilder-category
Then select the category and checkbox want you want to download. You can always come back to this later via: /Applications/PostgreSQL 9.1/Application Stack Builder.app

 

Configuring pgAdmin

Thats PostgreSQL installed - to interact with the database in /Applications/PostgreSQL 9.1 there is a GUI app called pgAdmin, launch that and you will see your initial connection.

postgresql-install-pgadmin
 - But it has a cross through it and is not connected, double-click it to enter the root database password.


postgresql-install-pgadmin-password



postgresql-install-pgadmin-password
Successful connection

 

Installing and configuring phpPGadmin

To interact with PostgreSQL via a browser you need to install phpPGadmin, this can be done in two ways depending on how you have installed Apache/PHP, you can do it via the /Applications/PostgreSQL 9.1/Application Stack Builder under the Web Development category, but if you already have Apache and php running in your development environment and want to leave it running the way it is best off to download the phpPGAdmin source and use that.

 - Download phpPGadmin, uncompress rename the directory to phppgadmin and file in your web root

 - edit the configuration file to make 2 changes:

/phppgadmin/config/config.inc.php

The first key line to change at this stage is to add in your local server 'localhost':

$conf['servers'][0]['host'] = 'localhost';

The second change is to allow access by the 'postgres' user which you set a password for in the initial PostgreSQL install, by changing the value to 'false':

$conf['extra_login_security'] = false;

postgresql-install-phppgadmin
 - Log in by going to your web root - http://localhost/phppgadmin/

 

postgresql-install-phppgadmin
 - Enter in the superuser 'postgres' and the password

 

postgresql-install-phppgadmin
 - Interact with PostgreSQL via the browser