## 查看数据库版本。 select version(); ## 查看表空间 select * from pg_tablespace; ## 大小相关的。 1)查看表空间大小 select pg_tablespace_size('pg_default'); 2)查看各个表空间的大小 select spcname, pg_size_pretty(pg_tablespace_size(spcname)) from pg_tablespace; 3)查看DB大小 select pg_size_pretty(pg_database_size(db_name)); 4)查看所有数据库的大小 select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database; 5)查看表大小 select pg_size_pretty(pg_relation_size(table_name)) 6)按占空间大小，顺序查看所有表的大小 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 schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB" from pg_tables where schemaname='public' group by 1; 7)按占空间大小，顺序查看索引大小 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 schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||indexname))/1024/1024) "Size_MB" from pg_indexes where schemaname='public' group by 1; ## 查看各数据库数据创建时间 select datname,(pg_stat_file(format('%s/%s/PG_VERSION',case when spcname='pg_default' then 'base' else 'pg_tblspc/'||t2.oid||'/PG_11_201804061/' end, t1.oid))).* from pg_database t1,pg_tablespace t2 where t1.dattablespace=t2.oid; ## 索引相关 1）创建索引。 CREATE INDEX index_name ON table_name (column_name, ...); 2) 并发创建索引（在线创建索引）。 CREATE INDEX CONCURRENTLY 3）删除索引 DROP INDEX idx_name; drop index concurrently idx_name; 4）查看表的所有索引信息 select * from pg_indexes where tablename='student'; 5）显示关于访问特定索引的I/O统计信息。 select * from pg_statio_all_indexes where relname='events'; 6）显示索引类型 select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in ( select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'events'); 7）显示索引大小 SELECT c.relname,c2.relname, c2.relpages*8 as size_kb FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = 'events' AND c.oid = i.indrelid AND c2.oid = i.indexrelid ORDER BY c2.relname; ## 查看表的约束 select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = 'events'; ## 查看表所对应的数据文件路径与大小 SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'events'; ## 序列相关 1）查看序列 select * from information_schema.sequences where sequence_schema = 'public'; 2）创建序列： create sequence seq_user_camera_version increment by 1 minvalue 1 no maxvalue start with 1; 3）建表，并用上面的序列作为主键自增序列 CREATE TABLE public.user_camera_version ( id int4 NOT NULL DEFAULT nextval('seq_user_camera_version'::regclass), user_id int4 NULL, user_type varchar(1) NULL, hardware_version varchar(100) NULL, software_version varchar(100) NULL, modify_date timestamp NULL, CONSTRAINT user_camera_version_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ) ; ## 函数相关 1）查看所有用户自定义函数。 \df 或 SELECT n.nspname as "Schema", p.proname as "Name", pg_catalog.pg_get_function_result(p.oid) as "Result data type", pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types", CASE p.prokind WHEN 'a' THEN 'agg' WHEN 'w' THEN 'window' WHEN 'p' THEN 'proc' ELSE 'func' END as "Type" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE pg_catalog.pg_function_is_visible(p.oid) AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' ORDER BY 1, 2, 4; 2）查看函数定义 select oid,* from pg_proc where proname = 'insert_platform_action_exist'; select * from pg_get_functiondef(oid); 3）创建函数 CREATE FUNCTION add1(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT; ## 查看视图 select * from pg_views where schemaname = 'public'; select * from information_schema.views where table_schema = 'public'; ## 锁相关 1）查看锁等待信息。 select * from pg_locks where granted is not true; 2）查看会话。 select * from pg_stat_activity; ## 查看postgresql数据库启动时间 select pg_postmaster_start_time(); ## 查询当前客户端的端口号 select inet_client_port(); 查看与当前会话相关联的服务器进程ID select pg_backend_pid(); 查看配置文件最后一次载入时间 select pg_conf_load_time ## 查看参数文件 show config_file; show hba_file; show ident_file; ## 查看当前会话的参数值 show all; ## 查看参数值 select * from pg_settings; ## 查看某个参数值,比如参数work_mem show work_mem ##修改某个参数值,比如参数work_mem alter system set work_mem='8MB' --使用alter system命令将修改postgresql.auto.conf文件，而不是postgresql.conf，这样可以很好的保护postgresql.conf文件，加入你使用很多alter system命令后搞的一团糟，那么你只需要删除postgresql.auto.conf，再执行pg_ctl reload加载postgresql.conf文件即可实现参数的重新加载。 ## 查看是否开启归档 show archive_mode; ## 运行日志相关 --运行日志包括Error信息，定位慢查询SQL，数据库的启动关闭信息，checkpoint过于频繁等的告警信息。 show logging_collector;--启动日志收集 show log_directory;--日志输出路径 show log_filename;--日志文件名 show log_truncate_on_rotation;--当生成新的文件时如果文件名已存在，是否覆盖同名旧文件名 show log_statement;--设置日志记录内容 show log_min_duration_statement;--运行XX毫秒的语句会被记录到日志中，-1表示禁用这个功能，0表示记录所有语句，类似mysql的慢查询配置 ## 用户和权限相关 1）创建用户 create user u2 with login CREATEROLE CREATEDB password 'u2'; create user u2 password 'u2'; 2）修改数据库owner ALTER DATABASE name OWNER TO new_owner; 3）设置用户对某个数据库的所有权限 GRANT ALL PRIVILEGES ON DATABASE database_name to username; 4）授予用户CONNECT到数据库的权限 GRANT CONNECT ON DATABASE database_name TO username; 5） 授予public模式中所有表的所有权限给用户。 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username; 6）授予public模式中所有序列的所有权限给用户: GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO username; 其他一些其他的复杂SQL 查看表的建表语句SQL CREATE OR REPLACE FUNCTION generate_create_table_statement(p_table_name varchar) RETURNS text AS $BODY$ DECLARE v_table_ddl text; column_record record; BEGIN FOR column_record IN SELECT b.nspname as schema_name, b.relname as table_name, a.attname as column_name, pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type, CASE WHEN (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN 'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) ELSE '' END as column_default_value, CASE WHEN a.attnotnull = true THEN 'NOT NULL' ELSE 'NULL' END as column_not_null, a.attnum as attnum, e.max_attnum as max_attnum FROM pg_catalog.pg_attribute a INNER JOIN (SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ ('^('||p_table_name||')$') AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3) b ON a.attrelid = b.oid INNER JOIN (SELECT a.attrelid, max(a.attnum) as max_attnum FROM pg_catalog.pg_attribute a WHERE a.attnum > 0 AND NOT a.attisdropped GROUP BY a.attrelid) e ON a.attrelid=e.attrelid WHERE a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum LOOP IF column_record.attnum = 1 THEN v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' ('; ELSE v_table_ddl:=v_table_ddl||','; END IF; IF column_record.attnum <= column_record.max_attnum THEN v_table_ddl:=v_table_ddl||chr(10)|| ' '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null; END IF; END LOOP; v_table_ddl:=v_table_ddl||');'; RETURN v_table_ddl; END; $BODY$ LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER; 查看当前事务锁等待、持锁信息的SQL 来源：https://github.com/digoal/blog/blob/master/201705/20170521_01.md?spm=a2c6h.12873639.article-detail.7.75fd5853rcmmqH&file=20170521_01.md with t_wait as ( select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted, a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath, b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted ), t_run as ( select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted, a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath, b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted ), t_overlap as ( select r.* from t_wait w join t_run r on ( r.locktype is not distinct from w.locktype and r.database is not distinct from w.database and r.relation is not distinct from w.relation and r.page is not distinct from w.page and r.tuple is not distinct from w.tuple and r.virtualxid is not distinct from w.virtualxid and r.transactionid is not distinct from w.transactionid and r.classid is not distinct from w.classid and r.objid is not distinct from w.objid and r.objsubid is not distinct from w.objsubid and r.pid <> w.pid ) ), t_unionall as ( select r.* from t_overlap r union all select w.* from t_wait w ) select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid, string_agg( 'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)|| 'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)|| 'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)|| 'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)|| 'SQL (Current SQL in Transaction): '||chr(10)|| case when query is null then 'NULL' else query::text end, chr(10)||'--------'||chr(10) order by ( case mode when 'INVALID' then 0 when 'AccessShareLock' then 1 when 'RowShareLock' then 2 when 'RowExclusiveLock' then 3 when 'ShareUpdateExclusiveLock' then 4 when 'ShareLock' then 5 when 'ShareRowExclusiveLock' then 6 when 'ExclusiveLock' then 7 when 'AccessExclusiveLock' then 8 else 0 end ) desc, (case when granted then 0 else 1 end) ) as lock_conflict from t_unionall group by locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;