日本免费高清视频-国产福利视频导航-黄色在线播放国产-天天操天天操天天操天天操|www.shdianci.com

學(xué)無先后，達者為師

網(wǎng)站首頁 編程語言 正文

postgresql?常用SQL語句小結(jié)_PostgreSQL

更新時間： 2022-12-13 編程語言

1、查詢鏈接數(shù)

SELECT sum(numbackends) FROM pg_stat_database;

2、查看死鎖狀態(tài)

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、備份數(shù)據(jù)庫

# 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、還原數(shù)據(jù)庫

# 已經(jīng)存在數(shù)據(jù)庫
pg_restore -U postgres -Ft -d dbcooper < dbcooper.tar
# 創(chuàng)建新數(shù)據(jù)庫
pg_restore -U postgres -Ft -C -d dbcooper < dbcooper.tar
#? ?

6、插入數(shù)據(jù)

插入單條數(shù)據(jù)

INSERT INTO TABLE_1
(
column_1,
column_2,
column_3
)
values(
column_1,
column_2,
column_3
)

插入多條數(shù)據(jù)

INSERT INTO TABLE_1
(
column_1,
column_2,
column_3
)
values(
column_1,
column_2,
column_3
),(
column_1,
column_2,
column_3
)...

從一張表查詢到的數(shù)據(jù)插入到另一張表

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、查詢數(shù)據(jù)庫中所有表的大小

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 、查詢數(shù)據(jù)庫的大小

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，包括索引和數(shù)據(jù)）

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;
欄目分類
最近更新