文章摘要
这篇文章介绍了PostgreSQL中常用的SQL语句，主要用于排查和解决死锁问题。主要内容包括查询死锁状态并删除相关进程，备份和恢复数据库，以及插入单条或多条数据的操作。文章还详细说明了如何从一张表插入数据到另一张表，并提供了查询表大小、索引大小、被锁定表以及Schema下表和索引总大小的方法。这些操作可以帮助用户更高效地排查和解决PostgreSQL死锁问题。
目录1、查询链接数2、查看死锁状态3、删除死锁进程4、备份数据库5、还原数据库6、插入数据插入单条数据插入多条数据从一张表查询到的数据插入到另一张表7 、查询pg中单张表的大小（不包含索引）8、查询数据库中所有表的大小9、按顺序查看索引10 、查询数据库的大小11、查询被锁定的表12 、查询一个Schema下面的所有表的总大小（单位MB，包括索引和数据）14 、查询表大小按大小排序并分离data与index
SELECT sum(numbackends) FROM pg_stat_database;
select pid,
? ? ? ?usename,
? ? ? ?pg_blocking_pids(pid) as blocked_by,
? ? ? ?query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;
SELECT pg_cancel_backend(__pid__);
SELECT pg_terminate_backend(__pid__);
# sql文件
pg_dump dangerousdb > db.sql
# tar文件
pg_dump -U postgres -F c dangerousdb > dangerousdb.tar
# gz文件
pg_dump -U postgres dangerousdb | gzip > dangerousdb.gz
# 已经存在数据库
pg_restore -U postgres -Ft -d dbcooper < dbcooper.tar
# 创建新数据库
pg_restore -U postgres -Ft -C -d dbcooper < dbcooper.tar
#? ?
INSERT INTO TABLE_1
(
column_1,
column_2,
column_3
)
values(
column_1,
column_2,
column_3
)
INSERT INTO TABLE_1
(
column_1,
column_2,
column_3
)
values(
column_1,
column_2,
column_3
),(
column_1,
column_2,
column_3
)…
INSERT INTO TABLE_1
(
column_1,
column_2,
column_3
)
SELECT
column_1,
column_2,
column_3
FROM
TABLE_2
where TABLE_2条件;
select
? ? pg_size_pretty(pg_relation_size(‘schema.table_name’));
select
? ? relname,
? ? pg_size_pretty(pg_relation_size(relid))
from
? ? pg_stat_user_tables
where
? ? schemaname=’public’
order by
? ? pg_relation_size(relid) desc;
select
? ? indexrelname,
? ? pg_size_pretty(pg_relation_size(relid))
from
? ? pg_stat_user_indexes
where
? ? schemaname=’public’
order by
? ? pg_relation_size(relid) desc;
select
? ? pg_database.datname,
? ? pg_size_pretty (pg_database_size(pg_database.datname)) as size
from
? ? pg_database;
select
? ? pg_class.relname as table,
? ? pg_database.datname as database,
? ? pid,
? ? mode,
? ? granted
from
? ? pg_locks,
? ? pg_class,
? ? pg_database
where
? ? pg_locks.relation=pg_class.oid
? ? and pg_locks.database=pg_database.oid;
select
? ? schemaname ,
? ? round(sum(pg_total_relation_size(schemaname || ‘.’ || tablename))/ 1024 / 1024) “Size_MB”
from
? ? pg_tables
where
? ? schemaname='<schemaname>’
group by
? ? 1;
13 、查询所有表的大小并排序（包含索引）
select
? ? table_schema || ‘.’ || table_name as table_full_name,
? ? pg_size_pretty(pg_total_relation_size(‘”‘ || table_schema || ‘”.”‘ || table_name || ‘”‘)) as size
from
? ? information_schema.tables
order by
? ? pg_total_relation_size(‘”‘ || table_schema || ‘”.”‘ || table_name || ‘”‘) desc
limit 20;
select
? ? table_name,
? ? pg_size_pretty(table_size) as table_size,
? ? pg_size_pretty(indexes_size) as indexes_size,
? ? pg_size_pretty(total_size) as total_size
from
? ? (
? ? select
? ? ? ? table_name,
? ? ? ? pg_table_size(table_name) as table_size,
? ? ? ? pg_indexes_size(table_name) as indexes_size,
? ? ? ? pg_total_relation_size(table_name) as total_size
? ? from
? ? ? ? (
? ? ? ? select
? ? ? ? ? ? (‘”‘ || table_schema || ‘”.”‘ || table_name || ‘”‘) as table_name
? ? ? ? from
? ? ? ? ? ? information_schema.tables) as all_tables
? ? order by
? ? ? ? total_size desc) as pretty_sizes;
或者
select
? ? table_name,
? ? pg_size_pretty(table_size) as table_size,
? ? pg_size_pretty(indexes_size) as indexes_size,
? ? pg_size_pretty(total_size) as total_size
from
? ? (
? ? select
? ? ? ? table_name,
? ? ? ? pg_table_size(table_name) as table_size,
? ? ? ? pg_indexes_size(table_name) as indexes_size,
? ? ? ? pg_total_relation_size(table_name) as total_size
? ? from
? ? ? ? (
? ? ? ? select
? ? ? ? ? ? (” || table_schema || ‘.’ || table_name || ”) as table_name
? ? ? ? from
? ? ? ? ? ? information_schema.tables) as all_tables
? ? order by
? ? ? ? total_size desc) as pretty_sizes;
到此这篇关于postgresql 常用SQL语句小结的文章就介绍到这了,更多相关postgresql 常用SQL内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！