轻松拿下PostgreSQL,这30个实用SQL语句你细品

梦尧技术 2023-04-17 09:53:25

引言

 

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

 

一、数据库连接

 

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

 

  •  
select count(*) 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 SELECT ON table_name TO username;

 

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

 

  •  
alter table table_name owner to username;

 

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

 

  •  
grant all privileges on table product to username;

 

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

 

  •  
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;

 

三、数据库表或者索引

 

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

 

  •  
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 not like 'pg_%' and relname not like '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('product')) as size;

 

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

 

  •  
select pg_size_pretty(pg_total_relation_size('table_name')) as size;

 

5、查看表中索引大小

 

  •  
select pg_size_pretty(pg_indexes_size('product'));

 

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

 

  •  
select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' order by 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 order by total_time desc limit 5;

 

备注:需要开启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_cpuFROM pg_stat_statementsORDER BY total_time DESCLIMIT 3;

 

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.datname as datname, pgsa.usename as usename, pgsa.client_addr client_addr, pgsa.application_name as application_name, pgsa.state as state, pgsa.backend_start as backend_start, pgsa.xact_start as xact_start, extract(epoch from (now() - pgsa.xact_start)) as xact_stay, pgsa.query_start as query_start, extract(epoch from (now() - pgsa.query_start)) as query_stay , pgsa.query as 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 order by query_stay desc limit 5;

 

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

 

  •  
select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;

 

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

 

  •  
select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;

 

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';

 

六、数据库备份(非SQL)

 

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语句来进行支撑。

 

作者丨梦尧技术
来源丨公众号:慕枫技术笔记(ID:lulideguang)
dbaplus社群欢迎广大技术人员投稿,投稿邮箱:editor@dbaplus.cn
最新评论
访客 2024年04月08日

如果字段的最大可能长度超过255字节,那么长度值可能…

访客 2024年03月04日

只能说作者太用心了,优秀

访客 2024年02月23日

感谢详解

访客 2024年02月20日

一般干个7-8年(即30岁左右),能做到年入40w-50w;有…

访客 2023年08月20日

230721

活动预告