postgresql常用命令讲解.docx
《postgresql常用命令讲解.docx》由会员分享，可在线阅读，更多相关《postgresql常用命令讲解.docx（13页珍藏版）》请在冰点文库上搜索。
postgresql常用命令讲解
1.createdb数据库名称
产生数据库
2.dropdb 数据库名称
删除数据库
3.CREATEUSER用户名称
创建用户
4.dropUser用户名称
删除用户
5.SELECTusenameFROMpg_user;
查看系统用户信息
\du
7.SELECTversion（）;
查看版本信息
8.psql数据库名
打开psql交互工具
9.mydb=>\ibasics.sql
\i命令从指定的文件中读取命令。
10.COPYweatherFROM'/home/user/weather.txt';
批量将文本文件中内容导入到wether表
11.SHOWsearch_path;
显示搜索路径
12.创建用户
CREATEUSER用户名WITHPASSWORD'密码'
13.创建模式
CREATESCHEMAmyschema;
14.删除模式
DROPSCHEMAmyschema;
15.查看搜索模式
SHOWsearch_path;
16.设置搜索模式
SETsearch_pathTOmyschema,public;
17.创建表空间
createtablespace表空间名称location'文件路径';
18.显示默认表空间
showdefault_tablespace;
19.设置默认表空间
setdefault_tablespace=表空间名称;
20.指定用户登录
psqlMTPS -u
21.显示当前系统时间、
now（）
22.配置plpgsql语言
CREATELANGUAGE'plpgsql'HANDLERplpgsql_call_handler
23.删除规则
DROPRULEnameONrelation[CASCADE|RESTRICT]
输入
name
要删除的现存的规则．
relation
该规则应用的关系名字（可以有大纲修饰）．
CASCADE
自动删除依赖于此规则的对象。
RESTRICT
如果有任何依赖对象，则拒绝删除此规则。
这个是缺省。
24.日期格式函数
select'P'||to_char（current_date,'YYYYMMDD'）||'01'
25.产生组
CreateGroup组名称
26.修改用户归属组
AlterGroup组名称adduser用户名称
26.为组赋值权限
grant操作On表名称 togroup组名称：
27.创建角色
CreateRole角色名称
28.删除角色
DropRole角色名称
29.获得当前postgresql版本
SELECTversion（）;
30.在linux中执行计划任务
通过crontab执行
suroot-c"psql-p5433-UdeveloperMTPS-c'selecttest（）'"
developer用户的密码存储于环境变量PGPASSWORD中。
31.查询表是否存在
select*frompg_statio_user_tableswhererelname='你的表名';
32.为用户复制SCHEMA权限
grantallonSCHEMA作用域名称to用户名称
33.整个数据库导出
pg_dumpall-D-p端口号-h服务器IP-Upostgres（用户名）>/home/xiaop/all.bak
34.数据库备份恢复
psql-h192.168.0.48-p5433 -Upostgres
35.当前日期函数
current_date
36.返回第十条开始的5条记录
select * from tabname limit 5 offset 10;
37.为用户赋模式权限
Grant onschemadeveloper toUDataHouse
38.将字符转换为日期时间
select to_timestamp（'2010-10-2112:
31:
22','YYYY-MM-DDhh24:
mi:
ss'）
39.数据库备份
pg_dumpall -h192.168.0.4-p5433-Upostgres >/DataBack/Postgresql2010012201.dmp
如8.1以后多次输入密码
40.\dn
查看schema
41.删除schema
dropschema_clustertestcascade;
42.导出表
./pg_dump-p端口号-U用户-t表名称-f备份文件位置数据库 ;
43.字符串操作函数
selectdistinct（split_part（ip,'.',1）||'.'||split_part（ip,'.',2））fromt_t_useriporderby（split_part（ip,'.',1）||'.'||split_part（ip,'.',2））;
44.删除表主键
altertable表名dropCONSTRAINT主键名称;
45.创建表空间
createtablespace空间名称location'路径'
46.查看表结构
select*frominformation_schema.columns
./postgres-D/usr/local/src/data
or
./pg_ctl-D/usr/local/src/data-llogfilestart
47.查看数据库大小
SELECTpg_size_pretty（pg_database_size（'MTPS'））Asfulldbsize;
48.查看数据库表大小
SELECTpg_size_pretty（pg_total_relation_size（'developer.t_L_collectfile'））Asfulltblsize,
pg_size_pretty（pg_relation_size（'developer.t_L_collectfile'））Asjustthetblsize
49.设置执行超过指定秒数的sql语句输出到日志
log_min_duration_statement=3
50.超过一定秒数sql自动执行执行计划
shared_preload_libraries='auto_explain'
custom_variable_classes='auto_explain'
auto_explain.log_min_duration=4s
51.数据库备份
selectpg_start_backup（'backupbaseline'）;
selectpg_stop_backup（）;
recovery.conf
restore_command='cp/opt/buxlog/%f%p'
52.重建索引
REINDEX{INDEX|TABLE|DATABASE|SYSTEM}name[FORCE]
INDEX
重新建立声明了的索引。
TABLE
重新建立声明的表的所有索引。
如果表有个从属的"TOAST"表，那么这个表也会重新索引。
DATABASE
重建当前数据库里的所有索引。
除非在独立运行模式下，会忽略在共享系统表上的索引（见下文）。
SYSTEM
在当前数据库上重建所有系统表上的索引。
不会处理在用户表上的索引。
另外，除了是在单主机模式下，共享的系统表也会被忽略（见下文）。
name
需要重建索引的索引，表或者数据库的名称。
表和索引名可以有模式修饰。
目前，REINDEXDATABASE和REINDEXSYSTEM只能重建当前数据库的索引，因此其参数必须匹配当前数据库的名字。
FORCE
这是一个废弃的选项，如果声明，会被忽略。
54.数据字典查看表结构
SELECTcolumn_name,data_typefrominformation_schema.columnswheretable_name='blog_sina_content_train';
52.查看被锁定表
SELECTpg_class.relnameAStable,pg_database.datnameASdatabase, pid,mode,granted
FROMpg_locks,pg_class,pg_database
WHEREpg_locks.relation=pg_class.oid
ANDpg_locks.database=pg_database.oid;
53.查看客户端连接情况
SELECTclient_addr,client_port,waiting,query_start,current_queryFROMpg_stat_activity;
54.常看数据库.conf配置
showall
55.修改数据库postgresql.conf参数
修改postgresql.conf内容
pg_ctlreload
56.回滚日志强制恢复
pg_resetxlog-f数据库文件路径
idvalue |remark
----------+--------
33953557|inser
57.当前日期属于一年中第几周
selectEXTRACT（weekfromTIMESTAMP'2010-10-22'）;
58.显示最近执行命令
\s
I.SQL命令
ABORT—退出当前事务
ALTERAGGREGATE—修改一个聚集函数的定义
ALTERCONVERSION—修改一个编码转换的定义
ALTERDATABASE—修改一个数据库
ALTERDOMAIN—改变一个域的定义
ALTERFUNCTION—修改一个函数的定义
ALTERGROUP—修改一个用户组
ALTERINDEX—改变一个索引的定义
ALTERLANGUAGE—修改一个过程语言的定义
ALTEROPERATOR—改变一个操作符的定义
ALTEROPERATORCLASS—修改一个操作符表的定义
ALTERROLE—修改一个数据库角色
ALTERSCHEMA—修改一个模式的定义
ALTERSEQUENCE—更改一个序列生成器的定义
ALTERTABLE—修改表的定义
ALTERTABLESPACE—改变一个表空间的定义
ALTERTRIGGER—改变一个触发器的定义
ALTERTYPE—改变一个类型的定义
ALTERUSER—改变数据库用户帐号
ANALYZE—收集与数据库有关的统计
BEGIN—开始一个事务块
CHECKPOINT—强制一个事务日志检查点
CLOSE—关闭一个游标
CLUSTER—根据一个索引对某个表集簇
COMMENT—定义或者改变一个对象的评注
COMMIT—提交当前事务
COMMITPREPARED—提交一个早先为两阶段提交准备好的事务
COPY—在表和文件之间拷贝数据
CREATEAGGREGATE—定义一个新的聚集函数
CREATECAST—定义一个用户定义的转换
CREATECONSTRAINTTRIGGER—定义一个新的约束触发器
CREATECONVERSION—定义一个新的的编码转换
CREATEDATABASE—创建新数据库
CREATEDOMAIN—定义一个新域
CREATEFUNCTION—定义一个新函数
CREATEGROUP—定义一个新的用户组
CREATEINDEX—定义一个新索引
CREATELANGUAGE—定义一种新的过程语言
CREATEOPERATOR—定义一个新的操作符
CREATEOPERATORCLASS—定义一个新的操作符表
CREATEROLE—defineanewdatabaserole
CREATERULE—定义一个新的重写规则
CREATESCHEMA—定义一个新的模式
CREATESEQUENCE—创建一个新的序列发生器
CREATETABLE—定义一个新表
CREATETABLEAS—从一条查询的结果中定义一个新表
CREATETABLESPACE—定义一个新的表空间
CREATETRIGGER—定义一个新的触发器
CREATETYPE—定义一个新的数据类型
CREATEUSER—创建一个新的数据库用户帐户
CREATEVIEW—定义一个视图
DEALLOCATE—删除一个准备好的查询
DECLARE—定义一个游标
DELETE—删除一个表中的行
DROPAGGREGATE—删除一个用户定义的聚集函数
DROPCAST—删除一个用户定义的类型转换
DROPCONVERSION—删除一个用户定义的编码转换
DROPDATABASE—删除一个数据库
DROPDOMAIN—删除一个用户定义的域
DROPFUNCTION—删除一个函数
DROPGROUP—删除一个用户组
DROPINDEX—删除一个索引
DROPLANGUAGE—删除一个过程语言
DROPOPERATOR—删除一个操作符
DROPOPERATORCLASS—删除一个操作符表
DROPROLE—删除一个数据库角色
DROPRULE—删除一个重写规则
DROPSCHEMA—删除一个模式
DROPSEQUENCE—删除一个序列
DROPTABLE—删除一个表
DROPTABLESPACE—删除一个表空间
DROPTRIGGER—删除一个触发器定义
DROPTYPE—删除一个用户定义数据类型
DROPUSER—删除一个数据库用户帐号
DROPVIEW—删除一个视图
END—提交当前的事务
EXECUTE—执行一个准备好的查询
EXPLAIN—显示语句执行规划
FETCH—用游标从查询中抓取行
GRANT—定义访问权限
INSERT—在表中创建新行
LISTEN—监听一个通知
LOAD—装载或重载一个共享库文件
LOCK—明确地锁定一个表
MOVE—重定位一个游标
NOTIFY—生成一个通知
PREPARE—创建一个准备好的查询
PREPARETRANSACTION—为当前事务做两阶段提交的准备
REINDEX—重建索引
RELEASESAVEPOINT—删除一个前面定义的保存点
RESET—把一个运行时参数值恢复为缺省值
REVOKE—删除访问权限
ROLLBACK—退出当前事务
ROLLBACKPREPARED—取消一个早先为两阶段提交准备好的事务
ROLLBACKTO—回滚到一个保存点
SAVEPOINT—在当前事务里定义一个新的保存点
SELECT—从表或视图中取出若干行
SELECTINTO—从一个查询的结果中定义一个新表
SET—改变运行时参数
SETCONSTRAINTS—设置当前事务的约束检查模式
SETROLE—setthecurrentuseridentifierofthecurrentsession
SETSESSIONAUTHORIZATION—为当前会话设置会话用户标识符和当前用户标识符
SETTRANSACTION—设置当前事务的特性
SHOW—显示运行时参数的数值
STARTTRANSACTION—开始一个事务块
TRUNCATE—清空一个或者一堆表
UNLISTEN—停止监听通知信息
UPDATE—更新一个表中的行
VACUUM—垃圾收集以及可选地分析一个数据库
II.客户端应用
clusterdb—对一个PostgreSQL数据库进行建簇
createdb—创建一个新的PostgreSQL数据库
createlang—定义一种新的PostgreSQL过程语言
createuser—定义一个新的PostgreSQL用户帐户
dropdb—删除一个现有PostgreSQL数据库
droplang—删除一种PostgreSQL过程语言
dropuser—删除一个PostgreSQL用户帐户
ecpg—嵌入的SQLC预处理器
pg_config—检索已安装版本的PostgreSQL的信息
pg_dump—将一个PostgreSQL数据库抽出到一个脚本文件或者其它归档文件中
pg_dumpall—抽出一个PostgreSQL数据库集群到脚本文件中
pg_restore—从一个由pg_dump创建的备份文件中恢复PostgreSQL数据库。
psql—PostgreSQL交互终端
vacuumdb—收集垃圾并且分析一个PostgreSQL数据库
III.PostgreSQL服务器应用
initdb—创建一个新的PostgreSQL数据库集群
ipcclean—从失效的PostgreSQL服务器中删除共享内存和信号灯
pg_controldata—显示一个PostgreSQL集群的控制信息
pg_ctl—启动，停止和重起PostgreSQL
pg_resetxlog—重置一个PostgreSQL数据库集群的预写日志以及其它控制内容
postgres—以单用户模式运行一个PostgreSQL服务器
postmaster—PostgreSQL多用户数据库服务器
59.导出数据库角色
/data/pgsql/bin/pg_dumpall-p5432-Upostgres-r>/tmp/postgres_8.3_role.bak
60.修改sequence所有者
grantallonsequence名称to所有者;
61.修改sequence初始值
Alter SEQUENCE sequencename START value;
62.查看sequence当前值
SELECT currval（'sequencename'）;
63.查看sequence下一值
SELECTnextval（'sequencename'）;
64.设置sequence当前值
alterSEQUENCE sequencename restartwith startvalue;
SELECTnextval（'sequencename'）;
65.查询表结构
SELECTa.attnum,a.attnameASfield,t.typnameAStype,a.attlenASlength,a
.atttypmodASlengthvar,a.attnotnullASnotnull
FROMpg_classc,pg_attributea,pg_typet
WHEREc.relname=表名称and a.attnum>0 and a.attrelid=c.oidanda
.atttypid=t.oid
66.将查询结果直接输出到文件
在psql中
\o文件路径
selectdatname,rolnamefrompg_database aleftouterjoinpg_rolesbona.datdba=b.oid;
\o
67.查询数据库所有则
selectdatname,rolnamefrompg_database aleftouterjoinpg_rolesbona.datdba=b.oid;
68.结束正在执行的事务
SELECT *from pg_stat_activity;
selectpg_cancel_backend（'procpid'）;
60.结束session
SELECT *from pg_stat_activity;
select pg_terminate_backend（'procpid'）;
61.postgresql取消转义字符功能
将postgresql.conf文件中的standard_conforming_strings设置为on
62.查询正在执行SQL
SELECT
procpid,
start,
now（）-startASlap,
current_query
FROM
（SELECT
backendid,
pg_stat_get_backend_pid（S.backendid）ASprocpid,
pg_stat_get_backend_activity_start（S.backendid）ASstart,
pg_stat_get_backend_activity（S.backendid）AScurrent_query
FROM
（SELECTpg_stat_get_backend_idset（）ASbackendid）ASS
）ASS
WHERE
current_query<>''
ORDERBY
lapDESC;
升级会员 