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
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
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".