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 herehttp://www.enterprisedb.com/products-services-training/pgdownload#osx

What I got is herehttp://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 namedpostgres
>supostgres
>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 relationsSchema | 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 rolesRole 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/