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

1.1 PostgreSQL

• 优点:

强大的对象关系映射功能
丰富的数据类型
强大的事务处理能力
丰富的统计函数和操作符
支持复杂的SQL查询
支持用户定义的数据类型和函数
支持NoSQL特性，如JSONB数据类型
支持地理空间数据处理
支持多版本并发控制 (MVCC)
支持非阻塞查询
支持函数索引
支持表分区
支持可扩展性，通过Postgres-XL或者Pgpool-II等第三方集群解决方案

• 缺点:

学习曲线较为陡峭
对于简单的操作，如插入或选择，可能表现不如MySQL
在大数据集上进行复杂的操作时，性能可能不如MySQL

1.2 MySQL

• 优点:

简单易学
速度快，尤其是在读操作为主的场景下
提供了大量的默认值和内置函数，使得开发者不需要自行编写复杂的SQL查询
支持MySQL Cluster，能够提供高可用性和强一致性保证
支持InnoDB存储引擎，提供了行级锁定和外键约束
支持MySQL Workbench，一个可视化的数据库设计、管理和优化工具
有MySQL Community Server、MySQL Enterprise Edition等不同版本供选择

• 缺点:

功能不如PostgreSQL丰富
不支持NoSQL特性
不支持地理空间数据处理
不支持MVCC
不支持表分区
不支持函数索引
可扩展性不如PostgreSQL

1.3 总结:

       选择哪种数据库取决于你的具体需求。如果你需要强大的对象关系映射和复杂查询处理，PostgreSQL可能更适合。如果你需要快速的读操作和简单的数据管理，MySQL可能是更好的选择。

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

#二进制安装
apt install curl ca-certificates
install -d /usr/share/postgresql-common/pgdg
curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
apt update && apt -y install postgresql

#范例：ubuntu上源码编译安装postgresql
#安装必要包
root@ubuntu:~# apt update && apt install -y gcc make libreadline-dev zlib1g-dev
#解压缩
root@ubuntu:~# tar xf postgresql-14.2.tar.gz 
root@ubuntu:~# cd postgresql-14.2/
#编译安装
root@ubuntu:~/postgresql-14.2# ./configure --prefix=/apps/pgsql
#加world有man帮助
root@ubuntu:~/postgresql-14.2# make -j 4 world
root@ubuntu:~/postgresql-14.2# make install-world
查看路径
root@ubuntu:~/postgresql-14.2# tree /apps/pgsql/
创建数据库用户和组
root@ubuntu:~/postgresql-14.2# useradd -s /bin/bash -m -d /home/postgres postgres
修改postgres密码
root@ubuntu:~/postgresql-14.2# echo -e '123456\n123456' | passwd postgres
#或者echo postgres:123456 | chpasswd
#创建数据目录并授权
root@ubuntu:~# mkdir -pv /pgsql/data
root@ubuntu:~# chown postgres.postgres /pgsql/data/
#设置环境变量
root@ubuntu:~# vim /etc/profile.d/pgsql.sh
#!/bin/bash
export PGHOME=/apps/pgsql
export PATH=$PGHOME/bin/:$PATH
export PGDATA=/pgsql/data
export PGUSER=postgres
export MANPATH=/apps/pgsql/share/man:$MANPATH
root@ubuntu:/usr/local/src/postgresql-14.2# su - postgres
#初始化并按照预先定义变量存放路径
postgres@ubuntu:~$ initdb
postgres@ubuntu:~$ ls /pgsql/data
#启动postgresql数据库服务
postgres@ubuntu:~$ pg_ctl -l logfile start
#查看端口号5432确认服务开启
postgres@ubuntu:~$ ss -ntl
postgres@ubuntu:~$ psql
设置开机自启动
第一种方法：
root@ubuntu:~# vim /etc/rc.local
root@ubuntu:~#cd /usr/local/src/postgresql-14.2/
root@ubuntu:/usr/local/src/postgresql-14.2# cp contrib/start-scripts/linux /etc/init.d/postgresql
root@ubuntu:/usr/local/src/postgresql-14.2# vim /etc/init.d/postgresql
prefix=/apps/pgsql
PGDATA="/pgsql/data"
root@ubuntu:~# chmod +x /etc/init.d/postgresql 
root@ubuntu:~# /etc/init.d/postgresql start
Starting PostgreSQL: ok
root@ubuntu:~# chmod +x /etc/rc.local

第二种方法：
root@ubuntu:~# vim /etc/rc.local
su - postgres -c "pg_ctl -l logfile start"

下面命令也可以启动服务
root@ubuntu:~# service postgresql start

第三种方法：
创建service文件
systemctl cat postgresql.service
#/lib/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
User=postgres
Group=postgres

ExecStart=/apps/pgsql/bin/postmaster -D /pgsql/data
ExecReload=/bin/kill -HUP

[Install]
WantedBy=multi-user.target

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

• pg_ctl命令选项及示例

#初始化
pg_ctl init[db]   [-D DATADIR] [-s] [-o OPTIONS]

#启动服务
pg_ctl start      [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s]
                  [-o OPTIONS] [-p PATH] [-c]

#停止服务
pg_ctl stop       [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]

#重启服务
pg_ctl restart    [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-c]

#重新加载
pg_ctl reload     [-D DATADIR] [-s]

#查看状态
pg_ctl status     [-D DATADIR]

#从节点提升为主节点
pg_ctl promote    [-D DATADIR] [-W] [-t SECS] [-s]

• pgsql命令选项及示例

psql [-h host] [-p 端口] 数据库名 -U [用户名]

范例：
#创建管理员账号
hellodb=# CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD '123456';
[postgres@rocky8 ~]$ psql -h 10.0.0.200 -U admin -d db1 -p 5432

• 不同系统的初始化操作

#初始化方法一
postgres@ubuntu:~$ pg_ctl init -D /pgsql/data2
#初始化方法二
postgres@ubuntu:~$ initdb

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

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

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

数据库：一个postgresql数据库服务下可以管理多个数据库，当应用连接到一个数据库时，一般只能访问这    个数据库中的数据，而不能访问其他数据库中的内容，默认情况下初始实例只有三个数据库：postgres、    template0、template1

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

表和索引：一个数据库可以有多个表和索引。在postgresql中表的术语称为relation,而在其他数据库中通常叫Table

行和列：每张表中有很多列和行的数据。在postgresql中行的术语一般为Tuple，而在其他数据库中则叫Row

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

远程连接配置：
- [1] 更改远程连接地址（可以是*或者0.0.0.0或者网段）
postgres@ubuntu:~$ echo $PGDATA
/pgsql/data

postgres@ubuntu:~$ vim $PGDATA/postgresql.conf

listen_addresses = '0.0.0.0' 

- [2]远程连接账号密码相关设置
postgres@ubuntu:~$ vim $PGDATA/pg_hba.conf
#添加此行
host    all             all             0.0.0.0/0               md5

postgres@ubuntu:~$ pg_ctl restart

#修改远程账号的密码
postgres=# ALTER USER postgres with password '123456';

#测试远程登录（输入密码）
[postgres@rocky8 ~]$ psql -d postgres -h 10.0.0.200 -p 5432 -U postgres
Password for user postgres: 

#测试远程登录（无密码）
若写脚本，非交互式：
[postgres@rocky8 ~]$ cat > .pgpass <<EOF
> 10.0.0.200:5432:testdb:postgres:123456
> EOF
chmod 600 .pgpass 
#再连接不需要输入密码了
[postgres@rocky8 ~]$ psql -h10.0.0.200 db1 postgres

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

• 数据库添加
  [root@rocky8 ~]# createdb -h 10.0.0.200 -U postgres db2 创建数据库
• 数据库删除
  postgres=# drop database db2;
• 模式添加
  postgres=# create schema test;
• 模式删除
  postgres=# drop schema test;
• 表的添加
  hellodb=# CREATE TABLE t1(id int);
• 表的删除
  hellodb=# drop table t1;
• 表数据的创建
  hellodb=# insert into teachers values(6,'Hu Xia',23,'F');
• 表数据的更新
  hellodb=# update teachers set name='Xiao Man' where tid=5;
• 表数据的读取
  hellodb=# select * from teachers;
• 表数据的删除
  hellodb=# delete from teachers where tid=5;
• 相关信息查看语句

#查询数据库明细
hellodb=# \l+
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   |  Size   | Tablespace |                Descrip
tion                 
-----------+----------+----------+---------+---------+-----------------------+---------+------------+-----------------------
---------------------
 hellodb   | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres         +| 8441 kB | pg_default | 
           |          |          |         |         | postgres=CTc/postgres |         |            | 
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |                       | 8233 kB | pg_default | default administrative
 connection database
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +| 8081 kB | pg_default | unmodifiable empty dat
abase
           |          |          |         |         | postgres=CTc/postgres |         |            | 
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +| 8081 kB | pg_default | default template for n
ew databases
           |          |          |         |         | postgres=CTc/postgres |         |            | 
(4 rows)

#查看用户
hellodb=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repluser  | Replication                                                | {}

#查看当前数据库所有表
hellodb=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | classes  | table | postgres
 public | coc      | table | postgres
 public | courses  | table | postgres
 public | scores   | table | postgres
 public | students | table | postgres
 public | teachers | table | postgres
 public | toc      | table | postgres
(7 rows)

#查看版本信息
hellodb=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit
(1 row)

#查看系统启动时间
testdb=# select pg_postmaster_start_time();
   pg_postmaster_start_time    
-------------------------------
 2024-08-07 05:52:30.144636+08
(1 row)

Time: 0.800 ms

#加载服务的时间
testdb=# select pg_conf_load_time();
      pg_conf_load_time       
------------------------------
 2024-08-07 05:52:30.03899+08
(1 row)

#查看时区
testdb=# show timezone;
   TimeZone    
---------------
 Asia/Shanghai

#更改时区（进配置文件搜索timezone）
postgres@ubuntu:~$ vim /pgsql/data/postgresql.conf 
timezone = 'Asia/Shanghai'
log_timezone = 'Asia/Shanghai'
postgres@ubuntu:~$ pg_ctl reload

#查看当前时间
testdb=# select now();
              now              
-------------------------------
 2024-08-07 07:28:30.964245+08

#查看当前用户
testdb=# select user;
   user   
----------
 postgres

#远程连接，查询本机IP和端口
postgres=# select inet_client_addr(),inet_client_port();
 inet_client_addr | inet_client_port 
------------------+------------------
 10.0.0.8         |            43870

#远程连接，查询服务器IP和端口
postgres=# select inet_server_addr(),inet_server_port();
 inet_server_addr | inet_server_port 
------------------+------------------
 10.0.0.200       |             5432

#查看进程id
postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
          38313

#查看系统内置变量
postgres=# \set

#查看最大连接数
postgres=# show max_connections;
 max_connections 
-----------------
 100

改最大连接数需要改配置文件重启
postgres@ubuntu:~$ vim /pgsql/data/postgresql.conf 
max_connections = 1000  
postgres@ubuntu:~$ pg_ctl restart
db1=# show max_connections;
 max_connections 
-----------------
 1000

#查看系统函数
db1=# \dfS

#查看archive默认开关
db1=# show archive_mode;
 archive_mode 
--------------
 off

#查看所有默认配置
db1=# show all;

#查看默认执行维护任务时的内存
db1=# show maintenance_work_mem ;
 maintenance_work_mem 
----------------------
 64MB

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

#列出所有账号
hellodb=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

#权限
hellodb=# \z

#创建账号
hellodb=# create user wang with password '123';   可以登录
hellodb=# create role user2 with password '123';   不能登录，想要登录，加with login

#创建管理员账号
hellodb=# CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD '123456';

#修改密码
hellodb=# ALTER USER admin with password '654321';

#修改权限
hellodb=# alter user user2 with login;

#删除账号
drop user user2

#更改创建数据库权限
postgres=# alter user wang with CREATEDB;

#database权限设置
GRANT create ON DATEBASE testdb TO wang;

#schema权限
ALTER SCHEMA wang OWNER to wang;
GRANT select,insert,update,delete ON ALL TABLES IN SCHEMA wang TO wang;

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

#object权限
GRANT select,insert,update,delete ON testdb.t1 TO wang;

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

范例：创建业务用户和授权
postgres=# create database pinxixi;
postgres=# \c pinxixi
pinxixi=# create user wanrentuan with password '123456';

#方法一
pinxixi=# create schema wanrentuan;
pinxixi=# ALTER SCHEMA wanrentuan OWNER to wanrentuan;

#方法二
pinxixi=# CREATE SCHEMA AUTHORIZATION wanrentuan;

#方法三
pinxixi=# GRANT select,insert,update,delete ON ALL TABLES IN SCHEMA wanrentuan to wanrentuan;

范例：
#创建一个名为“readonly”的用户
CREATE USER readonly with password '123456';

#把在public的schema下现有的所有表的SELECT 权限给用户readonly
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

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

#创建mage账号
[root@rocky8 ~]# su - postgres
[postgres@rocky8 ~]$ psql
postgres=# create user mage with password '123456';

#magedu模式
postgres=# create schema magedu

#准备zabbix库
postgres=# create database zabbix;

#配置mage用户的默认模式magedu
zabbix=# create schema magedu authorization mage;

#mage用户给zabbix库所有权限
zabbix=# alter database zabbix owner to mage;

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

image.png

• Postmaster主进程
  它是整个数据库实例的主控制进程，负责启动和关闭该数据库实例
• BgWriter 后台写进程
  WalWriter 预写式日志进程 写日志再写入数据优点：既安全又快 存放路径：wal
• Checkpointer 检查点进程
  所有脏数据写入磁盘
• PgArch 归档进程
  默认没有此进程，生产中必须开启此功能，备份用
• AutoVacuum 自动清理进程
  执行delete操作时，旧的数据并不会立即被删除，在更新数据时，也不会在旧的数据上做更新，而是新生成一行数据。旧的数据只是被标识为删除状态，在没有并发的其他事务读到这些日数据时，它们才会被清除掉 。
autovacuum lanucher 负责回收垃圾数据的master进程如果开启了autovacuum的话，那么.postmaster会fork这个讲程 。autovacuum worker 负责回收垃圾数据的worker进程,是lanucher进程fork出来的
• PgStat 统计数据收集进程
  此进程主要做数据的统计收集工作 。收集的信息主要用于查询优化时的代价估算。统计的数据包括对一个表或索引进行的插入、删.除、更新操作，磁盘块读写的次数以及行的读次数等。系统表pg statistic中存储了PgStat收集的各类统计信息
• SysLogger 系统日志进程
  默认没有此进程,配置文件 postgresgl.conf 设置参数logging collect设置为"on"时， 主进程才会启动SysLogger辅助进程。它从Postmaster主进程、所有的服务进程以及其他辅助进程收集所有的stderr输出，并将这0些输出写入到日志文件中
• startup 启动进程
  用于数据集恢复的进程
• Session 会话进程
  每一个用户发起连接后，一旦验证成功,postmaster进程就会fork一个新的子进程负责连接此用户。
通常表现为进程形式: postgres postgres [local] idle0

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

#pg_ident.conf用户映射配置文件
postgres@ubuntu:~$ ls /pgsql/data/pg_ident.conf 

#pg_hba.conf账号密码设置
postgres@ubuntu:~$ ls /pgsql/data/pg_hba.conf 

#数据文件
每个索引和表都是一个单独的文件，称为Segment，默认大于1G的Segment会被分割pg_class.efilenode.1这样的文件。大小可以在initdb时通过选项--with-segsize=SEGSIZE指定。

#控制文件
postgres@ubuntu:~$ ls /pgsql/data/global/pg_control 

#查看控制文件
postgres@ubuntu:~$ pg_controldata 
postgres@ubuntu:~$  ll /pgsql/data/pg_wal