Linux系统下Postgresql的安装部署及操作

一、 安装部署
1、 下载软件
从pgsql官网地址：https://www.postgresql.org/，进入后点击download就来到下载页，这里点击Linux下面的Other Linux选项，然后点击下方的tar.gz archive下载二进制归档，进入下载页面后，根据自己的机器类型选择要下载的包，本机为x86-64，下载包为postgresql-10.1-1-linux-x64-binaries.tar.gz。
2、 安装
解压数据库并放到指定目录：
tar -xvzf postgresql-10.1-1-linux-x64-binaries.tar.gz
#解压出来之后目录为pgsql
mv pgsql/ /soft/pg
现在pgsql的安装目录就是/soft/pg
创建pgsql用户并设置密码：
useradd postgres
passwd postgres
#两次输入密码并确认
注意：这里设置密码是linux用户postgres的登录密码，不是pgsql服务器的密码
创建pgsql数据目录： mkdir /soft/pg /pgsql_data
给postgres用户授权目录访问权限： chown postgres /soft/pg /pgsql_data/
然后切换到postgres用户来操作数据库，pgsql数据库就以postgres为默认用户，执行： su - postgres 切换
　切换之后建议修改~/.bash_profile将pgsql的bin目录加至环境变量中，方便直接使用pgsql相关命令，下面初始化数据库：
/soft/pg /pgsql/bin/initdb -D /soft/pg /pgsql_data/
　　等待执行完毕，没什么问题就初始化成功了，提示如下：

如图根据提示可以启动数据库： /soft/pg /pgsql/bin/pg_ctl -D /monchickey/pgsql_data/ -l logfile start 这里-l指定日志文件位置，这里直接输出在家目录下的logfile中，这个可以自己指定，这里-D指定数据目录，默认如果不加数据目录直接报错找不到，可以刚才说的环境变量配置文件中~/.bash_profile加入一行： export PGDATA=/soft/pg /pgsql_data 然后source进去即可，这样pgsql会自动去找PGDATA环境变量值，找不到才会报错

pgsql默认的端口号为5432，通过netstat命令或者lsof命令都可以看到监听情况：

停止postgresql的命令为： /soft/pg /pgsql/bin/pg_ctl -D /soft/pg /pgsql_data/ stop

3. 命令行界面简单操作
　　pgsql和mysql一样可以通过交互式提示符连接操作，连接方式如下：
./bin/psql -h 127.0.0.1 -d postgres -U postgres -p 5432
　　其中-h参数指定服务器地址，默认为127.0.0.1，默认不指定即可，-d指定连接之后选中的数据库，默认也是postgres，-U指定用户，默认是当前用户，-p 指定端口号，默认是"5432"，其它更多的参数选项可以执行： ./bin/psql --help 查看
　　登录进去默认界面如下所示：
　　

二、 postgresql命令
\h #查看所有的sql关键字
　　? #命令行操作的帮助
　　\d #查看当前schema 中所有的表
　　\q #退出pg命令行
　　\d #schema.table 查看表的结构 \d tablename
　　\x #横纵显示切换
　　\dT+ #显示扩展类型相关属性及描述
　　\dx #显示已安装的扩展插件
　　\l #列出所有的数据库
　　\timing #显示执行时间
　　\c database_name #切换数据库
　　set search to schema #切换schema
　　explain sql #解释或分析sql执行过程

.基本操作
1 登陆
超级用户登陆

su - postgres
psql template1     -- 登陆template1数据库

普通用户登陆

psql -U duangr -d template1      -- 使用duangr登陆template1数据库

2 基本查询
\l – 查看有哪些数据库
\q – 退出
\c duangr – 从之前的库切换到duangr库
\d table_name – 查看表结构
\dt – 查看有哪些表
\di – 查看索引
\du – 查看角色
通过SQL查看

– 查询系统有哪些数据库

select oid,datname from pg_database;

– 查询系统有哪些用户

select usename from pg_user;

– 查询系统有哪些角色

select rolname from pg_roles;

3 用户与角色
用户和角色类似,用户就是可以登陆的角色.

-- 创建用户 (duangr)
CREATE USER duangr WITH PASSWORD '${passwd}';
-- 在db中切换用户
\!psql -U duangr -d template1 
-- 删除用户 (duangr)
DROP USER  duangr;

-- 创建角色 (dgr_role) 
CREATE role dgr_role; 
-- 删除角色
DROP ROLE [ IF EXISTS ] name [, ...]
-- 将角色赋给其他角色或用户
GRANT role_name [, ...] TO role_or_user_name [, ...]
-- 回收角色
REVOKE role [, ...] FROM role_or_user_name [,...]
授予权限

GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] }
  ON [ TABLE ] tablename [, ...]
  TO {  rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]


GRANT { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] }

  ON SEQUENCE sequencename [, ...]
  TO {  role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
  ON DATABASE dbname [, ...]
  TO {  role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
  ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
  TO {  role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
  ON LANGUAGE langname [, ...]
  TO {  role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
  ON SCHEMA schemaname [, ...]
  TO {  role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
  ON TABLESPACE tablespacename [, ...]
  TO {  role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
回收权限

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename [, ...]
FROM {  role_or_user_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] }
ON SEQUENCE sequencename [, ...]
FROM { [  role_or_user_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
FROM { role_or_user_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
FROM {  role_or_user_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE langname [, ...]
FROM { role_or_user_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schemaname [, ...]
FROM { role_or_user_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespacename [, ...]
FROM {  role_or_user_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

4 数据库操作

-- 创建db (duangr)
create database duangr

-- 删除db (duangr)
drop database duangr

-- 建表
create table (
    [field1] [type1] <references 关联表名(关联的字段名)>,
    [field2] [type2] ,
    ...... ,
   primary key ([field1],[field2],...)
); 
-- 改表名
alter table [table_A] rename to [table_B]; 
-- 删表
drop table [table_name]

-- 修改表
-- 加字段
alter table [table_name] add column [field_name] [type]; 
-- 删字段
alter table [table_name] drop column [field_name]; 
--重命名字段
alter table [table_name] rename column [field_A] to [field_B]; 
--设置字段缺省值
alter table [table_name] alter column [field_name] set default [value]; 
--删除缺省值
alter table [table_name] alter column [field_name] drop default; 

-- 记录增删改
insert into [table_name] (field1,field2,...) values(...)
update [table_name] set field=? where ...
delete from [table_name] where ...

5 数据清理

-- 对数据库进行清理、收回磁盘空间并更新统计信息
vaccumdb -d 
    -a 对所有的数据库操作 
    -z 保证不断地删除失效的行，节约磁盘空间，将统计信息更新为最近的状态

6 备份数据库

-- 备份 duangr 库 
pg_dump sq > /export/home/sq/pgsql/backup/duangr_20131206.bak

7 导入数据
– 将文件中的数据导入到表中 (字段分隔符可以自定义,与数据文件中保持一致. 数据文件字段与表中列相对应)
copy table_name from ‘/tmp/etl/table_name.dat’ with delimiter ‘^A’
P.S. 此处使用ASCII字符 0x01 作为文件中字段分隔符,目的是避免与数据字段内容冲突.
^A 需要通过 “Ctrl+V” + “Ctrl+A” 的方式输入.