PostgreSQL

总结pg和mysql的优劣势。

以下是PostgreSQL（PG）和MySQL的优劣势对比：

	PostgreSQL（PG）	MySQL
优势
可靠性	强大的稳定性和可靠性，经过长时间测试和验证	高性能和稳定性，适用于多种应用场景
扩展性	支持水平扩展和垂直扩展，适用于大规模数据和高并发场景	支持主从复制和分布式部署，具有良好的可伸缩性
功能丰富性	提供广泛的功能和高级特性，如复杂数据类型、存储过程、触发器等	提供基本的数据库功能和特性，适用于大多数常见应用
SQL标准支持	遵循SQL标准，兼容性强	大部分兼容SQL标准
安全性	强大的安全特性，包括访问控制、数据加密等	提供基本的安全特性，如访问控制和权限管理
社区支持	活跃的开源社区，丰富的资源和支持	庞大的社区和生态系统，大量第三方工具和扩展
成本效益	开源软件，免费使用	开源软件，免费使用
劣势
性能	在高并发场景下可能较MySQL略逊一筹	优秀的性能，特别在高并发读写场景下
社区规模	相较于MySQL，社区规模较小	拥有庞大的社区和开发者基础
并发控制和事务处理	事务处理能力相对较弱	较好的并发控制和事务处理能力
数据类型和特性	丰富的数据类型和特性，但可能增加复杂性	较为简单和直观的数据类型和特性
大数据处理	对于大数据量处理可能需要特殊优化	不太适合处理大规模数据
存储引擎选择	存储引擎选择相对较少	提供多种存储引擎选择，满足不同需求

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

rocky8安装PostgreSQL

[root@rocky8 ~]#dnf install -y
https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-
repo-latest.noarch.rpm
#禁用内置的postgresql
[root@rocky8 ~]#dnf -qy module disable postgresql
[root@rocky8 ~]#dnf install -y postgresql12-server
#初始化数据库
[root@rocky8 ~]#/usr/pgsql-12/bin/postgresql-12-setup initdb
#启动服务
[root@rocky8 ~]#systemctl enable --now postgresql-12
#验证成功
[root@rocky8 ~]#sudo -u postgres psql -c "SELECT version();"
could not change directory to "/root": Permission denied

PostgreSQL 10.17 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.4.1
20200928 (Red Hat 8.4.1-1), 64-bit
(1 row)
[root@rocky8 ~]#su - postgres
Last login: Tue Dec 21 17:44:59 CST 2021 on pts/0
[postgres@rocky8 ~]$psql
psql (12.9)
Type "help" for help.
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

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

pg_ctl 命令

命令选项	描述	示例
init	初始化一个新的数据库实例	pg_ctl init -D /data/pgsql/data
start	启动数据库服务	pg_ctl start -D /data/pgsql/data
stop	停止数据库服务	pg_ctl stop -D /data/pgsql/data -m fast
restart	重启数据库服务	pg_ctl restart -D /data/pgsql/data
status	显示数据库服务的状态	pg_ctl status -D /data/pgsql/data
reload	重新加载配置文件	pg_ctl reload -D /data/pgsql/data

psql 命令选项

命令选项	描述	示例
-U	指定连接数据库的用户名	psql -U postgres -d mydb
-d	指定要连接的数据库名	psql -U postgres -d mydb
-h	指定数据库服务器的主机名或IP地址	psql -U postgres -h localhost -d mydb
-p	指定数据库服务器的端口号	psql -U postgres -h localhost -p 5432 -d mydb
-c	执行一条SQL命令并退出	psql -U postgres -d mydb -c "SELECT * FROM users;"
\l	列出所有数据库	psql -U postgres -c "\l"
\c	切换到其他数据库	psql -U postgres -d mydb -c "\c another_db"

总结pg数据库结构组织

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

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

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

范例：实现远程连接

#修改用户postgres密码
[root@rocky8 ~]#psql
postgres=# ALTER USER postgres with password '123456';
#查看监听地址和端口,默认为127.0.0.1:5432

[root@rocky8 ~]#vim /pgsql/data/postgresql.conf
listen_addresses = '0.0.0.0' #修改此行中的localhost为 0.0.0.0
[root@rocky8 ~]#vim /pgsql/data/pg_hba.conf
host 	all 	all 	::1/128 	trust
#上面行的后面加一行
host 	all 	all 	0.0.0.0/0 	md5
#重启服务生效
[postgres@rocky8 ~]$pg_ctl restart -mf
#查看监听地址和端口
[root@rocky8 ~]#ss -ntl
#测试远程登录
[root@rocky8 ~]#psql -d postgres -h 10.0.0.152（pgsql的主机IP） -p 5432 -U postgres

范例： 利用.pgpass文件实现免密码连接远程posgresql

[root@rocky8 ~]#vim .pgpass
#hostname:port:database:username:password
10.0.0.200:5432:testdb:postgres:123456
[root@rocky8 ~]#chmod 600 .pgpass
[root@rocky8 ~]#ll .pgpass
-rw------- 1 root root 81 Dec 30 10:04 .pgpass
#psql默认连接本机，需要指定和.pgpass文件内容相匹配信息才可以使用.pgpass文件连接
[root@rocky8 ~]#psql -U postgres -h 10.0.0.152 -d hellodb -w
psql (12.9)
Type "help" for help.
hellodb=# \c
You are now connected to database "hellodb" as user "postgres".
hellodb=#

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

库（数据库）的添加和删除操作

操作	SQL 语句
创建库	CREATE DATABASE database_name;
删除库	DROP DATABASE database_name;

模式（在PostgreSQL中通常等同于数据库）

在PostgreSQL中，通常不需要显式创建模式，因为当你创建一个新的数据库时，它会自动创建一个名为public的模式。但如果你需要创建额外的模式，可以使用以下命令：

操作	SQL 语句
创建模式	CREATE SCHEMA schema_name;
删除模式（及其所有对象）	DROP SCHEMA schema_name CASCADE;

表的添加和删除操作

操作	SQL 语句
创建表	CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
删除表	DROP TABLE table_name;

表数据的CRUD操作

操作	SQL 语句
创建（Create）	INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
读取（Read）	SELECT * FROM table_name; 或 SELECT column1, column2 FROM table_name WHERE condition;
更新（Update）	UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
删除（Delete）	DELETE FROM table_name WHERE condition;

相关信息查看语句

信息	SQL 语句
查看所有数据库	\l 或 SELECT datname FROM pg_database;
查看数据库中的表	\dt 或 SELECT tablename FROM pg_tables WHERE schemaname = 'public'; (对于public模式)
查看表结构	\d table_name (psql命令行工具) 或 SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'table_name';
查看表的行数据	SELECT * FROM table_name;

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

在PostgreSQL中，用户与角色是没有区别的。
用户和角色可以用来实现以下功能:

• 用来登录数据库实例

• 管理数据库对象

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

CREATE USER name [[WITH] option [ ...]]
CREATE ROLE name [[WITH] option [ ...]]
#CREATE USER 默认创建出来的用户有"LOGIN"的权限，而CREATE ROLE 创建出来的用户没有"LOGIN"的权限

#上面语法中的“option”可以是如下内容。
SUPERUSER | NOSUPERUSER:表示创建出来的用户是否为超级用户。只有超级用户才能创建超级用户。
CREATEDB /NOCREATEDB:指定创建出来的用户是否有执行"CREATE DATABASE"的权限。
CREATEROLE NOCREATEROLE:指定创建出来的用户是否有创建其他角色的权限。
CREATEUSER NOCREATEUSER:指定创建出来的用户是否有创建其他用户的权限。
INHERIT |NOINHERIT:如果创建的一个用户拥有某一个或某几个角色，这时若指定INHERIT，则表示用户自动拥有相应角色的权限，否则这个用户没有该角色的权限。
LOGIN | NOLOGIN:指定创建出来的用户是否有“LOGIN”的权限，可以临时地禁止一个用户的“LOGIN”权限，这时此用户就不能连接到数据库

用户管理:

#创建可以登录的用户和密码
CREATE USER liu WITH PASSWORD '123456';
#创建不可登录用户
create role zhang WITH PASSWORD '123456';
#创建复制用户
CREATE USER repl REPLICATION LOGIN ENCRYPTED PASSWORD '123456';
#创建管理员
CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD '123456' ;
#修改密码
ALTER USER admin with password '35689';
#修改权限和密码
alter user zhang with login ;
alter user liu with nologin password '2580';

用户的权限分为两类，一类是在创建用户时就指定的权限，这些权限如下:

• 超级用户的权限

• 创建数据库的权限

• 是否允许LOGIN的权限

这些权限是创建用户时指定的，后续可使用ALTER ROLE命令来修改。
还有一类权限，是由命令GRANT和 REVOKE来管理的，这些权限如下:

• 在数据库中创建模式(SCHEMA)

• 允许在指定的数据库中创建临时表连接某个数据库

• 在模式中创建数据库对象，如创建表、视图函数等

• 在一些表中做SELECT、UPDATE、INSERRDELETE等操作等

权限管理：

#授权创建新数据库
#alter user username with CREATEDB;
#database权限设置
GRANT create ON DATABASE db_name TO username;
#table权限设置
GRANT SELECT, INSERT ON table_name TO username;
#schema权限
ALTER SCHEMA username OWNER to username;
GRANT select,insert,update,delete ON ALL TABLES IN SCHEMA username TO username;

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

创建模式（Schema）

CREATE SCHEMA magedu;

创建用户

然后，创建mage的用户。

CREATE USER mage WITH PASSWORD 'mage';

设置用户的默认模式

使用ALTER USER命令来设置用户的默认模式。

ALTER USER mage SET search_path TO magedu;

创建数据库（如果尚未创建）

创建zabbix数据库

CREATE DATABASE zabbix;

授权用户对数据库的所有权限

使用GRANT命令来赋予mage用户对zabbix数据库的所有权限。

GRANT ALL PRIVILEGES ON DATABASE zabbix TO mage;

#完成以上操作需要有创建模式、用户、数据库权限的用户账号。

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

Postmaster 主进程

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

• 实际上,使用pg ctl来启动数据库时，pg_ctl也是通过运行postgres来启动数据库的，只是它做了一些包装,更容易启动数据库。它是第一个PostgreSQL进程，此主进程还会fork出其他子进程，并管理它们。

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

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

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) 在特定时间自动执行一个检查点,通过向数据库写入进程 (BgWriter) 传递消息来启动检查点请求。

PgArch 归档进程

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

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

• PostgreSQL 从8.X版本开始提供了PITR (
  Point-In-Time-Recovery)技术，即就是在对数据厍进行过一次全量备份后，该技术将备份时 间点后面的WAL日志通过归档进行备份，将来可以使用数据库的全量备份再加上后面产生的WAL日志，即可把数据库向前恢复到全量备份后的任意一个时间点的状态。

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

数据库数据存放在环境变量PGDATA指向数据目录。这个目录是在安装时指定的，所以在安装时需要指定一个合适的目录作为数据目录的根目录，而且，每一个数据库实例都要有一个对应的目录。目录的初始化是使用initdb来完成的。

初始化完成后，PGDATA数据目录下就会生成三个配置文件:

• postgresql.conf：数据库实例的主配置文件。它包含了数据库实例的基本配置参数，如端口号、内存使用、日志设置等。
• pg_hba.conf：认证配置文件。它决定了哪些主机可以连接到数据库，以及使用什么认证方法进行连接。例如，它可以配置为允许本地连接使用trust认证，而远程连接使用md5或password认证。
• pg_ident.conf：“ident”认证方式的用户映射文件。这个文件用于在“ident”认证方式下，将操作系统用户映射到数据库用户。

在PGDATA目录下还会生成如下一些子目录:

• base：存储了用户创建的数据库文件及隶属于这些数据库的所有关系，如表、索引等。每个数据库在base目录下都有一个子目录，子目录的名字是数据库OID（对象ID）。
• global：包含了一些共享的系统表。
• pg_clog：commit log的目录。它存储了事务的提交状态信息，用于并发控制和恢复。
• pg_log：系统日志目录。它包含了查询、错误和其他系统活动的日志文件，对于诊断问题很有用。
• pg_stat_tmp：统计信息的存储目录。这个目录包含了数据库运行时的统计信息，用于性能分析和调优。
• pg_tblspc：存储了指向各个用户自建表空间实际目录的链接文件。在PostgreSQL中，可以创建多个表空间来存储数pg_tblspc目录就是这些表空间的链接。
• pg_twophase：当使用了两阶段提交功能时，分布式事务的存储目录。
• pg_xlog（或pg_wal在某些版本中）：WAL（Write-Ahead Logging）日志的目录。WAL是PostgreSQL实现事务持久性和恢复的核心机制，它记录了所有更改数据的操作，以确保在系统崩溃时可以恢复数据。