第七周作业

1. 总结pg和mysql的优劣势。

   PostgreSQL优势 PostgreSQL完全免费，BSD协议，开源软件很多，容易实现读写分离，负载均衡，数据水平拆分等方案，支持复制查询，支持用户自定义类型或域，支持sequence,PostgerSQL是多进程的，当并发高时，总体处理性能强，DDL支持事务，explain返回信息丰富，可以支持秒以下的存储类型

   劣势 并发不高时，Mysql处理速度快

2. 总结pg二进制安装和编译安装。

   二进制安装包进行安装

   各linux发行版中大多都内置了PGsql的二进制安装包，但内置版本相对旧一些，对于二进制包的安装方法是通过不同发行版本的Linux下的包管理器进行的，如在THEL系统相关版本下用yum命令，在Ubuntu下用apt命令。

   源码编译安装

   使用源码编译安装更为灵活，用户可以有更多的选择，可以选择较新的版本、配置不同的编译选项，编译出用户喜欢的功能

   sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
   sudo dnf -qy module disable postgresql
   sudo dnf install -y postgresql16-server
   sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
   sudo systemctl enable postgresql-16
   sudo systemctl start postgresql-16
   

3. 总结pg服务管理相关命令 pg_ctl 和pgsql命令选项及示例和不同系统的初始化操作

   初始化数据库 initdb -D $PGDATA #

   -D 指定数据库实例的数据目录，使用环境变量PGDATA指定的路径 pg_ctl init

   查看服务状态 pg_ctl status #-D

   启动服务 pg_ctl -D $PGDATA -l logfile start

   -D datadir#指定数据库实例的数据目录 -l #服务器日志输出到logfile中

   停止数据库服务 pg_ctl stop -D $PGDATA -m #指定数据库停止方法 smart:等待所有连接终止后，关闭数据库 fast:快速关闭数据库，断开客户端的连接 immediate:立刻关闭数据库

   重启服务 pg_ctl restart

   加载配置 pg_ctl reload

   将从服务器提升为主服务器，恢复读写操作 pg_ctl promote

   psql命令 psql -h -p<端口> [数据库名称] -U [用户名称] -h #指定要连接的数据库主机名或IP地址 -p #指定连接的数据库端口

4. 总结pg数据库结构组织

   数据的组织结构可以分为五层：

   实例：一个postsql对应安装的数据目录$PGDATA,即一个instance实例

   数据库：一个postgresql数据库服务下可以管理多个数据库，当应用连接到一个数据库时，一般只能访问这个数据库中的数据

   模式：一个数据库可以创建多个不同的名称空间Schema,用于分隔不同的业务数据

   表和索引：一个数据库可以有多个表和索引，在postgressql中的术语为Relation,在其他数据库叫Table

   行和列：每张表有很多列和行数据，在postgresql的术语Tuple,在其他数据库叫Row

5. 实现pg远程连接。输入密码和无密码登陆

   修改用户的密码
   postgres=# ALTER USER postgres with password '123456';
   编辑配置文件
   [postgres@Rocky8 ~]$ vi /pgsql/data/postgresql.conf 
   #listen_addresses = '127.0.0.1'          # what IP address(es) to listen on;
   listen_addresses = '*'          # what IP address(es) to listen on;
   [postgres@Rocky8 ~]$ vi /pgsql/data/pg_hba.conf 
   # replication privilege.
   local   replication     all                                     trust
   host    replication     all             127.0.0.1/32            trust
   host    all             all             0.0.0.0/0               md5
   host    replication     all             ::1/128                 trust
   重启服务
   [postgres@Rocky8 ~]$ pg_ctl restart -mf
   
   输入密码登录：
   [root@Rocky8 ~]# psql -d postgres -h 10.0.0.4 -p 5432 -U postgres
   WARNING: password file "/root/.pgpass" has group or world access; permissions should be u=rw (0600) or less
   Password for user postgres: 
   
   无密码登录
   [root@Rocky8 ~]# cat .pgpass 
   10.0.0.4:5432:postgres:postgres:123456
   [root@Rocky8 ~]# chmod 600 .pgpass 
   [root@Rocky8 ~]# ll .pgpass 
   -rw-------. 1 root root 39 Oct 18 15:02 .pgpass
   [root@Rocky8 ~]# psql -U postgres -h 10.0.0.4 
   psql (14.9, server 14.2)
   
   postgres=# \q
   

6. 总结库，模式，表的添加和删除操作。表数据的CURD。同时总结相关信息查看语句。

   表的添加：

   通用：CREATE DATABASE ； PostgreSQL专用：CREATEDB

   表的删除：

   DROP DATABASE

   查看数据库相关命令：

   (1)列出所有数据库：\l

   (2)查看当前连接信息：\c

   (3)查看详细的信息：\conninfo

   (4)连接数据库：\c db_name 相当于mysql中的use

   查看表和表信息命令：

   (1)列出所有表，视图，序列：\d

   (2)查看表信息：\dt tb_name

   (3)查看表结构：\d tb_name 相当于mysl中的desc

7. 总结pg的用户和角色管理。

   创建用户
   create user name [with] option
   创建角色
   create role name [with] option #创建的角色默认无法连接
   #修改用户
   alter user
   #删除用户
   drop user
   #显示所有的用户和角色
   \du
   

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

   postgres=# create user mage password '123456';
   CREATE ROLE
   postgres=# create database zabbix owner mage;
   CREATE DATABASE
   postgres=# create schema magedu autho
   
   postgres=# create schema magedu AUTHORIZATION mage;
   CREATE SCHEMA
   postgres=# \du
                                      List of roles
    Role name |                         Attributes                         | Member of 
   -----------+------------------------------------------------------------+-----------
    ju        |                                                            | {}
    mage      |                                                            | {}
   

9. 总结pgsql的进程结构，说明进程间如何协同工作的。

   

   Postmaster主进程和服务进程，当PG数据库启动时，首先会启动Postmaster主进程。

   BgWriter 后台写进程
   当往数据库中插入或更新数据时，并不会马上把数据持久化到数据文件中，而是先写入Buffer中
   该辅助进程可以周期性的把内存中的脏数据刷新到磁盘中

   WalWriter 预写式日志进程
   修改数据之前，把修改操作记录到磁盘中，不需要实时的把数据持久化到文件中，即使机器突然宕机或服务器异常退出，导致一部分内存的脏数据没有及时刷新到文件中，在数据库重启时，通过读取WAL日志，并把最后一部分WAL日志重新执行一遍，就能恢复宕机时的状态
   保留数据完整性，写日志是顺序写入速度快

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

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

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

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

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

SysLogger 系统日志进程

默认没有此进程,配置文件 postgresql.conf 设置参数logging_collect设置为"on"时，主进程才会启动SysLogger辅助进程
它从Postmaster主进程、所有的服务进程以及其他辅助进程收集所有的stderr输出，并将这些输出写入到日志文件中

startup 启动进程
用于数据库恢复的进程

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

10.总结pgsql的数据目录中结构，说明每个文件的作用，并可以配上一些示例说明文件的作用。

base/：存储每个数据库的基本数据文件
存储每个数据库的基本数据文件。每个数据库都有一个以其 OID（对象标识符）命名的子目录，里面包含了该数据库的所有表和索引的数据文件。
global/：包含了全局性质的系统表空间文件
包含了全局性质的系统表空间文件。这里存放了所有数据库共享的系统表，如 pg_database、pg_authid 等。
pg_tblspc/：包含了表空间的符号链接
包含了表空间的符号链接。每个符号链接指向实际的表空间目录，表空间是用于组织数据库物理存储的一种方式。
pg_twophase/：包含了两阶段提交中使用的文件
包含了两阶段提交中使用的文件。两阶段提交用于确保分布式事务的一致性。
pg_stat_tmp/：包含了一些临时文件，用于存储统计信息
pg_stat/：包含了 PostgreSQL 收集的统计信息文件
包含了 PostgreSQL 收集的统计信息文件。这些文件记录了数据库服务器运行时的性能统计信息，如查询计划、锁等。
pg_logical/：包含了用于逻辑复制的文件
包含了用于逻辑复制的文件。逻辑复制允许将特定表、特定数据库对象或特定的数据更改复制到另一个数据库。
pg_replslot/：包含了复制插槽信息的文件
包含了复制插槽信息的文件。复制插槽用于流复制中，确保备用节点能够持续接收主节点的 WAL（Write-Ahead Logging）。
pg_subtrans/：包含了用于存储子事务信息的文件
包含了用于存储子事务信息的文件。子事务用于处理并发事务中的多个子操作。
pg_notify/：包含了用于存储异步通知信息的文件。
包含了用于存储异步通知信息的文件。异步通知允许数据库中的一个会话通知其他会话有关特定事件的发生。
pg_snapshots/：包含了用于存储快照信息的文件。
包含了用于存储快照信息的文件。快照是一种数据库的一致性视图，用于支持可重复读事务隔离级别。
pg_serial/：包含了用于存储序列信息的文件
包含了用于存储序列信息的文件。序列是 PostgreSQL 中生成唯一标识符的一种方式。
除了上述目录，还有一些重要的配置文件：

postgresql.conf：存放着 PostgreSQL 服务器的配置参数，如端口号、日志设置等。

pg_hba.conf：存放着 PostgreSQL 的身份验证规则，定义了哪些主机和用户能够连接到数据库，以及使用哪种身份验证方法。

pg_ident.conf：存放着标识映射规则，用于将操作系统用户映射到 PostgreSQL 数据库用户。