热衷于分享各种干货知识，大家有想看或者想学的可以评论区留言，秉承着“开源知识来源于互联网，回归于互联网”的理念，分享一些日常工作中能用到或者频率比较的内容，希望大家能够喜欢，并提出宝贵地意见，我们一起提升，守住自己的饭碗。

1、查看版本

postgres=# show server_version;

postgres=# select version();

postgres=#SELECT * FROM pg_catalog.pg_settings WHERE name = 'server_version'; #信息更全

2、查看有多少个数据库及数据库所属编码

postgres=# \l

3、查看字符集

postgres=# \encoding

4、查看数据库的连接数量

postgres=# select datid,datname,pid,usename,state,client_addr,query from pg_stat_activity; #显示更详细

postgres=# SELECT COUNT(*) FROM pg_stat_activity; #汇总出数量

postgres=#show max_connections; #查看最大连接数

5、查询当前的用户

postgres=#select * from current_user;

postgres=# SELECT SESSION_USER;

postgres=# select * from pg_user; #查询所有的用户信息

6、查看数据库运行的时间

postgres=# select pg_postmaster_start_time();

postgres=#SELECT age(now(), pg_postmaster_start_time()) AS uptime;#汇总出时间

7、查看当前用户有多少个表

postgres=#SELECT tablename

FROM pg_catalog.pg_tables

WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

postgres=#SELECT tablename

FROM pg_catalog.pg_tables

WHERE schemaname = 'public'; #查看特定模式的表

8、表空间管理

postgres=# \db 

postgres=# SELECT pg_tablespace.spcname,

       pg_size_pretty(pg_tablespace_size(pg_tablespace.oid)) AS size

FROM pg_tablespace;

postgres=# select pg_size_pretty(pg_tablespace_size('pg_global'));#查询表空间大小

postgres=# create tablespace test owner test location '/pgdb/data/test'; #创建表空间，/pgdb/data/test路径得提前建好

postgres=# drop tablespace test; #删除表空间

9、查看所有的schema

postgres=# select * from information_schema.schemata; #信息更全

postgres=# \dn

10、查看数据库大小

postgres=# select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS total_size from pg_database;

postgres=#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 #显示用户

11、查看是否开启归档日志

postgres=# show archive_mode;

postgres=# show archive_command ;

12、查看锁信息

postgres=# SELECT * FROM pg_locks;

13、查看表结构

postgres=# \d students

postgres=#SELECT 

    column_name,

    data_type,

    character_maximum_length,

    is_nullable,

    column_default 

FROM

    information_schema.COLUMNS 

WHERE

    TABLE_NAME = 'students';

14、用户管理

postgres=# create user test1  with password '123456';#创建用户

postgres=# create user superwith password '123456' superuser;#创建超级管理员

postgres=# create schema test1  authorization  test1;#创建和用户同名的 schema

postgres=# grant all on schema test1  to test1;#将 schema 的所有权限给用户

postgres=# grant usage on schema test1  to test;#将schema 的所有权限给另外一个用户

postgres=# ALTER TABLE my_table SET SCHEMA test1;修改schema

postgres=# alter user superwith password '123123';#\password  super也可以进行修改postgres=# alter user super  with nologin; #禁止用户登录

postgres=# drop owned bysuper;#删除用户

postgres=#DROP USER test;#删除用户

15、角色管理

postgres=# \du

postgres=# create role role2;#创建角色

postgres=#drop role role1; #删除角色

16、数据库管理

postgres=#CREATE DATABASE test; #创建

postgres=#DROP DATABASE test; #删除

postgres=# \c test #切换数据库

postgres=# \ds #查看当前数据库序列

17、 表管理

postgres=#ANALYZE test; #分析表

postgres=#alter table test add c3 int; #新增字段

postgres=#alter table test drop c3 ;  #删除字段

postgres=#alter table test alter column c3 type varchar(10); #更改字段

postgres=#\x #列模式显示每个字段

18、索引管理

postgres=#REINDEX TABLE tablename;#重建索引

postgres=# \di #列出所有索引

postgres=#SELECT t.relname AS table_name,         i.relname AS index_name,         string_agg(a.attname,         ',') AS column_nameFROM pg_class t, pg_class i, pg_index ix, pg_attribute aWHERE t.oid = ix.indrelid        AND i.oid = ix.indexrelid        AND a.attrelid = t.oid        AND a.attnum = ANY(ix.indkey)        AND t.relkind = 'r'        AND t.relname NOT LIKE 'pg_%'GROUP BY  t.relname, i.relnameORDER BY  t.relname, i.relname;

18、检查表的大小

postgres=#SELECT pg_size_pretty(pg_total_relation_size('tablename'));

19、数据库启停管理

[postgres@localhost ~]$ pg_ctl stop #停止[postgres@localhost ~]$ pg_ctl start #启动

20、数据库登录

[postgres@localhost ~]$psql #未开启密码登录验证可以用此登录

[postgres@localhost ~]$ psql -U postgres -p 5785 -d postgres -h 192.168.59.138

21、查看所有的 function

postgres=# \df

postgres=#SELECT proname, proargtypes, prosrc

FROM pg_proc;

22、查看数据库扩展

postgres=# \dx

23、切换工作路径

postgres=# \cd /pgdb #执行脚本时必需

24、会话管理

postgres=#\conninfo #查看当前会话连接信息

postgres=#\x #因为表字段非常多，通过终端查询会重叠在一块，比较难看清，需要开启扩展显示，和 MySQL \G 类似

postgres=#select*from pg_stat_activity where state !='idle';

postgres=# SELECT datname, usename, pid, client_hostname, backend_start, query_start, wait_event_type, STATE, backend_type FROM pg_stat_activity;#查看会话的状态

postgres=#select now(),pid,client_addr,now()-query_start exec_time,query from pg_stat_activity where state ='active'and pid <> pg_backend_pid()and query_start < now()-'60 s'::interval order by query_start;#查询超过60S的会话

test=#select pg_backend_pid(); #查看当前会话ID

25、 显示 SQL 执行时间

postgres=#\timing

26、将查询结果输出到操作系统（Oracle spool 功能）

postgres=# \o test.txt select * from demo1; \o

27、执行 SQL 脚本

postgres=#\i test.sql

28、序列管理

postgres=# CREATE SEQUENCE snc_seq INCREMENT BY 1 START WITH 1 NO MAXvalue NO CYCLE CACHE 10; #创建序列postgres=#  select * from snc_seq ; #查看序列postgres=# select currval(' snc_seq '); #查看序列当前值postgres=# select nextval('snc_seq'); #获取序列下一个值

29、与序列有关的表

postgres=# select * from pg_sequence;postgres=# select * from pg_sequences;postgres=# select * from information_schema.sequences;

30、查看 blocker

postgres=# SELECT datname, pid, usename, client_hostname, wait_event, STATE, query FROM pg_stat_activity WHERE pid IN (SELECT pid FROM pg_locks WHERE GRANTED<>'f' AND transactionid = (SELECT transactionid FROM pg_locks WHERE GRANTED= 'f'));

31、 查看膨胀高的 top 10 表

postgres=#SELECT relname AS TABLE_NAME, pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) AS table_size, n_dead_tup, n_live_tup,(n_dead_tup * 100 / (n_live_tup + n_dead_tup))AS dead_tup_ratio FROM pg_stat_user_tables WHERE n_dead_tup<>0 LIMIT 10;

32、查看长事务

postgres=# SELECT extract(epoch FROM (clock_timestamp() - xact_start)) AS longtrans,extract(epoch FROM (clock_timestamp() - query_start)) AS longquery FROM pg_stat_activity WHERE 1=1 AND STATE <> 'idle' ;

33、当前数据库TOP10大小的表

postgres=#SELECT relname, pg_relation_size('public."' || relname || '"')/1024/1024 AS MB, relkind FROM pg_class WHERE relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname NOT IN ('pg_toast', 'pg_temp_1', 'pg_toast_temp_1', 'pg_catalog', 'information_schema')) AND relkind ='r' ORDER BY 2 DESC LIMIT 10 ;

34、查看当前数据库top10对象

postgres=#SELECT relname, pg_relation_size('public."' || relname || '"')/1024/1024 AS MB, relkind FROM pg_class WHERE relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname NOT IN ('pg_toast', 'pg_temp_1', 'pg_toast_temp_1', 'pg_catalog', 'information_schema')) ORDER BY 2 DESC LIMIT 10;

35、查看统计信息

postgres=#analyze students; #收集统计信息（分析表）

postgres=#vacuum students; # vacuum 表

postgres=#vacuum;# vacuum 数据库

postgres=#vacuum full; # vacuum 回收空间,会锁表

postgres=#SELECT schemaname,relname, n_live_tup, n_dead_tup, n_mod_since_analyze, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname=’students’; # 查看统计信息

36、查看 SQL 执行计划

postgres=#explain select * from students;postgres=#explain (analyze,verbose,costs,buffers,timing) select * from students;注意，analyze 会执行 SQL 。

37、查找PG堵塞

postgres=#SELECT blocked_locks.pid AS blocked_pid,         blocked_activity.usename AS blocked_user,         blocking_locks.pid AS blocking_pid,         blocking_activity.usename AS blocking_user,         blocked_activity.query AS blocked_statement,         blocking_activity.query AS current_statement_in_blocking_processFROM pg_catalog.pg_locks blocked_locksJOIN pg_catalog.pg_stat_activity blocked_activity    ON blocked_activity.pid = blocked_locks.pidJOIN pg_catalog.pg_locks blocking_locks    ON blocking_locks.locktype = blocked_locks.locktype        AND blocking_locks.database IS NOT DISTINCTFROM blocked_locks.database        AND blocking_locks.relation IS NOT DISTINCTFROM blocked_locks.relation        AND blocking_locks.page IS NOT DISTINCTFROM blocked_locks.page        AND blocking_locks.tuple IS NOT DISTINCTFROM blocked_locks.tuple        AND blocking_locks.virtualxid IS NOT DISTINCTFROM blocked_locks.virtualxid        AND blocking_locks.transactionid IS NOT DISTINCTFROM blocked_locks.transactionid        AND blocking_locks.classid IS NOT DISTINCTFROM blocked_locks.classid        AND blocking_locks.objid IS NOT DISTINCTFROM blocked_locks.objid        AND blocking_locks.objsubid IS NOT DISTINCTFROM blocked_locks.objsubid        AND blocking_locks.pid != blocked_locks.pidJOIN pg_catalog.pg_stat_activity blocking_activity    ON blocking_activity.pid = blocking_locks.pidWHERE NOT blocked_locks.granted;

38、查看当前执行的所有SQL语句

postgres=#SELECT    pid,    datname,    usename,    client_addr,    application_name,    STATE,    backend_start,    xact_start,    xact_stay,    query_start,    query_stay,    REPLACE ( query, chr( 10 ), ' ' ) AS query FROM    (    SELECT        pgsa.pid AS pid,        pgsa.datname AS datname,        pgsa.usename AS usename,        pgsa.client_addr client_addr,        pgsa.application_name AS application_name,        pgsa.STATE AS STATE,        pgsa.backend_start AS backend_start,        pgsa.xact_start AS xact_start,        EXTRACT ( epoch FROM ( now( ) - pgsa.xact_start ) ) AS xact_stay,        pgsa.query_start AS query_start,        EXTRACT ( epoch FROM ( now( ) - pgsa.query_start ) ) AS query_stay,        pgsa.query AS query     FROM        pg_stat_activity AS pgsa     WHERE        pgsa.STATE != 'idle'         AND pgsa.STATE != 'idle in transaction'         AND pgsa.STATE != 'idle in transaction (aborted)'     ) idleconnections ORDER BY    query_stay DESC;

39、杀掉某个数据库的所有连接

postgres=#SELECT pg_terminate_backend(pg_stat_activity.pid)FROM pg_stat_activity WHERE datname='数据库名称' AND pid<>pg_backend_pid();

40、查询延迟

postgres=#select application_name,client_addr,client_port,write_lag,replay_lag,sync_state from pg_stat_replication ;

41、查看占用空间TOP10的表

postgres=#SELECT schemaname    ,tablename    ,pg_size_pretty(pg_total_relation_size((quote_ident(schemaname) || '.' || quote_ident(tablename))::regclass))FROM pg_tablesORDER BY pg_total_relation_size((quote_ident(schemaname) || '.' || quote_ident(tablename))::regclass) DESC limit 10;

42、查询更新和删除记录条数top 10的表

postgres=#SELECT schemaname    ,relname    ,n_tup_upd    ,n_tup_del    ,round(n_tup_hot_upd / (            CASE                 WHEN n_tup_upd = 0                    THEN 1.0                ELSE n_tup_upd::NUMERIC                END            ), 4)FROM pg_stat_all_tablesORDER BY n_tup_upd + n_tup_del DESC limit 10;

43、查询插入记录条数top 10的表

postgres=#SELECT schemaname    ,relname    ,n_tup_insFROM pg_stat_all_tablesORDER BY n_tup_ins DESC limit 10;

44、查看归档任务状态

postgres=#select * from pg_stat_get_archiver();

45、查看数据库的启动时间

postgres=# select pg_postmaster_start_time();

46、查询数据库是否为备库

postgres=# select pg_is_in_recovery();

47、查看表所在的数据文件

test=# select pg_relation_filepath('students');

48、查询当前的最老事务距离当前时间、距离当前事务数, 说明膨胀空间大小, 越大可能导致越多膨胀垃圾

test=#SELECT * FROM pg_prepared_xacts;

49、长事务、prepared statement

test=#WITH aAS (    SELECT min(TRANSACTION::TEXT::int8) m    FROM pg_prepared_xacts    )    ,bAS (    SELECT txid_snapshot_xmin(txid_current_snapshot())::TEXT::int8 AS m    )    ,cAS (    SELECT min(least(backend_xid::TEXT::int8, backend_xmin::TEXT::int8)) m    FROM pg_stat_activity    )    ,dAS (    SELECT datname        ,usename        ,pid        ,query_start        ,xact_start        ,now()        ,wait_event        ,query    FROM pg_stat_activity    WHERE backend_xid IS NOT NULL        OR backend_xmin IS NOT NULL    ORDER BY least(backend_xid::TEXT::int8, backend_xmin::TEXT::int8) limit 1    )    ,eAS (    SELECT *    FROM pg_prepared_xacts    ORDER BY TRANSACTION::TEXT::int8 limit 1    )SELECT b.m - least(a.m, c.m)    ,d.*    ,e.*FROM a    ,b    ,c    ,dLEFT JOIN e ON (1 = 1);

50、查看WAL的空间占用大小

postgres=# select pg_size_pretty(sum(size)) from pg_ls_waldir();

文中的概念来源于互联网，如有侵权，请联系我删除。

欢迎关注公众号：小周的数据库进阶之路，一起交流数据库、中间件和云计算等技术。如果觉得读完本文有收获，可以转发给其他朋友，大家一起学习进步！感兴趣的朋友可以加我微信，拉您进群与业界的大佬们一起交流学习。

原文来源：https://mp.weixin.qq.com/s/uPF2o8w4p1_yRgZp6UAozg