Tutorial: PostgreSQL on Mac OS X via MacPorts

Still taking on latest turorials, today we will be looking at installing PostgreSQL Server using MacPorts.

We should update our ports tree,

$ sudo port sync

and move on to install the PostgreSQL Server

$ sudo port install postgresql82-server

Like MySQL, i want PostgreSQL databases to live inside /Users/DB/postgresql, so let's create that directory.

$ sudo mkdir /Users/DB/postgresql

Make PostgreSQL binaries available to the user, and get it into ~/.profile to name it stick.

$ export PATH=$PATH:/opt/local/lib/postgresql82/bin
$ echo  'export PATH=$PATH:/opt/local/lib/postgresql82/bin' >> ~/.profile

Since PostgreSQL will run under postgres user, we need to make it own the postgresql directory.

$ chown -R postgres:postgres /Users/DB/postgresql

It's time to initialize PostgreSQL master database. Just a bit of explaining, we are starting PostgreSQL as user postgres, so we use sudo to elevate privileges, and su to assume the user we want, then -c tells su to execute a command. Which will be executes as user postgres

$ sudo su postgres -c "initdb -D /Users/DB/postgresql"

I hope everything is running to this point :).

Everything is ready to start PostgreSQL, but we don't want it to be a pain, starting and stopping PostgreSQL. To help out let's make some convenience aliases.

$ alias pgstart="sudo su postgres -c 'pg_ctl -D /Users/DB/postgresql -l 
/Users/DB/postgresql/logfile start'"
$ alias pgstop="sudo su postgres -c 'pg_ctl -D /Users/DB/postgresql stop -m fast'"
$ alias pgstatus="sudo su postgres -c 'pg_ctl status -D /Users/DB/postgresql'"

Also copy and paste those lines into your ~/.profile.

To finish, just some notes. PostgreSQL by default only listens for connections locallty, if you wish to enable connections on any network interface, edit /Users/DB/postgresql/postgresql.conf, uncomment listen_addresses and change to,

listen_addresses = '*'

open /Users/DB/postgresql/pg_hba.conf and add this line,

host    all         all         192.168.1.0/24        md5

of course you should changes it to meet your needs.

To edit this files i changed to user "postgres" with,

$ sudo su postgres

and used a command line editor to change them, it's more practical.

Now you should create a user, don't forget to start PostgreSQL.

• -s - is superuser
• -P - prompt for password
• -l - may login

$ sudo su postgres
$ createuser -s -P -l

We are all set with a login and password, if you created a user with the same name as your system username, you can now,

$ psql

and go into PostgreSQL console, without the need of becoming user postgres. Of course, now we can use any database management software to interact with PostgreSQL or Mysql.

As always, comments, suggestions, corrections, beer... all welcome!

Have fun!