第七周作业

目录

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

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

2.1 二进制包安装

2.2 源码编译安装

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

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

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

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

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

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

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

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

---

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

Mysql相对pg的优势：

• MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结构设计存在约束。
• MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作。
• MySQL相对于PG在国内的流行度更高，PG在国内显得就有些落寞了。
• MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，myisam适合静态数据的查询场景。

pg相对MYSQL的优势：

• 在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨。
• 存储过程的功能支持要比MySQL好，具备本地缓存执行计划的能力。
• 对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强。
• PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
• PG的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。
• MySQL的存储引擎插件化机制，存在锁机制复杂影响并发的问题，而PG不存在。
• PG对可以实现外部数据源查询，数据源的支持类型丰富。
• PG原生的逻辑复制可以实现表级别的订阅发布，可以实现数据通过kafka流转，而不需要其他的组件。
• PG支持三种表连接方式，嵌套循环，哈希连接，排序合并，而MySQL只支持嵌套循环。
• PostgreSQL源代码写的很清晰，易读性比MySQL强太多了。
• PostgreSQL通过PostGIS扩展支持地理空间数据。地理空间数据有专用的类型和功能，可直接在数据库级别使用，使开发人员更容易进行分析和编码。
• 可扩展型系统，有丰富可扩展组件，作为contribute发布。
• PostgreSQL支持JSON和其他NoSQL功能，如本机XML支持和使用HSTORE的键值对。它还支持索引JSON数据以加快访问速度，特别是10版本JSONB更是强大。
• PostgreSQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱，也没有人管你，这一点很重要，这表明了PostgreSQL数据库不会被其它公司控制。相反，MySQL现在主要是被Oracle公司控制。

总结：

1. 总体上来说，开源数据库都不是很完善，商业数据库oracle在架构和功能方面都还是完善很多的。从应用场景来说，PG更加适合严格的企业应用场景（比如金融、电信、ERP、CRM），但不仅仅限制于此，PostgreSQL的json，jsonb，hstore等数据格式，特别适用于一些大数据格式的分析；而MySQL更加适合业务逻辑相对简单、数据可靠性要求较低的互联网场景（比如google、facebook、alibaba），当然现在MySQL的在innodb引擎的大力发展，功能表现良好。
2. MySQL 和 PostgreSQL 复杂的开源关系型数据库，本文只是作者根据自己经验写的对PG和MySQL的理解，难免有不当之处，不当之处还请大家多多指正。
3. MySQL在国内的发展已然很成熟，但是如果你转向PostgreSQL，会发现不一样的天地，学院派的风格，丰富的功能，肯定会给你带来不一样的惊喜。

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

2.1 二进制包安装

例：Rocky8利用官方源安装PostgreSQL-16

[root@localhost ~]# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
 

#禁用内置的postgresql

[root@localhost ~]# dnf -qy module disable postgresql

[root@localhost ~]# dnf install -y postgresql12-server

#数据库初始化

[root@localhost ~]# /usr/pgsql-12/bin/postgresql-12-setup initdb

#启动服务

[root@localhost ~]# systemctl enable postgresql-12

[root@localhost ~]# systemctl start postgresql-12
 

#验证成功

[root@localhost ~]# su - postgres
[postgres@localhost ~]$ psql
psql (12.18)
Type "help" for help.

postgres=# 
 

2.2 源码编译安装

下载源代码

https://www.postgresql.org/ftp/source/

编译安装：

#安装依赖包

[root@localhost ~]# yum  install -y gcc make readline-devel zlib-devel

#下载解压缩

[root@localhost ~]# wget https://ftp.postgresql.org/pub/source/v12.9/postgresql-12.9.tar.gz

[root@localhost ~]# cd postgresql-12.9/

#编译安装

[root@localhost ~]# ./configure --prefix=/apps/pgsql --with-pgport=5432

[root@localhost postgresql-12.9]# make -j 2 world

[root@localhost postgresql-12.9]# make install-world

#创建数据库用户和组

[root@localhost postgresql-12.9]# useradd -s /bin/bash -m -d /home/postgres postgres
[root@localhost postgresql-12.9]# echo postgres:123456|chpasswd
 

#创建数据目录并授权

[root@localhost postgresql-12.9]# mkdir -pv /pgsql/data/

[root@localhost postgresql-12.9]# chown postgres.postgres /pgsql/data/

#设置环境变量

[postgres@localhost ~]$ vim/etc/profile.d/pgsql.sh 
export PGHOME=/apps/pgsql
export PATH=$PGHOME/bin/:$PATH
export PGDATA=/pgsql/data
export PGUSER=postgres
export MANPATH=/apps/pgsql/share/man:$MANPATH

#初始化数据库

[postgres@localhost ~]$ initdb -A md5 -D $PGDATA -E utf8 --locale=C -U postgres -W

#启动服务

[postgres@localhost ~]$ pg_ctl -D /pgsql/data -l logfile start

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

pg_ctl命令选项：

初始化数据库： pg_ctl  initdb -D $PGDATA  （-D 指定数据库实例的数据目录，使用环境变量PGDATA指定的路径 ）

查看服务状态：pg_ctl status -D

启动服务：pg_ctlstart  -D $PGDATA 

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

重启服务 ：pg_ctl restart

加载配置 ：pg_ctl reload

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

psql常用命令选项：

\h      查看sql语法
\?      命令行操作的帮助
Tab键可以补全目录
\i sql文件     执行sql文件
\l      列出所有的数据库
\dn     列出所有schema
\db     显示所有表空间（pg中的表空间是一个目录，表放在表空间相当于将表的数据文件放到该目录之下）
\d     查看当前schema 中所有的表、视图、序列
\d+   同上，但是多额外信息，下面几个命令都有带+版，能看到更详细的信息
\d schema.obj   查看对象结构（相当于desc）
\dt     只显示匹配的表
\di     只显示匹配的索引
\ds     只显示序列
\dv     只显示视图
\df      只显示函数
\sf 函数名   查看函数定义
\du 或 \dg   列出数据库中所有角色或用户（pg中是一样的）
\dp 或 \z 表名   显示表的权限分配情况
\x      横纵显示切换 （类似mysql \G）
\dT+   显示扩展类型相关属性及描述
\q      退出pg命令行
\z 或 \dp 表名    显示表的权限分配情况
\timing    显示执行时间
\watch 5    每五秒循环执行sql语句
\c dbname      切换数据库
\conninfo   查看连接信息
\echo 字符串  输出一行信息
\encoding 字符集名      设置客户端字符集
\set [NAME [VALUE]]    设置内部变量，不加参数则列出内部变量

初始化实例：

#先切换用户

su - postgres

#初始化数据库

initab [DATADIR]

pg_ctl init[db] [-s] [-D DATADIR] [-o options]

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

在一个PostgreSQL数据库系统中，数据的组织结构可以分为以下五层：

• 实例：一个PostgreSQL对应一个安装的数据目录$PGDATA,即一个instance实例。
• 数据库：一个PostgreSQL数据库服务下可以管理多个数据库，当应用连接到一个数据库时，一般只能访问这个数据库中的数据，而不能访问其他数据库中的内容。默认情况下初始实例只有三个数据库：postgres、template0、template1
• 模式：一个数据库可以创建多个不同的名称空间即Schema，用于分隔不同的业务数据
• 表和索引：一个数据库可以有多个表和索引。在PostgreSQL中表的术语称为Relation，而在其他数据库中通常叫Table
• 行和列：每张表中有很多列和行数据。在PostgreSQL中行的术语一般为：“Tuple”，而在其他数据库中则叫“Row”

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

#修改配置文件

[postgres@localhost ~]$ vim /pgsql/data/postgresql.conf 

listen_addresses = '*'

[postgres@localhost ~]$ vim /pgsql/data/pg_hba.conf 

host    all             all             0.0.0.0/32              md5

#重启服务

[postgres@localhost ~]$ pg_ctl restart

#用另一台主机远程连接

[postgres@rocky8 ~]$ psql -h 10.0.0.28 -d postgres -p 5432 -U postgres
Password for user postgres: 
psql (12.9)
Type "help" for help.

postgres=# 

#无密码登录
[postgres@localhost ~]$ cat .pgpass
10.0.0.28:5432:postgres:postgres:77057170

[postgres@rocky8 ~]$ chmod 600 .pgpass
[postgres@rocky8 ~]$ ll .pgpass
-rw------- 1 postgres postgres 42 Apr 26 17:12 .pgpass
[postgres@rocky8 ~]$ psql -h 10.0.0.28 -U postgres
psql (12.9)
Type "help" for help.

postgres=# 
 

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

表的CRUD：

INSERT UPDATA DELETE SELECT

注意：PostgreSQL用INSERT时,一定加上INTO。

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

在PostgreSQL中，用户与角色是没有区别的。

用户和角色可以用来实现以下功能：

• 用来登录数据库实例
• 管理数据库对象

创建用户与角色的语法如下：

CREATE USER name [[WITH] option [ ...]]

CREATE ROLE name [[WITH] option [ ...]]

#区别是CREATE USER创建的用户默认可以登录，而CREATE ROLE 创建的角色没有LOGIN权限，不可以登录，需要授权。

范例：

#创建可以登录的用户和密码

CREATE USER huang WITH PASSWORD '123456'；

#创建不可登录的用户

CREATE ROLE test WITH PASSWORD '123456'；

#创建管理员

CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD '123456'；

#创建复制用户

CREATE USER repl REPLICATION LOGIN ENCRYPTED PASSWORD '123456'；

#修改密码

ALTER USER admin with password '654321'；

#修改权限和密码

ALTER USER test with login;

#删除用户

DROP USER test;

#查看用户信息

\du 

#查看指定用户信息

\du huang

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

#创建数据库

postgres=# create database zabbix;

# 创建模式

postgres=# \c zabbix

# 创建用户
zabbix=# create user mage with password '123456';

# 授权

zabbix=# alter schema magedu owner to mage;
zabbix=# alter database zabbix owner to mage;

 

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

1、postmaster 主进程

• 它是整个数据库实例的主控制进程，负责启动和关闭改数据库实例。
• 它是第一个PGsql的进程，此主进程还会fork出其他子进程，并管理它们
• 当用户和PGsql建立连接时，首先是和Postmaster进程建立连接，首先客户端会发出身份验证的信息给Postmaster进程，Postmaster进程根据消息中的信息进行身份验证判断，如果验证通过，它会fork出一个会话子进程为这个连接服务。
• 当有服务进程出现错误时，Postmaster主进程会自动完成系统的恢复。恢复过程中会停掉所有的进程，然后进行数据库的一致性恢复，待恢复完成后，数据库又可以接受新的连接。
• 验证功能是通过配置文件pg_hba.conf和用户验证模块来提供。

2、bgwriter 后台写进程

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

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

3、walwriter 预写式日志

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

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

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

4、checkpointer 检查点操作

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

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

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

5、autovacuum 自动清理进程

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

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

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

6、pgarch 归档进程  #相当于mysql的二进制文件

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

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

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

7、pgstat 统计数据收集进程

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

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

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

8、syslogger 系统日志进程

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

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

9、startup 启动进程

• 用户数据库恢复的进程。

10、session 会话进程

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

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

数据更新过程

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

进程间的协同工作取决于消息传递和通信机制。主进程通过进程间通信（Inter-Process Communication，IPC）机制与后台进程进行通信，如共享内存、信号、套接字等。前端进程通过客户端-服务器模型与主进程通信，通过连接套接字进行交互。

主进程负责将客户端请求分派给合适的后台进程来执行。后台进程执行任务后将结果返回给主进程，主进程再将结果发送给对应的前端进程，最终将结果返回给客户端。

通过这种进程间协同工作的方式，PostgreSQL实现了客户端与后台进程之间的并发处理、任务分配和结果返回，以实现高性能、可靠的数据库服务。

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

pgsql的数据目录结构：

postgresql.conf            数据库的主配置文件

pg_hba.conf            认证配置文件，配置了允许哪些IP的主机访问数据库，认证的方法是什么等信息。

pg_ident.conf            认证方式ident的用户映射文件

base/            默认表空间的目录，每个数据库都对应一个base目录下的子目录，每个表和索引对应一个独立文件

global/            对应pg_global表空间，存放实例中的共享对象        

pg_bba.conf           数据库访问控制文件

pg_log                  数据库系统日志目录（路径根据配置定义）

pg_xact/    存储事务提交状态数据

pg_multixact/    共享行锁的事务状态数据

pg_notify/    Listen/Notify状态数据

pg_snapshots/    快照信息

pg_subtrans/    子事务状态数据

pg_stat/    统计信息的存储目录

pg_stat_tmp/    统计信息的临时存储目录

pg_tblspc/    存储了指向各个用户自建表空间实际目录的链接文件

pg_twophase/    使用两阶段事务提交时分布式事务的存储目录

pg_wal/    WAL日志的目录

PG_VERSION    pg服务器主版本号文件

postmaster.opts    记录服务器上一次启动的命令行选项
postmaster.pid    数据库启动的主进程信息文件，包括pid,SPGDATA目录，数据库启动时间，监听端口，socket文件路径，临听地址，共享内存的地址信息，主进程状态。
pg_commit_ts/    事物提交的时间戳数据
pg_dynshmem/    动态共享内存子系统中使用的文件

pg_logical/    逻辑解码的状态数据
pg_replslot/    复制槽数据
pg_serial/    已提交的可串行化事务相关信息
postgresql.auto.conf    存储使用alter system修改的配置

范例：

 [postgres@localhost ~]$ cat /pgsql/data/postmaster.pid
14638
/pgsql/data
1714116917
5432
/tmp
localhost
  5432001         6
ready   

范例：

[postgres@localhost ~]$ ll /pgsql/data/pg_wal/
total 16384
-rw------- 1 postgres postgres 16777216 Apr 26 16:46 000000010000000000000001
drwx------ 2 postgres postgres        6 Apr 26 15:34 archive_status