postgreSQL

osx 10.10.1 | Jan 14, 2015
PostgreSQL the standard database server for many production web applications. Not to mention required by Heroku.

---

##postgress commands createdb <dbname>
dropdb <dbname>

##psql commands
###unix output
psql -d <dbname> enter a psql session in a given db.
psql -l List available databases
###interactive psql session
\d prints out tables \d <table_name> prints out columes of the specified table.
\l List available databasses.
\d

---

##Install Postgres Postgres can by installed and administrated in several ways. Each instalation reqires a slightly different configuration and admin tool(s).

• The Postgres.app which has an independently developed but purpose built admin app clled PG Commander.
• Install postgres via Homebrew which comes with the native CLI(command line tool) psql.
• Site install Packages.

####Starting the Postgres Server Fallowing the install of Postgres you must start the Postgres server. This is true for all of the verious installations of Postgres I know of. Not having this running is the soure of many first time postgres users difficulties.

The caveats shown in the terminal after the Homebrew installation is complete document the commands to start and link to auto-start at login. I keep my server runnng all the time. When not in use it takes up very few system resources.

Alternatively their is a gem called [launchy] which gives you a simple commands to start and stop the server. (lunchy start postgres, unchy stop postgres)

The best way I know of to confirm whether an instance of postgres is running is to use the Activity Monitor located under Applicatin/Utilitys/ActivityMonitor.

---

###Install using Homebrew
Brew install Postgres is my first choise. I find this the most direct way to get up and running. Also this method seams to work perticularly well for Rails applications.

Asumming you have xcode, curL, and Homebrew
enter:

brew install postgres

After it finishes READ the caveats!!!

What you need to do fallowing the instalation is link the Postgres application plist file to the system LaunchAgnet. To do this first we need to make the LaunchAgent directory.
From the caveats,
enter:

mkdir -p ~/Library/LaunchAgents

Second we will create the link to LaunchAgent directory. Again from the caveats,
enter:

ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents

This third command will start the server imediatly. After this if you chouse to have it start when you login it will be their for you with no more effort needed. (If your using Launchy you don't need to run this command.)
enter:

launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Once all that is done enter which psql. Were looking for this /usr/local/bin/psql which tells us that were using the Homebrew version.

If this is your first time installing postgres using homebrew you need to create a datatbase. Enter

initdb `whoami`

##Usage ###Save a data set:

pg_dump -O -F t <dbmane> > <dbname_dump>

Restore: Be in the directory with dump_file.

pg_restore -C -d <database_name_to_revive_data> <name_of_saved_data_set>