postgresql 常用SQL语句小结

2022-11-19 06:01

短信预约 -IT技能 免费直播动态提醒

1、查询链接数

SELECT sum(numbackends) FROM pg_stat_database;

2、查看死锁状态

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;

3、删除死锁进程

SELECT pg_cancel_backend(__pid__);
SELECT pg_terminate_backend(__pid__);

4、备份数据库

# 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

5、还原数据库

# 已经存在数据库
pg_restore -U postgres -Ft -d dbcooper < dbcooper.tar
# 创建新数据库
pg_restore -U postgres -Ft -C -d dbcooper < dbcooper.tar
#

6、插入数据

插入单条数据

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条件;

7 、查询pg中单张表的大小（不包含索引）

select
  pg_size_pretty(pg_relation_size('schema.table_name'));

8、查询数据库中所有表的大小

select
  relname,
  pg_size_pretty(pg_relation_size(relid))
from
  pg_stat_user_tables
where
  schemaname = 'public'
order by
  pg_relation_size(relid) desc;

9、按顺序查看索引

select
  indexrelname,
  pg_size_pretty(pg_relation_size(relid))
from
  pg_stat_user_indexes
where
  schemaname = 'public'
order by
  pg_relation_size(relid) desc;

10 、查询数据库的大小

select
  pg_database.datname,
  pg_size_pretty (pg_database_size(pg_database.datname)) as size
from
  pg_database;

11、查询被锁定的表

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;

12 、查询一个Schema下面的所有表的总大小（单位MB，包括索引和数据）

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;

14 、查询表大小按大小排序并分离data与index

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内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

postgresql 常用SQL

阅读原文内容投诉