SELECT t.relname AS table_name, pg_size_pretty(pg_indexes_size(t.oid)) AS index_size FROM pg_class t JOIN pg_namespace n ON n.oid = t.relnamespace WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND t.relkind = 'r' -- 只选择普通表 ORDER BY pg_indexes_size(t.oid) DESC;
SELECT t.relname AS table_name, pg_size_pretty(pg_total_relation_size(t.oid)) AS total_size, pg_size_pretty(pg_relation_size(t.oid)) AS table_size, pg_size_pretty(pg_indexes_size(t.oid)) AS index_size FROM pg_class t JOIN pg_namespace n ON n.oid = t.relnamespace WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND t.relkind = 'r' -- 只选择普通表 ORDER BY pg_total_relation_size(t.oid) DESC;
SELECT d.datname AS database_name, u.usename AS username, pg_catalog.has_database_privilege(u.usename, d.datname, 'CONNECT') AS can_connect, pg_catalog.has_database_privilege(u.usename, d.datname, 'CREATE') AS can_create, pg_catalog.has_database_privilege(u.usename, d.datname, 'TEMP') AS can_use_temp FROM pg_database d CROSS JOIN pg_user u ORDER BY d.datname, u.usename;
database_name | username | can_connect | can_create | can_use_temp -----------------------------------+--------------------+-------------+------------+-------------- Devops | alicloud_rds_admin | t | t | t Devops | aurora | t | t | t Devops | cy7_read | t | f | t
schema_name | object_name | object_type | grantee | can_select | can_insert | can_update | can_delete | can_truncate | can_references | can_trigger -------------+------------------------+-------------+---------------------------+------------+------------+------------+------------+--------------+----------------+------------- public | order | r | alicloud_rds_admin | SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER public | order | r | aurora | SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER public | order | r | read | SELECT | | | | | | public | order | r | center_service | SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER public | order | r | devops_rw | | | | | | | public | order | r | dp | | | | | | | public | order | r | saascenter | SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER public | order | r | terminal_rw | | | | | | | public | order | r | user | | | | | | | public | order | r | dba_admin | SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER public | order | r | pg_execute_server_program | | | | | | | public | order | r | pg_monitor | | | | | | | public | order | r | pg_rds_superuser | SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER public | order | r | pg_read_all_settings | | | | | | | public | order | r | pg_read_all_stats | | | | | | | public | order | r | pg_read_server_files | | | | | | | public | order | r | pg_signal_backend | | | | | | | public | order | r | pg_stat_scan_tables | | | | | | | public | order | r | pg_write_server_files | | | | | | |
5 拼接出当前表的create table 语句
WITH column_info AS ( SELECT a.attname AS column_name, pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type, CASE WHEN a.attnotnull THEN ' NOT NULL' ELSE '' END AS not_null, pg_catalog.array_to_string(ARRAY( SELECT conname FROM pg_catalog.pg_constraint c WHERE c.conrelid = a.attrelid AND a.attnum = ANY(c.conkey) AND c.contype IN ('p', 'u') ), ', ') AS constraints FROM pg_catalog.pg_attribute a WHERE a.attrelid = 'your_table_name'::regclass AND a.attnum > 0 AND NOT a.attisdropped ) SELECT 'CREATE TABLE your_table_name (' || STRING_AGG(column_name || ' ' || data_type || not_null || CASE WHEN constraints IS NOT NULL THEN ' CONSTRAINT ' || constraints ELSE '' END, ', ') || ');' AS create_statement FROM column_info;
6 分析数据库中的表1天未进行
SELECT relname AS table_name, last_autovacuum, last_autoanalyze FROM pg_stat_all_tables WHERE last_autovacuum < NOW() - interval '1 day' AND schemaname NOT IN ('pg_catalog', 'information_schema');
