PostgreSQL是一款功能非常强大的开源关系型数据库，它支持哈希索引、反向索引、部分索引、Expression 索引、GiST、GIN等多种索引模式，同时可安装功能丰富的扩展包。相较于Mysql，PostgreSQ支持通过PostGIS扩展支持地理空间数据、支持嵌套循环，哈希连接，排序合并三种表连接方式等一系列的强化功能。本文主要整理总结了30个实用SQL，方便大家可以高效利用PostgreSQL。

　　1、获取数据库实例连接数

　　selectcount(*)frompg_stat_activity;

　　2、获取数据库最大连接数

　　showmax_connections

　　3、查询当前连接数详细信息

　　select*frompg_stat_activity;

　　4、查询数据库中各个用户名对应的数据库连接数

　　selectusename,count(*)frompg_stat_activitygroupbyusename;

　　1、为指定用户赋予指定表的select权限

　　GRANTSELECTONtable_nameTOusername;

　　2、修改数据库表所属的ownner

　　altertabletable_name ownertousername;

　　3、授予指定用户指定表的所有权限

　　grantallprivilegesontableproducttousername;

　　4、授予指定用户所有表的所有权限

　　GRANTALLPRIVILEGESONALLTABLESINSCHEMApublicTOusername;

　　1、获取数据库表中的索引

　　select*frompg_indexeswheretablename ='product';

　　2、获取当前db中所有表信息

　　select*frompg_tables;

　　3、查询数据库安装了哪些扩展

　　select*frompg_extension;

　　4、查询数据库中的所有表及其描述

　　selectrelnameasTABLE_NAME ,col_description(c.oid,0)asCOMMENTSfrompg_class cwhererelkind ='r'andrelnamenotlike'pg_%'andrelnamenotlike'sql_%'

　　1、查询执行数据库大小

　　selectpg_size_pretty (pg_database_size('db_product'));

　　2、查询数据库实例当中各个数据库大小

　　selectdatname, pg_size_pretty (pg_database_size(datname))ASsizefrompg_database;

　　3、查询单表数据大小

　　selectpg_size_pretty(pg_relation_size('product'))assize;

　　4、查询数据库表包括索引的大小

　　selectpg_size_pretty(pg_total_relation_size('table_name'))assize;

　　5、查看表中索引大小

　　selectpg_size_pretty(pg_indexes_size('product'));

　　6、获取各个表中的数据记录数

　　selectrelnameasTABLE_NAME, reltuplesasrowCountsfrompg_classwhererelkind ='r'orderbyrowCountsdesc

　　7、查看数据库表对应的数据文件

　　selectpg_relation_filepath('product');

　　1、查看数据库实例的版本

　　selectversion();

　　2、查看最新加载配置的时间

　　selectpg_conf_load_time();

　　3、查看当前wal的buffer中有多少字节未写入磁盘

　　selectpg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location());

　　4、查询最耗时的5个sql

　　select*frompg_stat_statementsorderbytotal_timedesclimit5;

　　备注：需要开启pg_stat_statements

　　5、获取执行时间最慢的3条SQL，并给出CPU占用比例

　　SELECTsubstring(query,1,1000)ASshort_query,round(total_time::numeric,2)AStotal_time,calls,round((100* total_time /sum(total_time::numeric)OVER())::numeric,2)ASpercentage_cpuFROMpg_stat_statementsORDERBYtotal_timeDESCLIMIT3;

　　6、分析评估SQL执行情况

　　EXPLAINANALYZESELECT*FROMproduct

　　7、查看当前长时间执行却不结束的SQL

　　selectdatname, usename, client_addr, application_name, state, backend_start, xact_start, xact_stay, query_start, query_stay,replace(query,chr(10),' ')asqueryfrom(selectpgsa.datnameasdatname, pgsa.usenameasusename, pgsa.client_addr client_addr, pgsa.application_nameasapplication_name, pgsa.stateasstate, pgsa.backend_startasbackend_start, pgsa.xact_startasxact_start,extract(epochfrom(now() - pgsa.xact_start))asxact_stay, pgsa.query_startasquery_start,extract(epochfrom(now() - pgsa.query_start))asquery_stay , pgsa.queryasqueryfrompg_stat_activityaspgsawherepgsa.state !='idle'andpgsa.state !='idle in transaction'andpgsa.state !='idle in transaction (aborted)') idleconnectionsorderbyquery_staydesclimit5;

　　8、查出使用表扫描最多的表

　　select*frompg_stat_user_tableswheren_live_tup >100000andseq_scan >0orderbyseq_tup_readdesclimit10;

　　9、查询读取buffer最多的5个SQL

　　select*frompg_stat_statementsorderbyshared_blks_hit+shared_blks_readdesclimit5;

　　10、获取数据库当前的回滚事务数以及死锁数

　　selectdatname,xact_rollback,deadlocksfrompg_stat_database

　　11、查询访问指定表的慢查询

　　select*frompg_stat_activitywherequeryilike

'%

　　%'

　　1、备份postgres库并tar打包

　　pg_dump -h 127.0.0.1 -p 5432 -U postgres -f postgres.sql.tar -Ft

　　2、备份postgres库，转储数据为带列名的INSERT命令

　　pg_dumpall -d postgres -U postgres -f postgres.sql --column-inserts

　　本文主要针对PostgreSQL数据库中在日常开发中比较常用的SQL进行了分类的总结，那么大家日常开发工作中，可以在分析数据库性能、数据库连接情况、sql执行情况等数据库分析方面都有对应的SQL语句来进行支撑。