引言
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_cpu
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 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陳述句來進行支撐，
本文來自博客園，作者：古道輕風，轉載請注明原文鏈接：https://www.cnblogs.com/88223100/p/PostgreSQL-30-practical-SQL-statements.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/551007.html
標籤：其他
上一篇：kafka設計理念決議
下一篇：返回列表