postgres常用命令.docx

1、postgres常用命令postgresql常用命令 2011-01-19 13:35:57| 分类： postgresql |字号 订阅1.createdb 数据库名称 产生数据库2.dropdb 数据库名称 删除数据库3.CREATE USER 用户名称 创建用户4.drop User 用户名称 删除用户5.SELECT usename FROM pg_user; 查看系统用户信息 du7.SELECT version(); 查看版本信息8.psql 数据库名 打开psql交互工具9.mydb= i basics.sqli 命令从指定的文件中读取命令。10.COPY weather FRO

2、M /home/user/weather.txt; 批量将文本文件中内容导入到wether表11.SHOW search_path; 显示搜索路径12.创建用户 CREATE USER 用户名 WITH PASSWORD 密码13.创建模式 CREATE SCHEMA myschema;14.删除模式 DROP SCHEMA myschema;15.查看搜索模式 SHOW search_path;16.设置搜索模式 SET search_path TO myschema,public;17.创建表空间 create tablespace 表空间名称 location 文件路径;18.显示默认表

3、空间 show default_tablespace;19.设置默认表空间 set default_tablespace=表空间名称;20.指定用户登录 psql MTPS -u21.显示当前系统时间、 now()22.配置plpgsql语言CREATE LANGUAGE plpgsql HANDLER plpgsql_call_handler23.删除规则DROP RULE name ON relation CASCADE | RESTRICT 输入name要删除的现存的规则relation该规则应用的关系名字(可以有大纲修饰)CASCADE自动删除依赖于此规则的对象。RESTRICT如果有

4、任何依赖对象，则拒绝删除此规则。这个是缺省。24.日期格式函数 select P|to_char(current_date,YYYYMMDD)|0125.产生组 Create Group 组名称26.修改用户归属组 Alter Group 组名称 add user 用户名称26.为组赋值权限 grant 操作 On 表名称 to group 组名称：27.创建角色 Create Role 角色名称28.删除角色 Drop Role 角色名称29.获得当前postgresql版本 SELECT version();30.在linux中执行计划任务 通过crontab执行 su root -c p

5、sql -p 5433 -U developer MTPS -cselect test() developer用户的密码存储于环境变量PGPASSWORD中。31.查询表是否存在 select * from pg_statio_user_tables where relname=你的表名;32.为用户复制SCHEMA权限 grant all on SCHEMA 作用域名称 to 用户名称33.整个数据库导出 pg_dumpall -D -p 端口号 -h 服务器IP -U postgres(用户名) /home/xiaop/all.bak34.数据库备份恢复 psql -h 192.168.0

6、.48 -p 5433 -U postgres /DataBack/Postgresql2010012201.dmp 如8.1以后多次输入密码40.dn 查看schema41.删除schema drop schema _clustertest cascade;42.导出表 ./pg_dump -p 端口号 -U 用户 -t 表名称 -f 备份文件位置 数据库 ;43.字符串操作函数select distinct(split_part(ip,.,1)|.|split_part(ip,.,2) from t_t_userip order by (split_part(ip,.,1)|.|split

7、_part(ip,.,2);44.删除表主键alter table 表名 drop CONSTRAINT 主键名称;45.创建表空间create tablespace 空间名称 location 路径46.查看表结构select * from information_schema.columns ./postgres -D /usr/local/src/dataor ./pg_ctl -D /usr/local/src/data -l logfile start47.查看数据库大小SELECT pg_size_pretty(pg_database_size(MTPS) As fulldbsiz

8、e;48.查看数据库表大小SELECT pg_size_pretty(pg_total_relation_size(developer.t_L_collectfile) As fulltblsize,pg_size_pretty(pg_relation_size(developer.t_L_collectfile) As justthetblsize49.设置执行超过指定秒数的sql语句输出到日志log_min_duration_statement = 350.超过一定秒数sql自动执行执行计划shared_preload_libraries = auto_explaincustom_vari

9、able_classes = auto_explainauto_explain.log_min_duration = 4s51.数据库备份 select pg_start_backup(backup baseline); select pg_stop_backup(); recovery.conf restore_command=cp /opt/buxlog/%f %p52.重建索引 REINDEX INDEX | TABLE | DATABASE | SYSTEM name FORCE INDEX 重新建立声明了的索引。 TABLE 重新建立声明的表的所有索引。如果表有个从属的TOAST表，

10、那么这个表也会重新索引。 DATABASE 重建当前数据库里的所有索引。 除非在独立运行模式下，会忽略在共享系统表上的索引（见下文）。 SYSTEM 在当前数据库上重建所有系统表上的索引。不会处理在用户表上的索引。 另外，除了是在单主机模式下，共享的系统表也会被忽略（见下文）。 name 需要重建索引的索引，表或者数据库的名称。 表和索引名可以有模式修饰。 目前，REINDEX DATABASE 和 REINDEX SYSTEM 只能重建当前数据库的索引， 因此其参数必须匹配当前数据库的名字。 FORCE 这是一个废弃的选项，如果声明，会被忽略。54.数据字典查看表结构SELECT colum

11、n_name, data_type from information_schema.columns where table_name = blog_sina_content_train; 52.查看被锁定表SELECT pg_class.relname AS table, pg_database.datname AS database, pid, mode, grantedFROM pg_locks, pg_class, pg_databaseWHERE pg_locks.relation = pg_class.oidAND pg_locks.database = pg_database.oi

12、d;53.查看客户端连接情况SELECT client_addr ,client_port,waiting,query_start,current_query FROM pg_stat_activity;54.常看数据库.conf配置show all55.修改数据库postgresql.conf参数修改postgresql.conf内容pg_ctl reload56.回滚日志强制恢复pg_resetxlog -f 数据库文件路径 idvalue | remark-+- 33953557 | inser57.当前日期属于一年中第几周select EXTRACT(week from TIMESTA

13、MP 2010-10-22);58.显示最近执行命令sI. SQL 命令ABORT 退出当前事务ALTER AGGREGATE 修改一个聚集函数的定义ALTER CONVERSION 修改一个编码转换的定义ALTER DATABASE 修改一个数据库ALTER DOMAIN 改变一个域的定义ALTER FUNCTION 修改一个函数的定义ALTER GROUP 修改一个用户组ALTER INDEX 改变一个索引的定义ALTER LANGUAGE 修改一个过程语言的定义ALTER OPERATOR 改变一个操作符的定义ALTER OPERATOR CLASS 修改一个操作符表的定义ALTER R

14、OLE 修改一个数据库角色ALTER SCHEMA 修改一个模式的定义ALTER SEQUENCE 更改一个序列生成器的定义ALTER TABLE 修改表的定义ALTER TABLESPACE 改变一个表空间的定义ALTER TRIGGER 改变一个触发器的定义ALTER TYPE 改变一个类型的定义ALTER USER 改变数据库用户帐号ANALYZE 收集与数据库有关的统计BEGIN 开始一个事务块CHECKPOINT 强制一个事务日志检查点CLOSE 关闭一个游标CLUSTER 根据一个索引对某个表集簇COMMENT 定义或者改变一个对象的评注COMMIT 提交当前事务COMMIT PR

15、EPARED 提交一个早先为两阶段提交准备好的事务COPY 在表和文件之间拷贝数据CREATE AGGREGATE 定义一个新的聚集函数CREATE CAST 定义一个用户定义的转换CREATE CONSTRAINT TRIGGER 定义一个新的约束触发器CREATE CONVERSION 定义一个新的的编码转换CREATE DATABASE 创建新数据库CREATE DOMAIN 定义一个新域CREATE FUNCTION 定义一个新函数CREATE GROUP 定义一个新的用户组CREATE INDEX 定义一个新索引CREATE LANGUAGE 定义一种新的过程语言CREATE OPE

16、RATOR 定义一个新的操作符CREATE OPERATOR CLASS 定义一个新的操作符表CREATE ROLE define a new database roleCREATE RULE 定义一个新的重写规则CREATE SCHEMA 定义一个新的模式CREATE SEQUENCE 创建一个新的序列发生器CREATE TABLE 定义一个新表CREATE TABLE AS 从一条查询的结果中定义一个新表CREATE TABLESPACE 定义一个新的表空间CREATE TRIGGER 定义一个新的触发器CREATE TYPE 定义一个新的数据类型CREATE USER 创建一个新的数据库

17、用户帐户CREATE VIEW 定义一个视图DEALLOCATE 删除一个准备好的查询DECLARE 定义一个游标DELETE 删除一个表中的行DROP AGGREGATE 删除一个用户定义的聚集函数DROP CAST 删除一个用户定义的类型转换DROP CONVERSION 删除一个用户定义的编码转换DROP DATABASE 删除一个数据库DROP DOMAIN 删除一个用户定义的域DROP FUNCTION 删除一个函数DROP GROUP 删除一个用户组DROP INDEX 删除一个索引DROP LANGUAGE 删除一个过程语言DROP OPERATOR 删除一个操作符DROP OP

18、ERATOR CLASS 删除一个操作符表DROP ROLE 删除一个数据库角色DROP RULE 删除一个重写规则DROP SCHEMA 删除一个模式DROP SEQUENCE 删除一个序列DROP TABLE 删除一个表DROP TABLESPACE 删除一个表空间DROP TRIGGER 删除一个触发器定义DROP TYPE 删除一个用户定义数据类型DROP USER 删除一个数据库用户帐号DROP VIEW 删除一个视图END 提交当前的事务EXECUTE 执行一个准备好的查询EXPLAIN 显示语句执行规划FETCH 用游标从查询中抓取行GRANT 定义访问权限INSERT 在表中创

19、建新行LISTEN 监听一个通知LOAD 装载或重载一个共享库文件LOCK 明确地锁定一个表MOVE 重定位一个游标NOTIFY 生成一个通知PREPARE 创建一个准备好的查询PREPARE TRANSACTION 为当前事务做两阶段提交的准备REINDEX 重建索引RELEASE SAVEPOINT 删除一个前面定义的保存点RESET 把一个运行时参数值恢复为缺省值REVOKE 删除访问权限ROLLBACK 退出当前事务ROLLBACK PREPARED 取消一个早先为两阶段提交准备好的事务ROLLBACK TO 回滚到一个保存点SAVEPOINT 在当前事务里定义一个新的保存点SELEC

20、T 从表或视图中取出若干行SELECT INTO 从一个查询的结果中定义一个新表SET 改变运行时参数SET CONSTRAINTS 设置当前事务的约束检查模式SET ROLE set the current user identifier of the current sessionSET SESSION AUTHORIZATION 为当前会话设置会话用户标识符和当前用户标识符SET TRANSACTION 设置当前事务的特性SHOW 显示运行时参数的数值START TRANSACTION 开始一个事务块TRUNCATE 清空一个或者一堆表UNLISTEN 停止监听通知信息UPDATE 更新

21、一个表中的行VACUUM 垃圾收集以及可选地分析一个数据库II. 客户端应用clusterdb 对一个PostgreSQL数据库进行建簇createdb 创建一个新的 PostgreSQL 数据库createlang 定义一种新的 PostgreSQL 过程语言createuser 定义一个新的 PostgreSQL 用户帐户dropdb 删除一个现有 PostgreSQL 数据库droplang 删除一种 PostgreSQL 过程语言dropuser 删除一个 PostgreSQL 用户帐户ecpg 嵌入的 SQL C 预处理器pg_config 检索已安装版本的 PostgreSQL 的

22、信息pg_dump 将一个PostgreSQL数据库抽出到一个脚本文件或者其它归档文件中pg_dumpall 抽出一个 PostgreSQL 数据库集群到脚本文件中pg_restore 从一个由 pg_dump 创建的备份文件中恢复 PostgreSQL 数据库。psql PostgreSQL 交互终端vacuumdb 收集垃圾并且分析一个PostgreSQL 数据库III. PostgreSQL 服务器应用initdb 创建一个新的 PostgreSQL数据库集群ipcclean 从失效的PostgreSQL服务器中删除共享内存和信号灯pg_controldata 显示一个 PostgreS

23、QL 集群的控制信息pg_ctl 启动，停止和重起 PostgreSQLpg_resetxlog 重置一个 PostgreSQL 数据库集群的预写日志以及其它控制内容postgres 以单用户模式运行一个 PostgreSQL服务器postmaster PostgreSQL多用户数据库服务器59.导出数据库角色 /data/pgsql/bin/pg_dumpall -p 5432 -U postgres -r /tmp/postgres_8.3_role.bak60.修改sequence所有者 grant all on sequence名称 to 所有者;61.修改sequence初始值Alt

24、er SEQUENCE sequencename START value;62.查看sequence当前值SELECT currval(sequencename);63.查看sequence下一值SELECT nextval(sequencename);64.设置sequence当前值 alter SEQUENCE sequencename restart with startvalue;SELECT nextval(sequencename);65.查询表结构 SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS l

25、ength,a.atttypmod AS lengthvar,a.attnotnull AS notnullFROM pg_class c,pg_attribute a,pg_type tWHERE c.relname=表名称and a.attnum 0 and a.attrelid = c.oid and a.atttypid = t.oid66.将查询结果直接输出到文件在psql中o 文件路径select datname,rolname from pg_database a left outer join pg_roles b on a.datdba=b.oid ;o 67.查询数据库所有

26、则select datname,rolname from pg_database a left outer join pg_roles b on a.datdba=b.oid ;68.结束正在执行的事务SELECT * from pg_stat_activity;select pg_cancel_backend(procpid); 60.结束sessionSELECT * from pg_stat_activity;select pg_terminate_backend(procpid);61.postgresql取消转义字符功能 将postgresql.conf文件中的standard_co

27、nforming_strings设置为on62.查询正在执行SQLSELECT procpid, start, now() - start AS lap, current_query FROM (SELECT backendid, pg_stat_get_backend_pid(S.backendid) AS procpid, pg_stat_get_backend_activity_start(S.backendid) AS start, pg_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S ) AS S WHERE current_query ORDER BY lap DESC;