postgresql 常用命令

psql登录，需要输入密码：
psql -h10.2.30.28 -p5432 -Uroot -d postgres


psql登录，不需要输入密码：
export PGPASSWORD=root
psql -h10.2.30.28 -p5432 -Uroot -d postgres

或者：
PGPASSWORD=root  psql -h10.2.30.28 -p5432 -Uroot -d postgres


通过psql向数据库发起SQL命令
psql  -c "SELECT current_user; "

如果想仅显示命令返回的结果，psql加上-At选项即可：
psql  -At -c "SELECT current_user; "


导入数据库脚本：
psql mydb pguser -f script/test_2.sql


退出psql
\q


进入某个数据库
psql –d dbname
或
\c dbname

列出所有的 schema
\dn

列出所有的数据库用户和角色
\du
或
\dg


查看所有数据库
\l

获取数据库的建表语句（-s参数只导表结构，不导数据）：
export PGPASSWORD=root
pg_dump -h10.2.29.66 -p5432 -Uroot  -s test980500 > test980500.sql



显示每个匹配关系（表、视图、索引、序列）的信息
\d [ pattern ]

不加任何参数表示查看当前数据库的所有表。
可以加上 + 查看更多的信息，如 \d+
后面跟一个表名，表示显示这个表的结构定义

\d tablename

也可以显示索引的信息，如 \d 索引名称
\d indexname

后面也可以跟通配符如 "*" 或 "?"，\d x*
\d *

将显示比 \d 更详细的信息，还会显示任何与表关系的注释，以及表中出现的 OID
\d+

只显示匹配的表
\dt

查看表大小
\dt+ tablename

只显示索引
\di

查看索引大小
\di+ indexname

只显示序列
\ds

只显示视图
\dv

只显示函数
\df

查看函数定义
\sf function_name

显示 SQL 已执行的时间，默认情况下是 off
\timing on或off


显示所有的表空间，表空间其实是一个目录，放在这个表空间的表，就是把表的数据文件发到这个表空间下。
\db

显示表的权限分配情况
\dp

或
\z

指定客户端的字符编码
\encoding UTF8

\encoding gbk;


设置输出的格式
\pset
\pset border 0 : 表示输出内容无边框。 border 1 ：表示边框只在内部。border 2 ：内外都有边框

把表中的每一行的每列数据都拆分为单行展示，与 MySQL 中的 "\G" 的功能类似。
\x


用于输出一行信息，通常用于在 .sql 文件中输出一些提示信息。
\echo


设置密码
\password


列出当前数据库连接的信息
\conninfo


查看数据库中安装的扩展 或 select * from pg_extension;
\dx


查看当前用户
\c
select current_user;


查看所有用户
select * from pg_user;
select * from pg_shadow;

查看所有角色
\du

select * from pg_roles;


显示SQL执行时间
\timing


反复执行当前SQL，seconds表示两次执行间隔的时间，以秒为单位，默认为2秒
\watch [ seconds ]


\i 执行外部的SQL


查看活动连接列表及进程ID
SELECT * FROM pg_stat_activity;


查看数据库用户连接数
select datname,usename,state,count(*) from pg_stat_activity group by datname,usename,state order by 1,2,3,4;


查看长时间运行的SQL
SELECT datname,pid,usename,query_start,STATE,left(query,40) query,now()-query_start
FROM pg_stat_activity
WHERE STATE<>'idle' AND (backend_xid IS NOT NULL OR backend_xmin IS NOT NULL)
ORDER BY now()-query_start;


终止执行时间超过10分钟的sql
select pg_terminate_backend(pid) from pg_stat_activity where   clock_timestamp()-query_start >  '10 min' and backend_type='client backend';


终止超过10分钟的长事务会话
select pg_terminate_backend(pid) from pg_stat_activity where   clock_timestamp()-xact_start >  '10 min' and backend_type='client backend';


取消正在长时间执行的SQL命令的方法有以下两种。
先查询pg_stat_activity以找出长时间运行的SQL命令
select pid,usename,query_start, query from pg_stat_activity;

##取消正在执行的sql
select pg_cancel_backend(pid);

##终止一个后台服务进程，同时释放后台服务进程资源
select pg_terminate_backend(pid); 

前者是给正在执行的SQL配置一个取消标志，正在执行的任务在合适的时候检查到就主动退出，如果没有检测到该任务无法正常退出，这时需要用后者来执行。

终止某个用户所有连接
SELECT pg_terminate_backend(pid)  FROM pg_stat_activity  WHERE username='testuser';


查看数据库活动会话：
SELECT pid, usename, datname, query, client_addr
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() AND state='active' ORDER BY query;


查询等待事件：
SELECT pid, usename, datname, query, client_addr, wait_event_type, wait_event
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() AND wait_event is not null
ORDER BY wait_event_type;


查看表的统计信息
select relowner::regrole,relname,relkind,relpages,reltuples from pg_class where relname='t1';


查看膨胀高的表TOP 10(碎片)
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  order by 5  desc LIMIT 10;


查看是否归档
show archive_mode;

查看 XXX 数据库的大小
SELECT pg_size_pretty(pg_database_size('XXX')) As fulldbsize;


查看所有数据库的大小
select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;


查看表的大小
\dt+  test_tab

pg_relation_size()仅计算表的大小，不包括索引的大小。
pg_total_relation_size()则会把表上索引的大小也计算进来。

select pg_size_pretty(pg_relation_size('test_tab')) ;

select pg_size_pretty(pg_total_relation_size('test_tab')) ;

查看表上所有索引的大小
select pg_size_pretty(pg_indexes_size('test_tab'));


查看schema下各表数据量
select relname,pg_size_pretty(pg_total_relation_size(relid)) from pg_stat_user_tables where schemaname ='public' order by pg_total_relation_size(relid) desc;


查看表空间的大小
select pg_size_pretty(pg_tablespace_size('pg_global'));

select pg_size_pretty(pg_tablespace_size('pg_default'));


查看表对应的数据文件
select pg_relation_filepath('test_tab');


查看各数据库数据创建时间：
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;


获取元命令对应的SQL代码
psql提供的元命令实质上向数据库发出相应的SQL查询，当使用psql连接数据库时，-E选项可以获取元命令的SQL代码，如下所示：
psql -h10.2.30.28 -p5432 -Uroot -d postgres  -E

postgres=# \db
********* QUERY **********
SELECT spcname AS "Name",
  pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
  pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace
ORDER BY 1;
**************************

      List of tablespaces
    Name    | Owner | Location 
------------+-------+----------
 pg_default | root  | 
 pg_global  | root  | 
(2 rows)
postgres=# \l
********* QUERY **********
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************

                             List of databases
   Name    | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
-----------+-------+----------+------------+------------+-------------------
 postgres  | root  | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | root  | UTF8     | en_US.utf8 | en_US.utf8 | =c/root          +
           |       |          |            |            | root=CTc/root
 template1 | root  | UTF8     | en_US.utf8 | en_US.utf8 | =c/root          +
           |       |          |            |            | root=CTc/root
(3 rows)


查看当前数据库实例的版本信息
select version();


查看数据库的启动时间
select pg_postmaster_start_time();


查看最后load配置文件的时间
select pg_conf_load_time();


显示当前数据库时区
show timezone;


查询当前连接的数据库名称
select current_catalog, current_database();


查看当前用户
select user;

select current_user;


查询当前session所在客户端的IP地址及端口
select inet_client_addr(),inet_client_port();


查询当前数据库服务器的IP地址及端口
select inet_server_addr(),inet_server_port();


查看数据库实例是否正在做基础备份
select pg_is_in_backup(), pg_backup_start_time() ;


查看当前数据库实例处于Hot Standby状态还是正常数据库状态
select pg_is_in_recovery();