pg常用运维命令

常用命令
\d 显示当前数据库中的所有的表、视图、sequence
\d test01 显示test01表的详情
\dt 显示当前数据库中的所有的表
\dt test* 只列出test开头的表
\db+ 查看pg中所有表空间
\dn 显示所有Schema
\dv 显示视图
\di 显示索引
\ds 显示序列号
\df 显示函数
\du, \dg 列出数据库所有角色或用户
\dp, \z 显示所有表和视图的权限分配情况
\dS, 显示pg_catalog 下的所有系统视图和系统表


1.从information_schema.tables获取所有表和视图，包括information_schema和pg_catalog的表和视图。
select table_catalog,table_schema,table_name,table_type from information_schema.tables;

select count(1) from information_schema.tables;

                 
          

pg  catalog是系统级的schema，用于存储系统函数和系统元数据。每个database创建好以后默认都会含有两个catalog：
一个名为pg_catalog，用于存储 PostgreSQL 系统自带的函数、表、系统视图、数据类型转换器以及数据类型定义等元数据；
另一个是information_schema，用于存储 ANSI 标准中所要求提供的元数据查询视图，这些视图遵从 ANSI SQL 标准的要求，
以指定的格式向外界提供 PostgreSQL 元数据信息


create schema <schema_name>;  #创建schema
select current_schema;   #确认当前schema




查进程：
select * from pg_stat_activity where usename <> 'dbajmcc' and query <> '';



杀进程：
select pg_cancel_backend(线程id);  
来kill掉指定的SQL语句。取消后台操作，回滚未提交事物,不会中断session


select pg_terminate_backend(pid)
取消后台操作，回滚未提交事物，并且中断session



==========================================================================================================================================

test_jiwei=# SHOW search_path;   //可以查看当前库下面的搜索路径
   search_path   
-----------------
 "$user", public                 //如果没有指定schema.表名，那么默认是和该库的owner同名的schema，如果也没有，则再是public这个shcema
(1 row)


SELECT * FROM information_schema.schemata;     //查看当前数据库下的所有schema

SELECT * FROM information_schema.tables;       //查看当前数据库下的所有表


# 查看表的大小
SELECT 
    nspname AS schema_name,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,    -- 正常数据+索引+大字段数据+碎片空间（物理大小）
	pg_size_pretty(pg_relation_size(c.oid)) AS data_size,           -- 正常数据
	pg_size_pretty(pg_indexes_size(c.oid)) AS table_index_size      -- 索引
FROM 
    pg_class c
JOIN 
    pg_namespace n ON n.oid = c.relnamespace
WHERE 
    c.relkind = 'r'  -- 'r' 表示普通表
	and c.relname = ('face_embedding')
ORDER BY 
    pg_total_relation_size(c.oid) DESC;




查看参数的命令
show 参数名;
或
select name, setting, pending_restart from pg_settings where name like '%参数名%';

修改系统参数命令
ALTER SYSTEM SET shared_buffers TO '512MB';   #修改的参数会记录到postgresql.auto.conf中，不会改变postgresql.conf中的值

修改会话级别参数命令
SET shared_buffers TO '512MB'; 


查看是主库还是备库
登录数据库执行select pg_is_in_recovery()函数，如果返回t说明是备库，返回f说明是主库




====================================================查看库表oid====================================
1、库的oid
select * from pg_database where datname = 'jiweisun';
select datname,oid from pg_database;
  
2、表的oid
select relname,oid from pg_class where relname in ('aa'); 

3、查出表的存储位置
select pg_relation_filepath('表名');

===================================================================================================

备份

逻辑导出
pg_dump      

pg_dumpall   


物理备份
pg_basebackup     会备份数据和wal（这个是归档日志）
备份会生成两个目录文件，一个base目录，存的是数据，一个pg_wal,存的是归档日志


pg_basebackup -D /data1/pgsql/data -F -P -Xs -Upostgres  -h10.65.101.192 -p5432 

-D  备份到哪个目录
-Ft  打tar包
-F, --format=p|t       #output format (plain (default), tar)

-Pv
-P, --progress         #show progress information
-v, --verbose          #output verbose messages


-Xs 
-X, --wal-method=none|fetch|stream  #include required WAL files with specified method


-R, --write-recovery-conf  #write configuration for replication 


恢复：
类似于MySQL的xbk
把base目录拷贝到数据目录下，把pg_wal拷贝到归档日志目录下

然后到数据目录下,vim postgresql.auto.conf 

把 restore_command = 'cp /archive/%f %p'
   recovery_target = 'immediate'
   recovery_target_xid='事务号'
   
写到文件中，保存退出




========================================================

4、pg系统表介绍
\d 命令可以查看有哪些系统表，pg13版本一共有129个系统表、视图和sequence），系统表之间基本上都是以oid关联
PostgreSQL的每一个库中都有自己的一套系统表，其中大多数系统表都是在数据库创建时从模板数据库中拷贝过来的，
因此这些系统表里的数据都是与所属数据库相关的。
只有少数系统表是所有数据库共享的（比如pg_database），这些系统表里的数据是关于所有数据库的。


注：\? 命令可以查看各种命令

4.1 pg_class系统表
该表存储的是数据库所有的对象信息（relkind 字段值：r=ordinary table，i = index，S = sequence，v = view等等）

4.2 pg_am 系统表
该系统表存储的是系统支持的索引的访问方法（如btree，hash,gist，gin等索引）

4.3 pg_attribute系统表
该系统表存储的是数据表列的详细信息

4.4 pg_authid系统表
该表存储的是数据库用户的详细信息

4.5 pg_auth_memb                  ers系统表
该表存储的是数据库用户之间的关系

4.6 pg_database系统表
该表存储的是数据库的信息

4.7 pg_index系统表
该表存储的是索引信息  

4.8 pg_namespace系统表
该表存储的是数据库中的schema信息（pg中称为namespace，命名空间是SQL92模式下层的结构：每个名字空间有独立的关系、类型等集合，但并不会相互冲突。PostgreSQL的名字空间层次是：数据库.模式.表.属性。）       

4.9 pg_tablespace系统表



5、pg系统视图介绍
5.1 pg_group
存储的是用户组的信息

5.2 pg_indexes
存储的是索引详细信息
schemaname | public
tablename  | aa
indexname  | id_index
tablespace | 
indexdef   | CREATE INDEX id_index ON public.aa USING btree (id)

5.3 pg_locks
存储的是锁信息

5.4 pg_roles
存储的是角色信息

5.5 pg_tables
存储的是表对象信息
schemaname  | siyou
tablename   | bb
tableowner  | jiwei
tablespace  | 
hasindexes  | f
hasrules    | f
hastriggers | f
rowsecurity | f 



select pg_reload_conf() 函数
修改配置文件pg_hba.conf或者postgres.conf之后，在psql里执行这个函数可以使修改生效

或者是用pg_ctl -D /data1/pgsql/data reload 命令也可以生效



6.4 数据库对象管理函数
pg_column_size(any) int 存储一个指定的数值需要的字节数（可能压缩过）
pg_database_size(oid) bigint 指定OID的数据库使用的磁盘空间
pg_database_size(name) bigint 指定名称的数据库使用的磁盘空间
pg_indexes_size(regclass) bigint 关联指定表OID或表名的表索引的使用总磁盘空间
pg_relation_size(relation regclass, fork text) bigint 指定OID或名的表或索引，通过指定fork('main', 'fsm' 或'vm')所使用的磁盘空间
pg_relation_size(relation regclass) bigint pg_relation_size(..., 'main')的缩写
pg_size_pretty(bigint) text Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units
pg_size_pretty(numeric) text 把以字节计算的数值转换成一个人类易读的尺寸单位
pg_table_size(regclass) bigint 指定表OID或表名的表使用的磁盘空间，除去索引（但是包含TOAST，自由空间映射和可视映射）
pg_tablespace_size(oid) bigint 指定OID的表空间使用的磁盘空间
pg_tablespace_size(name) bigint 指定名称的表空间使用的磁盘空间
pg_total_relation_size(regclass) bigint 指定表OID或表名使用的总磁盘空间，包括所有索引和TOAST数据







7、postgres进程结构
postmaster --所有数据库进程的主进程（负责监听和fork子进程）
startup    --主要用于数据恢复的进程
syslogger  --记录系统日志的进程
pgstat     --收集统计信息
pgarch     --如果开启了归档，那么postmaster会fork一个归档进程
checkpointer   --负责检查点的进程
bgwriter    --负责把shared buffer 中的脏数据写入磁盘的进程
autovacuum lanucher   --负责垃圾回收的进程，如果开启了autovacuum，postmaster会fork这个子进程
autovacuum worker     --负责垃圾回收的worker进程，是launcher进程fork出来的


8、postgres物理结构
8.1主要讲一下存储结构
select * from pg_relation_filepath('aa');  查看aa这张表所在的目录

jiweisun=# select * from pg_relation_filepath('aa');
-[ RECORD 1 ]--------+-----------------
pg_relation_filepath | base/16386/16388


8.2 几个比较重要的内存参数参数
shared_buffers=128m     databuffer，相当于mysql  buffer  pool，一般大小设置为机器内存的25%，最大不要超过50%
wal_buffers=-1          wal日志 buffer，相当于mysql redo buffer, min 32kB, -1 sets based on shared_buffers
work_mem=4m             为每一个连接在join/排序/hash 等操作所需要的内存
max_connections = 100   默认值是100，有点小，需要增大，单节点2000以内差不多


8.3日志文件种类
$PGDATA/log  运行日志，pg10之前叫作$pgdata/pg_log
$PGDATA/pg_wal
$PGDATA/pg_xact 事务提交日志，pg10之前叫做pg_clog
服务器日志，可以在启动时指定，比如 pg_ctl start -l /alert.log



8.4 wal 日志命名格式
文件名为16进制的24个字符组成，每8个字符一组，每组意义如下
00000001 00000000 00000005
时间线     逻辑id   物理id


8.5 wal相关函数
select pg_walfile_name(pg_current_wal_lsn());    查看当前lsn所在的wal文件名
select * from pg_ls_waldir() order by modification asc;   查看各个wal文件最后修改时间
select pg_switch_wal();     切换wal日志

8.6 pg_waldump 查看wal 内容
pg_waldump 文件名





8、用户管理
create user jiwei with [encrypted] password '1234';
create role  和create user 一样，只是创建出来的账号没有登录权限，可以加个  with login;

postgres=# \help create user;
Command:     CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

    SUPERUSER | NOSUPERUSER：创建出来的用户是否为超级用户
	CREATEDB | NOCREATEDB：创建出来的用户是否有create database的权限
	CREATEROLE | NOCREATEROLE：创建出来的用户是否有创建其它角色的权限
	CREATEUSER | NOCREATEUSER：创建出来的用户是否有创建其它用户的权限
	INHERIT | NOINHERIT：确定角色是否继承其它角色的权限
	LOGIN | NOLOGIN：创建出来的角色是否有登录权限
	CONNECTION LIMIT n：创建出来的角色并发连接数限制数量，默认值是“-1”,表示没有限制
	VALID UNTIL 'timestamp'：密码失效时间
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL

    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

URL: https://www.postgresql.org/docs/13/sql-createuser.html

删除用户  drop user jiwei;

修改用户权限、密码等
alter user jiwei  with password '123456';


postgres=# \h alter user;
Command:     ALTER USER
Description: change a database role
Syntax:
ALTER USER role_specification [ WITH ] option [ ... ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'

ALTER USER name RENAME TO new_name

ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT 
}
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL

where role_specification can be:

    role_name
  | CURRENT_USER
  | SESSION_USER

URL: https://www.postgresql.org/docs/13/sql-alteruser.html


9、权限管理

说明：在postgresql数据库中，任何逻辑对象(包括数据库)都是有所有者的，也就是说数据库对象都是属于某个用户的，
所以，无需把对象的权限赋予所有者，因为所有者默认就拥有所有的权限，在PG数据库中，删除及其修改对象的权限都不能赋予别的用户，
它是所有者的固有权限，不能赋予或撤销，所有者也隐式地拥有把操作该对象的权限授予其他用户的权利。

#回收public权限
在初始化数据库实例后，数据库中默认就会存在一个名称为“public”的schema，任何用户都有在public schema上的create 权限，
通常我们需要把这个权限回收回来
revoke create on schema recharge from public;




说明：两类权限
用户的权限分两类，一类是在创建用户时就指定的权限，有：

超级用户的权限
创建数据库的权限
是否允许login的权限
更多见\help create role
这些权限是创建用户时指定的，后面可以使用alter role来修改。

另一类是由GRANT和REVOKE命令来管理的，有：
在数据库中创建schema的权限
连接某个数据库的权限
在某个数据库中创建数据库对象的权限，如表、视图、函数等
在一些表中做SELECT 、INSERT、UPDATE、DELETE、truncat、excute、trigger、REFERENCES、usage等操作的权限



9.1权限级别
cluster权限：实例级别的权限，通过pg_hba.conf配置
database权限：数据库权限，通过grant和revoke操作schema配置
TBS权限：表空间权限，跟database权限类似，通过grant和revoke操作表配置
schema权限：模式权限通过grant和revoke操作模式下的对象配置
object权限：对象权限，通过grant和revoke配置

9.2授权命令
grant all privileges on database 数据库名 to 用户名;   #示例赋予最大权限
grant select, insert, update, delete on 表名 to 用户名; #赋予指定权限
revoke select, insert, update, delete on 表名　from 用户名; #收回权限



10、用户、权限、建库例子
10.1 create user jiwei with password '1234';   #不给权限，这样创建出的用户是没有任何权限的。

10.2 create database jiwesun;                  #这样创建的库默认owner 是postgres账号
     create database jiweisun owner jiwei;     #指定创建出来的库的owner
10.3 alter database jiweisun owner to jiwei;  #修改库的owner为jiwei




11、创建用户完整流程例子
#创建用户
create user query encrypted password 'kol6xw2oxd6rfnwm';

#把schema授权给用户，要先切换到recon schema 所在的库
grant usage on schema recon to query;    # usage 是 有使用该schema 下 数据库对象（例如函数，视图，数据类型，索引等，不包括表）的权限
-- 如果是schema 要一个一个授权
grant usage on schema rmseouter to query;

grant select on all tables in schema recon to query;
-- 指定某些具体表
grant select on myschema.mytable to query;

#自动授予未来新创建表的查询权限
alter default privileges for user postgres in schema recon grant select on tables to query;
alter default privileges for user recharge in schema recharge grant all privileges on tables to recharge;
alter default privileges for user recharge in schema recharge grant all privileges on sequences to recharge;


#查看用户的权限

查看用户的系统权限：
SELECT * FROM pg_roles WHERE rolname = 'your_username';


查看用户在表上的权限：
SELECT * FROM information_schema.table_privileges WHERE grantee = 'ctp';


查看用户在存储过程和函数上的权限：
SELECT * FROM information_schema.routine_privileges WHERE grantee = 'your_username';