引言
PostgreSQL是一款功能非常强大的开源关系型数据库，它支持哈希索引、反向索引、部分索引、Expression 索引、GiST、GIN等多种索引模式，同时可安装功能丰富的扩展包。相较于Mysql，PostgreSQ支持通过PostGIS扩展支持地理空间数据、支持嵌套循环，哈希连接，排序合并三种表连接方式等一系列的强化功能。本文主要整理总结了30个实用SQL，方便大家可以高效利用PostgreSQL。
实用SQL语句
一、数据库连接
1、获取数据库实例连接数
selectcount(*)from pg_stat_activity;
2、获取数据库最大连接数
show max_connections
3、查询当前连接数详细信息
select*from pg_stat_activity;
4、查询数据库中各个用户名对应的数据库连接数
select usename, count(*) from pg_stat_activity group by usename;
二、赋权操作
1、为指定用户赋予指定表的select权限
GRANT SELECTON table_name TO username;
2、修改数据库表所属的ownner
altertable table_name owner to username;
3、授予指定用户指定表的所有权限
grant all privileges ontable product to username
4、授予指定用户所有表的所有权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;
三、数据库表或者索引
1、获取数据库表中的索引
select*from pg_indexes where tablename ='product';
select * from pg_indexes where tablename = 'product';
2、获取当前db中所有表信息
select*from pg_tables;
3、查询数据库安装了哪些扩展
select*from pg_extension;
4、查询数据库中的所有表及其描述
select relname as TABLE_NAME ,col_description(c.oid,0)as COMMENTS from pg_class c where relkind ='r'and relname notlike'pg_%'and relname notlike'sql_%'
四、获取数据大小
1、查询执行数据库大小
select pg_size_pretty (pg_database_size('db_product'));
2、查询数据库实例当中各个数据库大小
select datname, pg_size_pretty (pg_database_size(datname))AS size from pg_database;
3、查询单表数据大小
select pg_size_pretty(pg_relation_size('table_name'))as size;
4、查询数据库表包括索引的
select pg_size_pretty(pg_total_relation_size('table_name'))as size;
5、查看表中索引大小
select pg_size_pretty(pg_indexes_size('table_name'));
6、获取各个表中的数据记录数
select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind ='r'orderby rowCounts desc
7、查看数据库表对应的数据文件
select pg_relation_filepath('product');
五、数据库分析
1、查看数据库实例的版本
select version();
2、查看最新加载配置的时间
select pg_conf_load_time();
3、查看当前wal的buffer中有多少字节没有写入到磁盘中
select pg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location());
4、查询最耗时的5个sql
select*from pg_stat_statements orderby total_time desclimit5;
备注：需要开启pg_stat_statements
5、获取执行时间最慢的3条SQL，并给出CPU占用比例
SELECT substring(query,1,1000)AS short_query,round(total_time::numeric,2)AS total_time,calls,round((100* total_time / sum(total_time::numeric) OVER ())::numeric,2)AS percentage_cpu FROM pg_stat_statements ORDERBY total_time DESCLIMIT3;
6、分析评估SQL执行情况
EXPLAIN ANALYZE SELECT*FROM product
7、查看当前长时间执行却不结束的SQL
select datname, usename, client_addr, application_name, state, backend_start, xact_start, xact_stay, query_start, query_stay, replace(query, chr(10),' ')as query from(select pgsa.datnameas datname, pgsa.usenameas usename, pgsa.client_addr client_addr, pgsa.application_nameas application_name, pgsa.stateas state, pgsa.backend_startas backend_start, pgsa.xact_startas xact_start, extract(epoch from(now()- pgsa.xact_start))as xact_stay, pgsa.query_startas query_start, extract(epoch from(now()- pgsa.query_start))as query_stay , pgsa.queryas query from pg_stat_activity as pgsa where pgsa.state!='idle'and pgsa.state!='idle in transaction'and pgsa.state!='idle in transaction (aborted)') idleconnections orderby query_stay desclimit5;
8、查出使用表扫描最多的表
select*from pg_stat_user_tables where n_live_tup >100000and seq_scan >0orderby seq_tup_read desclimit10;
9、查询读取buffer最多的5个SQL
select*from pg_stat_statements orderby shared_blks_hit+shared_blks_read desclimit5;
10、获取数据库当前的回滚事务数以及死锁数
select datname,xact_rollback,deadlocks from pg_stat_database
11、查询指定表的慢查询
select*from pg_stat_activity where query ilike '%<table_name>%'and query_start - now()> interval '10 seconds';
六、数据库备份
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语句来进行支撑。
创作不易，如果各位同学觉得文章还不错的话，麻烦点赞+收藏+评论交流哦。老样子，文末和大家分享一首诗词。
临江仙·送钱穆父
一别都门三改火，天涯踏尽红尘。依然一笑作春温。无波真古井，有节是秋筠。
惆怅孤帆连夜发，送行淡月微云。樽前不用翠眉颦。人生如逆旅，我亦是行人。
