第7周作业

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

PostgreSQL主要优势：

1. PostgreSQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱，也没有人管 你，这一点很重要，这表明了PostgreSQL数据库不会被其它公司控制。oracle数据库不用说了，是商业数据 库，不开放。而MySQL数据库虽然是开源的，但现在随着SUN被oracle公司收购，现在基本上被oracle公司控制，其实在SUN被收购之前，MySQL中最重要的InnoDB引擎也是被oracle公司控制的，而在MySQL中很多重要的数据都是放在InnoDB引擎中的，反正我们公司都是这样的。所以如果MySQL的市场范围与oracle数据 库的市场范围冲突时，oracle公司必定会牺牲MySQL，这是毫无疑问的
2. 与PostgreSQl配合的开源软件很多，有很多分布式集群软件，如pgpool、pgcluster、slony、 plploxy等等，很容易做读写分离、负载均衡、数据水平拆分等方案，而这在MySQL下则比较困难。
3. PostgreSQL源代码写的很清晰，易读性比MySQL强太多了，怀疑MySQL的源代码被混淆过。所以很多公 司都是基本PostgreSQL做二次开发的。
4. PostgreSQL在很多方面都比MySQL强，如复杂SQL的执行、存储过程、触发器、索引。同时PostgreSQL 是多进程的，而MySQL是线程的，虽然并发不高时，MySQL处理速度快，但当并发高的时候，对于现在多核的
   单台机器上，MySQL的总体处理性能不如PostgreSQL，原因是MySQL的线程无法充分利用CPU的能力。

PostgreSQL的劣势：

复杂性：相对于MySQL，PostgreSQL的配置和管理可能更加复杂，需要更多的经验和技能。
性能：在某些情况下，MySQL可能具有更好的性能表现，尤其是在高并发的场景下。
兼容性：虽然PostgreSQL支持许多不同的扩展和功能，但与其他系统的兼容性可能不如MySQL。

MySQL的优势：

易用性：MySQL易于安装和使用，提供了直观的命令行界面和图形化管理工具，使得管理更加简单。
性能：MySQL在单线程和多线程应用中都有很好的性能表现，尤其是在高并发的场景下。
兼容性：MySQL与许多不同的编程语言和框架兼容，使得开发人员可以轻松地集成MySQL到他们的项目中。

MySQL相对于PostgreSQL的劣势：

1.最重要的引擎InnoDB很早就由Oracle公司控制。目前整个MySQL数据库都由Oracle控制。
2.对复杂查询的处理较弱，查询优化器不够成熟
3.只有一种表连接类型:嵌套循环连接(nestedloop),不支持排序-合并连接(sort-merge join)与散列连接(hash join)。
4.性能优化工具与度量信息不足
5.InnoDB的表和索引都是按相同的方式存储。也就是说表都是索引组织表。这一般要求主键不能太长而且插入时的主键最好是按顺序递增，否则对性能有很大影响。
6.大部分查询只能使用表上的单一索引;在某些情况下，会存在使用多个索引的查询,但是查询优化器通常会低估其成本,它们常常比表扫描还要慢。
7.表增加列，基本上是重建表和索引，会花很长时间。
8.存储过程与触发器的功能有限。可用来编写存储过程、触发器、计划事件以及存储函数的语言功能较弱
9.不支持Sequence。
10.不支持函数索引，只能在创建基于具体列的索引。 不支持物化视图。
11.执行计划并不是全局共享的, 仅仅在连接内部是共享的。 执行计划共享
12.MySQL支持的SQL语法(ANSI SQL标准)的很小一部分。不支持递归查询、通用表表达式（Oracle的with 语句）或者窗口函数（分析函数）。
13.不支持用户自定义类型或域(domain)。
14.对于时间、日期、间隔等时间类型没有秒以下级别的存储类型
15.身份验证功能是完全内置的，不支持操作系统认证、PAM认证，不支持LDAP以及其它类似的外部身份验证功能。
16.不支持database link。有一种叫做Federated的存储引擎可以作为一个中转将查询语句传递到远程服务器的一个表上,不过,它功能很粗糙并且漏洞很多
17.Mysql Cluster可能与你的想象有较大差异。开源的cluster软件较少。 复制(Replication)功能是异步的,并且有很大的局限性.例如,它是单线程的(single-threaded),因此一个处理能力更强的Slave的恢复速度也很难跟上
18.处理能力相对较慢的Master.
19.explain看执行计划的结果简单。
20.类似于ALTER TABLE或CREATE TABLE一类的操作都是非事务性的.它们会
21.提交未提交的事务，并且不能回滚也不能做灾难恢复

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

Rocky8 利用官方源安装 PostgreSQL-15
[root@Rocky8 ~]#sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
#禁用内置的postgresql
[root@Rocky8 ~]#sudo dnf -qy module disable postgresql
[root@Rocky8 ~]#sudo dnf install -y postgresql15-server

#初始化数据库
[root@Rocky8 ~]#sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

#启动服务
[root@Rocky8 ~]#sudo systemctl enable postgresql-15
[root@Rocky8 ~]#sudo systemctl start postgresql-15

#验证成功
[root@Rocky8 ~]#sudo -u postgres psql -c “SELECT version();”

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

初始化实例：pg_ctl init -D /pgsql/data2
-D DATADIR #指定数据库实例的数据目录。如果没有指定DATADIR,使用环境变量PGDATA指定的路径
查看状态：pg_ctl status -D /path/to/database
启动数据库：pg_ctl start -D /path/to/database
停止数据库：pg_ctl stop -D /path/to/database
重启数据库：pg_ctl restart -D /path/to/database

选项：
-D: 指定数据库配置文件的文件系统位置。
-l: 追加服务器日志输出到指定的文件。
-o: 指定被直接传递给 postgres 命令的选项。

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

在一个PostgreSQL 数据库系统中，数据的组织结构可以分为以下五层:
实例: 一个PostgreSQL对应一个安装的数据目录$PGDATA,即一个instance实例
数据库:一个PostgreSQL数据库服务下可以管理多个数据库，当应用连接到一个数据库时，一般只
能访问这个数据库中的数据，而不能访问其他数据库中的内容
默认情况下初始实例只有三个数据库： postgres、template0、template1
模式: 一个数据库可以创建多个不同的名称空间即Schema,用于分隔不同的业务数据
表和索引：一个数据库可以有多个表和索引。在PostgreSQL中表的术语称为 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)
Type “help” for help.

postgres=# \q

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

创建数据库 create database
#方法1 [root@ubuntu2004 ~]#createdb -h 10.0.0.200 -p 5432 -U postgres testdb Password:
#方法2 postgres@ubuntu2004:~$ psql postgres=# create database testdb;
删除数据库 postgres@ubuntu2004:~$ psql postgres=# drop database testdb;
#创建模式 create schema schema_name;
#删除模式 drop schema schema_name;

#列出所有schema postgres=# \dn

#列出所有数据库名,相当于MySQL中的show databases; postgres=# \l

#显示数据库详细信息,比如大小 testdb-# \l+ #查看当前连接信息 postgres=# \c

#查看当前连接详细信息 postgres=# \conninfo You are connected to database “postgres” as user “postgres” via socket in “/tmp” at port “5432”.
#连接数据库,相当于use postgres=# \c hellodb You are now connected to database “hellodb” as user “postgres”. hellodb=#

管理表 postgres=# \c testdb

查看表和表信息
#列出所有表,视图,序列 \d
#列出public的schema中所有的表名,相当于show tables; \dt
#查看t1的表信息 \dt t1
#支持通配符和？，以下显示所有t开头的表 \dt t
#列出myschema模式的表结构 \dt myschema.*
#查看t1的表结构,相当于desc \d t1
#列出所有表信息,包括大小 hellodb-# \dt+

#列出表信息 hellodb=# \dt students

#列出表信息的大小信息 hellodb=# \dt+ students

#查看所有表 postgres=# select * from pg_tables;

#查看表大小 testdb=# select pg_total_relation_size(‘tb1’);

表数据的CURD

INSERT UPDATA DELETE SELECT

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

创建用户与角色 CREATE USER name [[WITH] option [ …]] CREATE ROLE name
[[WITH] option [ …]]

#查看帮助 \h create user \h alter user \h drop user \h create role \h alter role \h drop role

create user #创建的用户默认可以连接 create role #创建的用户默认无法连接

#修改用户 alter user
#删除用户 drop user

#显出所有用户和角色
#\du和\dg命令等价。原因是在PostgreSQL数据库中用户和角色不分的。 \du \dg

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 |
| {} postgres | Superuser, Create role, Create DB, Replication,
Bypass RLS | {}

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

Postmaster 主进程

A.它是整个数据库实例的主控制进程，负责启动和关闭改数据库实例。

B.实际上在使用pg_ctl来启动数据库时，pg_ctl也是通过运行postgrestl来启动数据库的，只是它做了一些包装，更容易启动数据库。

C.它是第一个PGsql的进程，此主进程还会fork出其他子进程，并管理它们。

D.当用户和PGsql建立连接时，首先是和Postmaster进程建立连接，首先客户端会发出身份验证的信息给Postmaster进程，Postmaster进程根据消息中的信息进行身份验证判断，如果验证通过，它会fork出一个会话子进程为这个连接服务。

E.当有服务进程出现错误时，Postmaster主进程会自动完成系统的恢复。恢复过程中会停掉所有的进程，然后进行数据库的一致性恢复，待恢复完成后，数据库又可以接受新的连接。

F．验证功能是通过配置文件pg_hba.conf和用户验证模块来提供。

G.Postmaster程序是指向postagres的软连接

BgWriter后台写进程

1.为了提高，插入，删除和更新数据的性能，当数据更新时，并不会马上把数据持久化到数据文件中，而是先写入Buffer中。

2.改辅助进程可以周期性的把内存中的数据刷新到磁盘中。

3.WaWriter预写式日志进程

4.WAL是write ahead log的缩写，WAL log旧版中称为xlog相当于MYsql中的Redo log.

5.预写式日志是在修改数据之前，必须把这些修改记录到磁盘中，这样更新实际数据时,就不需要把数据实时到持久化到文件中,即使出现异常,导致部分数据没有刷新到文件中,在数据库重启时，通过读取WAL日志，把最后一部分WAL日志重新执行一遍，就能恢复到异常时的状态。

6.WAL日志保存在pg_wal目录中(早期版本为pg_xlog).文件默认16MB，为了满足恢复要求，在pg_log下会产生多个日志，未持久化的数据都可以通过WAL日志来恢复，那些不需要的WAL日志将会自动覆盖。

Checkpointer检查进程点。

6.检查点是事务序列中的点，保证在改检查点之前的日志信息都更新到文件中。

7.在检查点时，所有脏数据都冲刷到磁盘并向日志文件中写入一条特殊的检查点记录。

8.检查点进程（CKPT）在特定时间执行一个检查点，通过向数据库写入进程（BgWirter）传递消息来启动检查点请求。

AutoVacuum自动清理进程。

9.执行删除操作时，旧数据不会被立即删除，在更新数据时，也不会在旧数据上做更新，而是新生成一行数据。旧的数据只是被标识为删除状态，在没有并发的其他事务读到这些旧数据时，它们才会被清除掉。

10.Autovacuum launcher 负责回收垃圾数据的worker进程，如果开启了Autovacuum的话，Postmaster就会fork这个进程。

11.Autovacuum worker负责回收垃圾数据的worker进程,是launcher进程fork出来的。

PgStat统计数据收集进程。

12.此进程主要做数据的统计收集工作。

13.收集的信息主要用于查询优化时的代价估算。

14.系统表pg_statistic中存储了PgStat收集的各类统计信息。

PgArch归档进程

15.默认没有此进程，开启归档功能后才会启动改进程。

16.WAL日志文件会被循环利用，也就是说WAL文件会被覆盖，利用PgArxh进程会在覆盖前把WAL日志备份出来，类似于binlog,可以用来备份。

17.PGsql从8.0版本开始提供了PITR技术，就是对数据库做一次全量备份后，该技术将备份时间点后面的WAL日志通过备份归档来进行备份，将来可以用数据库的全量备份再加上后面产生的WAL日志，即可把数据库向前恢复到全量备份的任意一个时间点的状态。

Syslogger系统日志进程。

18.默认没有此进程，在配置文件postgresql.conf中将logging_collerct设置为‘on’时，主进程才会启动Syslogger辅助进程。

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

Starup启动进程。

20.用户数据库恢复的进程。

Session会话进程。

21.每一个用户发起连接后，一旦验证成功Postmaster进程就会fork一个新的子进程负责连接此用户。

22.通常表现为进程形式：postgres postgrest [local] idle

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

数据库数据存放在环境变量PGDATA指向数据目录。这个目录是在安装时指定的，所以在安装时需要指
定一个合适的目录作为数据目录的根目录，而且，每一个数据库实例都要有一个对应的目录。目录的初
始化是使用initdb来完成的。
初始化完成后，PGDATA数据目录下就会生成三个配置文件。
postgresql.conf #数据库实例的主配置文件，基本上所有的配置参数都在此文件中。
pg_hba.conf #认证配置文件，配置了允许哪些IP的主机访问数据库，认证的方法是什么等信息。
pg_ident.conf #认证方式ident的用户映射文件。
此外在PGDATA目录下还会生成如下一些子目录
base #默认表空间的目录,每个数据库都对应一个base目录下的子目录,每个表和索引对应一个独立文件
global #这个目录对应pg_global表空间,存放实例中的共享对象
pg_clog #存储事务提交状态数据
pg_bba.conf #数据库访问控制文件
pg_log #数据库系统日志目录，在查询一些系统错误时就可查看此目录下日志文件。(根据配置定义,可能没
有这个目录)
pg_xact #提交日志commit log的目录,pg 9之前叫pg_clog
pg_multixact #共享行锁的事务状态数据
pg_notify #异步消息相关的状态数据pg_serial #串行隔离级别的事务状态数据
pg_snapshots #存储执行了事务snapshot导出的状态数据pg_stat_tmp #统计信息的临时文件
pg_subtrans #子事务状态数据
pg_stat #统计信息的存储目录。关闭服务时,将pg_stat_tmp目录中的内容移动至此目录实现保存
pg_stat_tmp #统计信息的临时存储目录。开启数据库时存放统计信息
pg_tblsp #存储了指向各个用户自建表空间实际目录的链接文件
pg_twophase#使用两阶段提交功能时分布式事务的存储目录
pg_wal #WAL日志的目录,早期版一本目录为pg_xlog
PG_VERSION #数据库版本
postmaster.opts #记录数据库启动时的命令行选项
postmaster.pid #数据库启动的主进程信息文件，包括PID，SPGDATA目录,数据库启动时间，监听端口，
socket文件路径，临听地址,共享内存的地址信息(ipsc可查看),主进程状态

常用配置说明
listen_addresses='’ #监听客户端的地址，默认是本地的，需要修改为或者0.0.0.0
port = 5432 #pg端口，默认是5432
max_connections = 2000 #最大连接数，默认100
unix_socket_directories #socket文件的位置，默认在/tmp下面
shared_buffers #数据缓存区，建议值1/4–1/2主机内存，和Oracle的buffer cache类似
maintenance_work_mem #维护工作内存，用于vacuum,create index,reindex等。建议值(1/4主机
内存)/autovacuum_max_workers
max_worker_processes #总worker数
max_parallel_workers_per_gather #单条QUERY中，每个node最多允许开启的并行计算WORKER数
wal_level #wal级别，版本11+默认是replica
wal_buffers #类似Oracle的log buffer
checkpoint_timeout #checkpoint时间间隔
max_wal_size #控制wal的最大数量
min_wal_size #控制wal的最小数量
archive_command #开启归档命令，示例：‘test ! -f /arch/%f && cp %p /arch/%f’
autovacuum #开启自动vacuum

pg_ident.con用户映射配置文件
pg_ident.conf是用户映射配置文件。结合pg_hba.conf文件，method为ident可以用特定的操作系统用
户以指定的数据库用户身份登录数据库。
这个文件记录着与操作系统用户匹配的数据库用户，如果某操作系统用户在本文件中没有映射用户，则
默认的映射数据库用户与操作系统用户同名。比如，服务器上有名为user1的操作系统用户，同时数据
库上也有同名的数据库用户user1，user1登录操作系统后可以直接输入psql，以user1数据库用户身份
登录数据库且不需密码
如果操作系统用户和数据库用户不同名,可以用下面格式进行映射

数据文件说明

PostgreSQL中的每个索引和表都是一个单独的文件，称为Segment。默认是每个大于1G的Segment会
被分割pg_class.efilenode.1这样的文件。Segment的大小可以在initdb时通过选项—withsegsize=SEGSIZE指定
注意：truncate表之后relfilenode会变。对应的物理文件名字也会变

控制文件
控制文件存放了数据库当前的状态，存放在PGDATA/global/pg_control

日志文件
运行日志： $PGDATA/log(pg10之前为$PGDATA/pg_log),默认不存在，需要开启配置项
logging_collector
在线重做日志：$PGDATA/p{g}_{w}al(pg10之前为$PGDATA/pg_xlog)
事务提交日志：$PGDATA/p{g}_{x}act(pg10之前为$PGDATA/pg_clog)
服务器日志：可以在启动的时候指定:pg_ctl start -l ./logfile