Archive

Archive for the ‘postgresql’ Category

Install Postgres on Mac OS X Lion with homebrew howto

July 20, 2012 3 comments

1) This is the easiest part, simply

$ brew install postgres

2) this step is not obvious but you can’t move on without it (would be nice if brew can automate this step too :D)

$ initdb /usr/local/var/postgres

3) add /usr/local/bin to $PATH if it’s not already there, one way to do that is

$ echo 'export PATH=/usr/local/bin:$PATH' >> ~/.bash_profile && . ~/.bash_profile

without this step you will end up using /usr/bin/psql, which is installed by default in Lion, while trying to access db, which would give you the following error:

psql: could not connect to server: Permission denied
Is the server running locally and accepting
connections on Unix domain socket “/var/pgsql_socket/.s.PGSQL.5432”?

[added 7/26/2012]
3.5) start postgres server

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

4) access postgres db

psql postgres your_username

or

psql postgres `whoami`

assuming your_username is the one who runs step 2

5) if everything’s ok you should be able to see

psql (9.1.3)
Type “help” for help.

postgres=#

type \q to exit

6) if you want to create your first db, do (under system shell, not postgres) a

createdb mydb

and access it through

psql mydb your_username

Note: your_username in command psql is optional if the shell user is the creator of the db to be accessed, that is, you can simply type psql postgres or psql mydb to access the dbs.

Enjoy.

References:
http://archives.postgresql.org/pgsql-general/2009-09/msg00990.php
http://nextmarvel.net/blog/2011/09/brew-install-postgresql-on-os-x-lion/ (didn’t use the method describe in this post but use the PATH method instead)
http://www.postgresql.org/docs/9.0/static/tutorial-createdb.html

Categories: lion, mac osx, postgresql

So long, MySQL

November 9, 2011 Leave a comment

I’ve been playing around with PostgreSQL recently and I am total impressed by the features that PostgreSQL supports. I would like to point out the create domain statement (which confirms with SQL standard) that instantly makes me a MySQL betrayer: (from http://www.postgresql.org/docs/8.4/interactive/sql-createdomain.html)

CREATE DOMAIN us_postal_code AS TEXT
CHECK(
   VALUE ~ '^\\d{5}$'
OR VALUE ~ '^\\d{5}-\\d{4}$'
);

CREATE TABLE us_snail_addy (
  address_id SERIAL PRIMARY KEY,
  street1 TEXT NOT NULL,
  street2 TEXT,
  street3 TEXT,
  city TEXT NOT NULL,
  postal us_postal_code NOT NULL
);

The main reason: Data validation is made easy and powerful. With MySQL, data validation relies heavily on the application that interacts with the tables.

Categories: database, postgresql