标签：常用 运维 database 数据库 pg 权限 select schema
常用命令
\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; //查看当前数据库下的所有表
查看参数的命令
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';
标签：常用,
运维,
database,
数据库,
pg,
权限,
select,
schema
From： https://www.cnblogs.com/sunjiwei/p/18567824