OS X Lion, PostgreSQL and MacPorts install and administration cheat sheet

Since PostgreSQL is a nightmare to administer I have to put these hints down here so I never have to deal with it again 🙂

Install PostgreSQL with Macports:

sudo port install postgresql90-server

Create the default database environment

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

Start the database environment

sudo su postgres -c '/opt/local/lib/postgresql90/bin/postgres -D /opt/local/var/db/postgresql90/defaultdb'

or

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

Now lets create the database we are going to use

sudo su postgres -c '/opt/local/lib/postgresql90/bin/createdb test'

and now we want to connect to the newly created database with psql

sudo su postgres -c '/opt/local/lib/postgresql90/bin/psql test'

To add a new user to the database and give him full privileges. Open psql (like above) and type:

CREATE USER niklas WITH PASSWORD 'myPassword';
GRANT ALL PRIVILEGES ON DATABASE myDatabase to niklas;

After this the user ‘niklas’ can logon to the database with this:

/opt/local/lib/postgresql90/bin/psql test

Tested on OS X Lion (10.7.3), PostgreSQL 9.0 and MacPorts 2.0.4

  1. Install PostgreSQL with Macports:

    sudo port install postgresql90-server

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>