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;