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