Postgres SQL Up and Running on OSX using Homebrew

Need to have a postgres instance set up in addition to your normal MySQL setup locally?  This example assumes you are using PHP and Apache, though some of it likely works outside of those parameters.

Start with updating Homebrew

brew update

Install PostgresQL

brew install postgresql

Now install the PHP PDO package.  You will want first figure out which version of PHP you are using (php -v on the cli) and select the correct PDO package.  The example below is using PHP 5.5.x.  NOTE: this should not mess up your existing install of PHP.  Homebrew is refined enough to simply add the necessary options to your PHP installation rather than replace them.

brew install php55-pdo-pgsql

I only need to have Postgres running temporarily, so I didn't set up a launchd task or anything.  Which is what you need to do if you want it always running in the background.  To just kick it off and have it run for the time being you can execute

postgres -D /usr/local/var/postgres

This will leave you attached to the process on your command line.  So you need to put it in the background by hitting <CTRL+Z> and then executing bg at the next shell prompt.  You should get notification that the process is running in the background.

Now for creating a database.  This is easy...

createdb mydbname

If you have a dump of a db you need to load up.  You take the dump file and run...

psql mydbname < mydbname.bak.sql

Once this is created you can hop in to the PSQL shell and start querying.  To launch the shell.

psql mydbname

User management was a little unclear to me.  When following the instructions above you will find yourself with a user named the same as your OSX user name.  If you want add another user and you wish to use for development you can create one OUTSIDE the PSQL shell using a command called 'createuser'.  It should prompt you for password, etc.  There are other setting you can use of course for specifying specifics.  You can see those here.  You can also use SQL to do all this as you would in MySQL, and this is probably the best way to alter your user if you want some things changed after initial creation.

createuser myusername

I have to admit, I feel silly getting into any more specifics about managing priviledges.  But from what little I have done, I know you can grant your user priviledges to the using something like...  The SCHEMA you'll reference is most likely 'public'.  Postgres using the term schema and database a little differently than mysql.  You create a database, and usually within that database is a schema called  public that contains the tables.

GRANT ALL PRIVILEGES ON ALL TABLES IN schema TO mydbname;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN schema TO mydbname;

There may be a better way (it seems like there should be) to grant all priviledges to a specified user, which would be especially helpful when simply setting something up locally to develop with.  If you know of a better way, let me know in the comments.

OSX UI

For mySql, sequel pro seems to be the best GUI available.  While I've not found something exactly like that for postgresql.  There is a very nice lightweight free app called PSequel. That allows you to browse your local databases.  Another note worth mentioning is that you do not need to specify a port when using this app, the default is fine... and if the user you have set up doesn't have a password, then no need to enter a password either.  Basically just a user and database name.

Drupal

If you are using drupal and need to switch between postgresql and mysql, you should definitely look into the DBTNG Migrator project.  Because of drupal's database abstraction layer this makes easy work of moving the database between server types.