postgresql-常用命令

转载自：https://www.modb.pro/db/403443

1、元命令

\?:  查看元命令的帮助。

\l: 列出所有数据库。

\encoding: 查看字符集。

\encoding 字符集: 设置字符集。

\password user_name: 修改用户密码。

\x: 以列显示的开关。相当于mysql中的\G。执行一次为打开，再执行一次为关闭。

\timing on｜off: 设置是否显示执行时长。

\set AUTOCOMMIT on|off: 打开/关闭自动提交功能。

\conninfo: 显示连接信息。

\! : 执行shell命令。如：\! date， 输出当前日期。

\i filename: 执行filename文件中的sql语句，也可用psql -s filename。

\q: 退出psql命令行环境。

\e：打开文本编辑器。

## \pset
\pset border 0/1/2：设置执行结果的边框样式。

\pset border 0: 输出内容无边框，无任何|
\pset border 1: 边框只在内部有，无外边框
\pset border 2: 内外都有边框

\gexec：将当前查询缓冲区发送到服务器，然后将查询输出（如果有的话）的每一行的每一列都要作为要执行的SQL语句处理。这个用法可参考：Psql之\gexec命令。


## \c
\c: 查看当前数据库和用户。查询当前数据库等同于select current_database();查询当前用户等同于select current_user;
\c db_name: 进入指定的数据库。
\c database user_name : 切换到某个数据库下某个角色

## \d
\dn: 列出当前库下所有schema。
\d: 查看当前数据库下的所有表、视图和序列。
\dt: 只查看数据库中的所有表。
\d tb_name： 查看表结构定义。
\dt+ tb_name: 查看表大小等属性。
\db: 查看表空间。
\du: 列出所有用户及其用户权限。
\ds: 查看用户自定义序列。
\df: 查看用户自定义函数。

2、常用SQL语句

## 查看数据库版本。
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;