PostgreSQL(1)Setup PostgreSQL DB

PostgreSQL(1)Setup PostgreSQL DB

I know the related things as follow:
cassandra DB, mongo DB, redis DB
Kafka, zookeeper, rabbitMQ, spark.

I begin to learn to know about the PostgreSQL.

1. Installation
Install that on my local MAC OS from here http://www.enterprisedb.com/products-services-training/pgdownload#osx

What I got is here http://get.enterprisedb.com/postgresql/postgresql-9.3.4-1-osx.zip

Double click and install on my machine. Not good for me.

Install from Source
http://ftp.postgresql.org/pub/source/v9.3.4/postgresql-9.3.4.tar.gz

>tar zxvf postgresql-9.3.4.tar.gz
>cd postgresql-9.3.4
>./configure --prefix=/Users/carl/tool/pgsql-9.3.4
>make world
>make install-world

Make the soft link
>sudo ln -s /Users/carl/tool/pgsql-9.3.4 /opt/pgsql-9.3.4
>sudo ln -s /opt/pgsql-9.3.4 /opt/pgsql

>vi ~/.profile
export PATH=/opt/pgsql/bin:$PATH

>. ~/.profile

Verify the Installation
>postgres --version
postgres (PostgreSQL) 9.3.4

2. Prepare the Databases
>mkdir -p /Users/carl/db/postgresql

Initial the DB 
>initdb --pgdata=/Users/carl/db/postgresql
Success. You can now start the database server using:    postgres -D /Users/carl/db/postgresql or    pg_ctl -D /Users/carl/db/postgresql -l logfile start

Check status
>pg_ctl -D /Users/carl/db/postgresql status

Start the server
>pg_ctl -D /Users/carl/db/postgresql start

eg
>pg_ctl -D /database_directory -l /log_file.log start

Error Message:
FATAL:  could not open lock file "/tmp/.s.PGSQL.5432.lock": Permission denied

Solution:
>sudo rm -fr /tmp/.s.PGSQL.5432.lock

Error Message:
LOG:  could not bind Unix socket: Address already in use HINT:  Is another postmaster already running on port 5432? If not, remove socket file "/tmp/.s.PGSQL.5432" and retry. WARNING:  could not create Unix-domain socket in directory "/tmp" FATAL:  could not create any Unix-domain sockets

Solution:
>sudo rm -fr /tmp/.s.PGSQL.5432

Solved
LOG:  database system was shut down at 2014-03-21 16:59:46 CDT LOG:  autovacuum launcher started LOG:  database system is ready to accept connections

List the databases we have
>psql -l

3. Create the Right DB with Auth
List all the users on MAC System
>ls /Users

Create a user named postgres
>su postgres
>psql

Error Message:
FATAL:  role "postgres" does not exist psql: FATAL:  role "postgres" does not exist

Solution:
>createuser postgres
>createdb -Opostgres -Eutf-8 demo

Logon to the DB with the right user
>psql -U postgres demo

or 

>psql -U postgres -d demo -h localhost -p 5432

4. Some Basic Commands
List all the Databases
demo=> \l                                List of databases   Name    |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges -----------+----------+----------+-------------+-------------+------------------- demo      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

Connect to other databases
demo=> \c demo You are now connected to database "demo" as user "postgres". demo=> demo=> \c postgres You are now connected to database "postgres" as user "postgres".

SQL Command
create table, insert data and etc
>create table usertable(name VARCHAR(20), signupdate DATE);
>insert into usertable (name, signupdate ) values ('sillycat', '2014-03-24' );
>select * from usertable;

List all the tables We have
demo=> \d           List of relations Schema |   Name    | Type  |  Owner   --------+-----------+-------+---------- public | usertable | table | postgres

List the description of one Table, e.g. usertable
demo=> \d usertable            Table "public.usertable"   Column   |         Type          | Modifiers ------------+-----------------------+----------- name       | character varying(20) |  signupdate | date                  | 

List all the Users
demo=> \du                             List of roles Role name |                   Attributes                   | Member of -----------+------------------------------------------------+----------- carl      | Superuser, Create role, Create DB, Replication | {} postgres  |                                                | {}

List the Connection Info
demo=> \conninfo You are connected to database "demo" as user "postgres" on host "localhost" at port "5432".


References:
http://www.postgresql.org/
https://github.com/mauricio/postgresql-async

http://book.51cto.com/art/201201/313178.htm
http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html
http://mac-dev-env.patrickbougie.com/postgresql/
http://ionrails.com/2012/06/03/installing-postgresql-on-a-mac-lion/