Installing PostgreSQL on a Mac (Mac OS X) tutorial, Introduction on How to run create and run a PostgreSQL database, etc.

20150823 Update

I found myself looking up this previous tweet on my twitter:

because I wanted to lookup some very basic commands with postgreSQL. Hope this link is useful for that:
http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/

Create a new database and assign it an owner

createdb -O yourusername yourdatabase
(Replace yourusername and yourdatabase with the name of the owner, and your database’s name, respectively)

Donate Button with Credit Cards If you like my notes and github repositories, please consider donating to me on PayPal (it’s easy! All you need is an email and to press the PayPal donate button) or at my ernestyalumni.tilt.com crowdfunding campaign. I am committed and on principle, I will keep everything I make and put out open-sourced, open and publicly available to anyone, anywhere, anytime; your help, even as little as $5, would help me create even more value (see the Tilt campaign for my mission and plans and projects to execute).


I want to outline the steps I took to get postgreSQL to run on a Mac (Mac OS X 10.10.4) and create a working (so far) database called “test.”

From Russ Brooks’ webpage:
http://www.russbrooks.com/2010/11/25/install-postgresql-9-on-os-x

1. Install PostgreSQL using Homebrew.

“You’re not still using MacPorts, are you? Please go get Homebrew immediately, the new-hotness package manager for OS X written in Ruby.” -Russ Brooks

Install PostgreSQL

brew update
brew install postgresql

2. Follow the instructions at the end of the install to initialize the DB, add startup items, and start Postgres.

For me, it was:

initdb /usr/local/var/postgres
cp /usr/local/Cellar/postgresql/9.4.4/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Caveats:
-9.4.4, the version number naming the directory, might be different for you. Check the version of PostgreSQL you downloaded and installed.
-If LaunchAgents is already there in the user’s (that ~ tilde thing) Library directory, as happened to me, I deleted it (use rm in the command prompt) and made the directory in Library (mkdir ~/Library/LaunchAgents/)

If on your Mac, you are logged in on an admin account and you want to use this account to do stuff on PostgreSQL, then go ahead.

However, I wanted to use PostgreSQL on a user account that does not have admin privileges. So I needed to create this user.

3. createuser

I used the command
createuser
that should work after brew installing PostgreSQL. cf. http://www.postgresql.org/docs/9.1/static/app-createuser.html
Read that webpage. I created a user that is a superuser and that can create databases with these flags (those options with a dash first, -, and then a letter)

createuser -P -s -e -d iamauser

where iamauser is your choice of name.

You’ll be (at least for me) prompted for your password.

4. Creating databases

You’ll have to create a database in the command prompt (Terminal). createdb should run.
cf. http://www.postgresql.org/docs/9.4/static/app-createdb.html
As you can see, there are plenty of options, flags, to try out (if you’re an expert at this, please give me more examples of usage than the webpage shows). I did this at the command prompt:

createdb test

5. SQLAlchemy to test the database, “test”, out

AT THIS POINT, this is specific to Python users of SQLAlchemy. This is my personal choice of the best SQL wrapper (wrapper, I think, is a term for a program that makes Python(ic) objects out of SQL things) ever made (shout out to Mike Bayer for creating SQLAlchemy).

Go through the SQLAlchemy Quick tutorial (the ORM Object Relational M.) and test it out. Of note, to create_engine create the engine, “connecting up” to that test PostgreSQL database, I did this:

from sqlalchemy import create_engine
engine = create_engine("postgresql://iamauser:thatpasswordyouentered@localhost/test")

where
iamauser
is your user name and

thatpasswordyouentered

is your password.

EY : 20150709 It concerns me that you are entering your password as a Python string as clear as day. Could someone let me know how to deal with security issues involving entering your password as a Python string? 2:00 pm PST update I’m going to look into using keyring at
https://pypi.python.org/pypi/keyring

Then I added users (after making a User class, as outlined in the SQLAlchemy documentation) to the databases and queried for them as SQLAlchemy outlined.

20150823 Update

I found these 2 questions on stackexchange.com useful:

package manager failed to create the database named $user for you(!!!)

http://stackoverflow.com/questions/17633422/psql-fatal-database-user-does-not-exist

How do I list all databases and tables using psql?

http://dba.stackexchange.com/questions/1285/how-do-i-list-all-databases-and-tables-using-psql

And it should work good now; you can see the databases that exists already and check the owners’ permission.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s