PosgreSQL Basic

1. Install postgresql postgresql-contrib

camoss@cd-devel15:~$ sudo apt-get install postgresql postgresql-contrib

This will install the latest version available in your Ubuntu release and the commonly used add-ons for it.

2. Connect to PosgreSQL server

camoss@cd-devel15:~$ sudo -u <user> psql <database>

Note: 大多数PostgreSQL都会利用现有的Linux系统账户来认证PostgreSQL，所以，在安装PostgreSQL时，系统上必须有一个postgres的账户。
postgres账户作为PostgreSQL的管理员账户，而不是root。
基于PostgreSQL这种账户管理，我们必须以Linux账户postgres(或自己为Linux和PostgreSQL创建好的账户)来访问PostgreSQL服务器。
因此，你将看到PostgreSQL的管理员账户postgres在文件/etc/passwd中。
camoss@cd-devel15:~$ 
camoss@cd-devel15:~$ grep -i "postgres" /etc/passwd
postgres:x:113:122:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
camoss@cd-devel15:~$ 

e.g.
camoss@cd-devel15:~$ sudo -u postgres psql postgres

3. Set password for role/user of PostgreSQL

postgres=# \password <user>

e.g. 
Set a password for the "postgres" database role
postgres=# \password postgres

pwd: postgres_123

4. Show help information 

Show help information about the backslash commands

postgres=# \?

Show help information about SQL commands

postgres=# \h

5. Exit PosgreSQL server

Control+D
or
postgres=# \q

6. Create database

To create the first database, which we will call "demodb", simply type:
sudo -u postgres createdb demodb
or
camoss@cd-devel15:~$ sudo -u postgres psql postgres
postgres=# CREATE DATABASE testdb;

7. List all Databases

List the names, owners, character set encodings, and access privileges of all the databases in the server. 
If + is appended to the command name, database sizes, default tablespaces, and descriptions are also displayed. 
(Size information is only available for databases that the current user can connect to.)

postgres=# \l+ (or \list+)

8. Connect to Database

postgres=# \c or \connect [ dbname [ username ] [ host ] [ port ] ]

9. Create linux user for PosgreSQL

camoss@cd-devel15:~$ adduser demouser

pwd: demopwd

10. Create/Drop database role/user

camoss@cd-devel15:~$ sudo -u postgres createuser demouser

camoss@cd-devel15:~$ sudo -u postgres dropuser demouser

or
postgres=# CREATE ROLE demouser;
postgres=# ALTER ROLE demouser login;
or

postgres=# CREATE ROLE demouser login;

Note: 我试了一下sudo -u postgres createuser <user/role> 和 CREATE ROLE <role/user> login 创建的user/role效果一样。

可以通过\du或\dg来查看database roles/users

e.g.

Creates the user with no database creation rights (-D) with no add user rights (-A) and will prompt you for entering a password (-P)

camoss@cd-devel15:~$ sudo -u postgres createuser -D -A -P myuser

Create the database 'mydb' with 'myuser' as owner

camoss@cd-devel15:~$ sudo -u postgres createdb -O myuser mydb

11. Alter database

postgres=# ALTER DATABASE name RENAME TO new_name
postgres=# ALTER DATABASE name OWNER TO new_owner
postgres=# ALTER DATABASE name SET TABLESPACE new_tablespace

e.g.
demodb=# 
demodb=# ALTER DATABASE demodb OWNER TO demouser;
ALTER DATABASE
demodb=# 
demodb=# \l

12. Connect to database(demodb) of PosgreSQL serveras above user(demouser)

Note:
database "demodb" --> which is created by above step 7
user "demouser" --> which is created by above steps 10 and 11

e.g.
camoss@cd-devel15:~$ 
camoss@cd-devel15:~$ sudo -u demouser psql demodb
psql (9.3.6)
Type "help" for help.
demodb=> 
demodb=> 

13. List Users and Roles

\du[+] [ pattern ]
Lists database roles. (Since the concepts of "users" and "groups" have been unified into "roles", this command is now equivalent to \dg.) 
If pattern is specified, only those roles whose names match the pattern are listed. 
If the form \du+ is used, additional information is shown about each role; currently this adds the comment for each role.

\dg[+] [ pattern ]
Lists database roles. (Since the concepts of "users" and "groups" have been unified into "roles", this command is now equivalent to \du.) 
If pattern is specified, only those roles whose names match the pattern are listed. 

If the form \dg+ is used, additional information is shown about each role; currently this adds the comment for each role.

14. Enable PostgreSQL to listen across different networks

Edit file /etc/postgresql/9.3/main/postgresql.conf, and alter the listen_addresses 
e.g. Listen on all network interfaces as follow:
listen_addresses = '*'

15. Reload/Restart PostgreSQL Server

Configuring the networking / users may need to reload the server

camoss@cd-devel15:~$ sudo /etc/init.d/postgresql reload

Some settings changes in postgresql.conf require a full restart

camoss@cd-devel15:~$ sudo /etc/init.d/postgresql restart