PostgreSQL

Postgres 是我最喜欢的关系型数据库，新版本更是支持JSONB等NOSQL数据库特征的数据类型，一直是GUI工具维护，有些情况下还是有些不便，现网上收集并整理了一些常用命令：

1. 系统程序：

createdb       创建一个新的数据库（同SQL：CREATE DATABASE） 
createuser     创建一个新的用户（同SQL：CREATE USER ）
dropdb         删除数据库 
dropuser       删除用户 
pg_dump        将指定数据库导出到一个脚本文件 
pg_dumpall     将所有数据库导出到一个脚本文件 
pg_restore     从一个由pg_dump或pg_dumpall程序导出的脚本文件中恢复数据库 
psql           命令行交互式客户端，如psql [database]
vacuumdb       清理和分析一个PostgreSQL数据库，它是客户端程序psql环境下SQL语句VACUUM的shell脚本封装，二者功能完全相同

pg_ctl         启动、停止、重启（如：pg_ctl start 和 service postgresql start相同） 
pg_controldata 显示PostgreSQL服务的内部控制信息 

2. psql 命令

db=# \l  查看系统中现存的数据库 
db=# \q  退出客户端程序psql 
db=# \c  从一个数据库中转到另一个数据库中，如db=# \c sales 从db转到sales 
db=# \dt 查看表列表
db=# \d  查看Object [表,序列等]
db=# \d [表名]  查看表结构 
db=# \di 查看索引

3. 基本数据库操作及DML

创建数据库：create database [数据库名]; 
删除数据库：drop database [数据库名]; 
创建表：   create table 
重命名表： alter table [表名A] rename to [表名B]; 
删除表：   drop table [表名]; 

在已有的表里添加字段： alter table [表名] add column [字段名] [类型]; 
删除字段： alter table [表名] drop column [字段名]; 
修改数据类型：alter table [表名] alter column [字段名] type [类型];
重命名字段： alter table [表名] rename column [字段名A] to [字段名B]; 
给字段设置缺省值： alter table [表名] alter column [字段名] set default [默认值]; 
去除缺省值： alter table [表名] alter column [字段名] drop default; 

4. PostgreSQL用户管理

PostgreSQL数据目录中的pg_hba.conf的作用就是用户认证，可以在/usr/local/pgsql/data中找到。
有以下几个例子可以看看：

1. 允许在本机上的任何身份连接任何数据库
   TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
   local all all trust(无条件进行连接)
2. 允许IP地址为192.168.1.x的任何主机与数据库sales连接
   TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
   host sales all 192.168.1.0 255.255.255.0 ident sameuser(表明任何操作系统用户都能够以同名数据库用户进行连接)

创建PostgreSQL数据库用户示例

1. 进入PostgreSQL高级用户 
2. 启用客户端程序，并进入template1数据库 
psql template1 
3. 创建用户 
template1=# CREATE USER hellen WITH ENCRYPED PASSWORD'zhenzhen' 
4. 因为设置了密码，所以要编辑pg_hba.conf，使用户和配置文件同步。 
在原有记录上面添加md5 
local all hellen md5 
5. 使用新用户登录数据库 
template1=# \q 
psql -U hellen -d template1 
6. 切换用户： 
template1=# \!psql -U tk -d template1

设定用户的权限

* 创建一个用户组： 
sales=# CREATE GROUP sale; 
* 添加几个用户进入该组 
sales=# ALTER GROUP sale ADD USER sale1,sale2,sale3; 
* 授予用户级sale针对表employee和products的SELECT权限 
sales=# GRANT SELECT ON employee,products TO GROUP sale; 
* 在sale中将用户user2删除 
sales=# ALTER GROUP sale DROP USER sale2;

5. 备份数据库

可以使用pg_dump和pg_dumpall来完成。比如备份sales数据库：

pg_dump sales>/home/tk/pgsql/backup/1.bak