PostgreSQL(1)Setup PostgreSQL DB
PostgreSQL(1)Setup PostgreSQL DBI 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.iyunv.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/
页:
[1]