PostgreSQL on Mac OS X

Installing

Install Server

See Install PostgreSQL using MacPorts for more information

$ sudo port install postgresql94-server
--->  Computing dependencies for postgresql94-server
--->  Dependencies to be installed: postgresql94 postgresql_select
--->  Fetching archive for postgresql_select
--->  Attempting to fetch postgresql_select-0.2_0.darwin_15.noarch.tbz2 from https://packages.macports.org/postgresql_select
--->  Attempting to fetch postgresql_select-0.2_0.darwin_15.noarch.tbz2.rmd160 from https://packages.macports.org/postgresql_select
--->  Installing postgresql_select @0.2_0
--->  Activating postgresql_select @0.2_0
--->  Cleaning postgresql_select
--->  Fetching archive for postgresql94
--->  Attempting to fetch postgresql94-9.4.8_0.darwin_15.x86_64.tbz2 from https://packages.macports.org/postgresql94
--->  Attempting to fetch postgresql94-9.4.8_0.darwin_15.x86_64.tbz2.rmd160 from https://packages.macports.org/postgresql94
--->  Installing postgresql94 @9.4.8_0
--->  Activating postgresql94 @9.4.8_0

To use the postgresql server, install the postgresql94-server port

--->  Cleaning postgresql94
--->  Fetching archive for postgresql94-server
--->  Attempting to fetch postgresql94-server-9.4.8_0.darwin_15.x86_64.tbz2 from https://packages.macports.org/postgresql94-server
--->  Attempting to fetch postgresql94-server-9.4.8_0.darwin_15.x86_64.tbz2.rmd160 from https://packages.macports.org/postgresql94-server
--->  Installing postgresql94-server @9.4.8_0
--->  Activating postgresql94-server @9.4.8_0

To create a database instance, after install do
 sudo mkdir -p /opt/local/var/db/postgresql94/defaultdb
 sudo chown postgres:postgres /opt/local/var/db/postgresql94/defaultdb
 sudo su postgres -c '/opt/local/lib/postgresql94/bin/initdb -D /opt/local/var/db/postgresql94/defaultdb' 

--->  Cleaning postgresql94-server
--->  Updating database of binaries
--->  Scanning binaries for linking errors
--->  No broken files found.

Select PostgreSQL Version

$ port select --list postgresql
Available versions for postgresql:
    none (active)
    postgresql94

$ sudo port select --set postgresql postgresql94
Selecting 'postgresql94' for 'postgresql' succeeded. 'postgresql94' is now active.

$ port select --list postgresql
Available versions for postgresql:
    none
    postgresql94 (active)

Optionally Install the Documentation

$ sudo port install postgresql94-doc

Create the database instance

 $ sudo mkdir -p /opt/local/var/db/postgresql94/defaultdb
 $ sudo chown postgres:postgres /opt/local/var/db/postgresql94/defaultdb
 $ sudo su postgres -c '/opt/local/lib/postgresql94/bin/initdb -D   /opt/local/var/db/postgresql94/defaultdb'

Start the database automatically

$ sudo port load postgresql94-server

The database will then be shutdown and restarted whenever the machine is shutdown and restarted.

To disable automatic startup:

$ sudo port unload postgresql94-server

Start the database manually

$ sudo su postgres -c '/opt/local/lib/postgresql94/bin/pg_ctl -D /opt/local/var/db/postgresql94/defaultdb -l /opt/local/var/log/postgresql94/postgres.log start'

Watch the log:

$ sudo su postgres -c 'tail -f /opt/local/var/log/postesql94/postgres.log'

Create an initial super-user

$ sudo -u postgres createuser -drs ${USERNAME}

Installing Additional Supplied Modules

See: http://lightyearsoftware.com/2010/08/adding-contrib-extensions-to-macports-postgresql/

  1. Re-install PostgreSQL using the -k keep option:

    $ sudo port -nk upgrade --force postgresql94

  2. Build and install the desired module, e.g. pgcryptto:

    $ cd `port work postgresql94`
$ cd postgresql..
$ cd pgcrypto
$ make all
$ sudo make install
$ cd /opt/local/share/postgresql94/contrib

  3. Update each database that requires the module:

    $ psql mydb <pgcrypto.sql
$ sudo port clean postgresql94

-- Frank Dean - 9 Jul 2016

Related Topics MacOSXTips, PostgreSQLmacOS, PostgreSQL