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