【pgsql简单管理】

1、PG用户和角色

1.1、用户常见指令

#列出所有用户账号
\du     #可指定
\dg

#显示表，视图，序列的权限分配情况
\z   
\dp

#创建账号
create user wei WITH PASSWORD '123';                   #创建用户wei并设密码123
create role wei WITH PASSWORD '123';                   #创建角色，所创建账号wei不能直接登录
create role wei with login password '123';             #加上选项login可直接登录
alter user wei with login;                             #也可用此方法更改为可登录，不可登录改为nologin
create role wei with SUPERUSER login password '123';   #创建wei账号作为管理员
create role postgres with ENCRYPTED password '123';    #修改postgres账号的密码
\h create user                                         #查看帮助

#创建复制用户
create user repl replication login encrypted password '123456';

#修改密码
alter user wei with password '321'

#删除账号
srop user wei;

角色相当于组

1.2、权限

分为两类

• 一类在创建账号时直接添加可登录、是否管理员、可用于复制等等
• 另一类由grant和revoke来管理授权和取消授权

权限指令

hellodb=# \h 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
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | 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/14/sql-createuser.html

1.2.1、具体实现

数据库的所有者可对数据库里的所有资源进行管理增删改查

#授权wei用户可创建数据库
alter user wei with CREATEDB;

#授权wei用户可在数据库db1中创建资源，表、索引、视图等等
grant create on db1 testdb to wei;

#更改schema类别wang的拥有者为wei
alter schema wang OWNER to wei;
GRANT select,insert,update,delete ON ALL TABLES IN SCHEMA wang TO wei;

#创建test的schema指定拥有者为joe
CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;

#让wei用户来管理表testdb.t1进行增删改查
GRANT select,insert,update,delete ON testdb.t1 TO wei;

#创建数据库并指定所有者的用户
create user wei with password '123456';
CREATE DATABASE db2 OWNER wei;

举例

添加mage用户，magedu模式，准备zabbix库，配置mage用户的默认模式magedu，要求mage用户给zabbix库有所有权限。

postgres=# create database zabbix;
CREATE DATABASE
postgres=# \c zabbix
You are now connected to database "zabbix" as user "postgres".
zabbix=# create schema magedu;
CREATE SCHEMA
zabbix=# create user mage;
CREATE ROLE
zabbix=# alter schema magedu owner to mage;
ALTER SCHEMA
zabbix=# alter database zabbix owner to mage;

2、PostgreSQL体系架构

PostgreSQL和MySQL相似,也采用典型的C/S模型。

PostgreSQL体系结构分两部分

• 实例instance
• 磁盘存储
  • 共享内存
  • 私有内存

实例instance包括

• 进程
• 内存存储结构

2.1、进程

• Postmaster主进程

  • 它是整个数据库实例的主控制进程，负责启动和关闭该数据库实例。
  • 实际上,使用pg ct来启动数据库时，pg .ctI也是通过运行postgres来启动数据库的，只是它做了一些包装,更容易启动数据库。
  • 它是第一个PostgreSQL进程，此主进程还会fork出其他子进程,并管理它们。
  • 当用户和PostgreSQL建立连接时，首先是和Postmaster进程建立连接。首先，客户端会发出身份验证的信息给Postmaster进程，Postmaster进程根据消息中的信息进行身份验证判断,如果验证通过，它会fork出一 个 会话子进程为这个连接服务。
  • 当某个服务进程出现错误的时候，Postmaster主进程会自动完成系统的恢复。恢复过程中会停掉所有的服务进程，然后进行数据库数据的一致性恢复等恢复完成后，数据库又可以接受新的连接。
  • 验证功能是通过配置文件pg_.hba.conf和用户验证模块来提供。
  • postmaster 程序是指向postgres的软链接

• BgWriter后台写进程

  • 为了提高插入、删除和更新数据的性能，当往数据库中插入或者更新数据时，并不会马上把数据持久化到数据文件中,而是先写入Buffer中
  • 该辅助进程可以周期性的把内存中的脏数据刷新到磁盘中

• WalWriter预写式日志进程

  • WAL是write ahead log的缩写，WAL log旧版中称为xlog,相当于MySQL中Redo log

  • 预写式日志是在修改数据之前，必须把这些修改操作记录到磁盘中,这样后面更新实际数据时，就不需要实时的把数据持久化到文件中了。即使机器突然宕机或者数据库异常退出，导致一部分内存中的脏数据没有及时的刷新到文件中，在数据库重启后，通过读取WAL日志,并把最后一部分WAL日志重新执行一 遍， 就能恢复到宕机时的状态了

  • WAL日志保存在pg wal目录(早期版本为pg. xlog) 下.每个xlog文件默认是16MB,为了满足恢复要求，在pg. _wal目录下会产生多个WAL日志,这样就可保证在宕机后，未持久化的数据都可以通过WAL日志来恢复，那些不需要的WAL日志将会被自动覆盖

• Checkpointer 检查点进程

  • 检查点(Checkpoints)是事务序列中的点保证在该点之前的所有 日志信息都更新到数据文件中.
  • 在检查点时，所有脏数据页都冲刷到磁盘并且向日志文件中写入一条特殊的检查点记录。在发生崩溃的时候，恢复器就知道应该从日志中的哪个点(称做redo记录)开始做REDO操作,因为在该记录前的对数据文件的任何修改都已经在磁盘上了。在完成检查点处理之后,任何在redo记录之前写的日志段都不再需要，因此可以循环使用或者删除。在进行WAL归档的时候，这些日志在循环利用或者删除之前应该必须先归档保存，记录数据写入磁盘的时间点
  • 检查点进程(CKPT)在特定时间自动执行一个检查点,通过向数据库写入进程(BgWiter)传递消息来启动检查点请求

• AutoVacuum 自动清理进程

  • 执行delete操作时， 旧的数据并不会立即被删除，在更新数据时，也不会在旧的数据.上做更新，而是新生成一行数据。旧的数据只是被标识为删除状态，在没有并发的其他事务读到这些旧数据时，它们才会被清除掉
  • autovacuum lanucher负责回收垃圾数据的master进程如果开启了autovacuum的话，那么postmaster会fork这个进程
  • autovacuum worker负责回收垃圾数据的worker进程是lanucher进程fork出来的

• PgStat统计数据收集进程

  • 此进程主要做数据的统计收集工作
  • 收集的信息主要用于查询优化时的代价估算。统计的数据包括对一个表或索引进行的插入、删除、更新操作,磁盘块读写的次数以及行的读次数等。
  • 系统表pg. statistic中存储了PgStat收集的各类统计信息

• PgArch归档进程

  • 默认没有此进程,开启归档功能后才会启动archiver进程
  • WAL日志文件会被循环使用，也就是说WAL日志会被覆盖利用PgArch进程会在覆盖前把WAL日志备份出来，类似于binlog，可用于备份功能
  • PostgreSQL 从8.X版本开始提供了PITR ( Point-ln-Time- Recovery)技术，即就是在对数据厍进行过一-次全量备份后，该技术将备份时间点后面的WAL日志通过归档进行备份，将来可以使用数据库的全量备份再加上后面产生的WAL日志,即可把数据库向前恢复到全量备份后的任意一个时间点的状态

• SysLogger 系统日志进程

  • 默认没有此进程配置文件postgresql.conf设置参数logging. collect设置为"on’时,主进程才会启动SysLogger辅助进程

  • 它从Postmaster主进程、 所有的服务进程以及其他辅助进程收集所有的stderr输出，并将这些输出写入到日志文件中

• startup 启动进程

  • 用于数据库恢复的进程

• Session会话进程

  • 每一个用户发起连接后，一旦验证成功.postmaster进程就会fork一个新的子进程负责连接此用户。
  • 通常表现为进程形式: postgres postgres local] idle

2.2、数据更新过程

• 先将数据库文件中的更改的数据加载至内存
• 在内存更新数据
• 将日志写入内存WAL的缓存区
• 将日志提交，将日志写入操作系统cache
• 同步日志到磁盘
• 后台写数据库的更新后的数据到操作系统cache
• 写完数据后，更新检查点checkpoint
• 同步数据到磁盘

3、第七周作业

redis安装及简单应用
pgsql日志管理和备份
pgsql简介
日志服务
网络文件共享