N74第七周作业

1.总结postgresql和mysql的优劣势

MySQL	PostgreSQL
最重要的引擎InnoDB很早就由Oracle公司控制。目前整个MySQL数据库都由Oracle控制。	BSD协议，没有被大公司垄断。
对复杂查询的处理较弱，查询优化器不够成熟	很强大的查询优化器，支持很复杂的查询处理。
只有一种表连接类型：嵌套循环连接（nested-loop），不支持排序-合并连接（sort-merge join）与散列连接（hash join）	都支持
性能优化工具与度量信息不足	提供了一些性能视图，可以方便的看发生在一个表和索引上的select、delete、update、insert统计信息，也可以看到cache命中率。网上有一个开源的pgstatspack工具
InnoDB的表和索引都是按相同的方式存储。也就是说表都是索引组织表。这一般要求主键不能太长而且插入时的主键最好是按顺寻递增，否则对性能有很大影响	不存在这个问题
大部分查询只能使用表上的单一索引；在某些情况下，会存在使用多个索引的查询，但是查询优化器通常会低估其成本，它们常常比表扫描还要慢	不存在这个问题
表增加列，基本上是重建表和索引，会花很长时间。	表增加列，只是在数据字典中增加表定义，不会重建表
存储过程与触发器的功能有限。可用来编写存储过程、触发器、计划事件以及存储函数的语言功能较弱	除支持pl/pgsql写存储过程，还支持perl、python、Tcl类型的存储过程：pl/perl，pl/python，pl/tcl。也支持用C语言存储过程。
不支持Sequence	支持
不支持函数索引，只能在创建基于具体列的索引。不支持物化视图	支持函数索引，同时还支持部分数据索引，通过规则系统可以实现物化视图的功能
执行计划并不是全局共享的，仅仅在连接内部是共享的	执行计划共享
MySQL支持的SQL语法（ANSI SQL标准）的很小一部分。不支持递归查询、通用表表达式（Oracle的with语句）或者窗口函数（分析函数）。	都支持
不支持用户自定义类型或域（domain）	支持
对于时间、日期、间隔等时间类型没有秒以下类别的存储类型	可以精确到秒以下
身份验证功能是完全内置的，不支持操作系统认证、PAM认证，不支持LDAP以及其他类似的外部身份验证功能	支持OS认证、Kerberos 认证、Ident的认证、LDAP认证、PAM认证
不支持database link。有一种叫做Federated的存储引擎可以作为一个中转将查询语句传递到远程服务器的一个表上，不过，他功能很粗糙并且漏洞很多	有dblink，同时还有一个dbi-link的东西，可以连接到oracle和mysql上
MySQL Cluster可能与你的想象有较大差异。开源的cluster软件较少。复制（Replication）功能是异步的，并且有很大的局限性。例如，它是单线程的（single-threaded），因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master	有丰富的开源cluster软件支持
explain看执行计划的结果简单	explain返回丰富的信息
类似于ALTER TABLE或CREATE TABLE一类的操作都是非事务性的。它们会提交未提交的事务，并且不能回滚也不能做灾难恢复	DDL也是有事务的

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

PostgreSQL可以通过二进制安装和编译安装两种方式进行部署和安装。

1. 二进制安装：

• 下载二进制文件：从PostgreSQL官方网站（https://www.postgresql.org/download/）下载适用于您操作系统的二进制发行版。
• 安装二进制文件：运行二进制安装程序，并按照安装向导的指示进行操作。通常情况下，您只需选择安装位置、设置数据库超级用户密码等基本配置信息。
• 配置环境变量：将PostgreSQL的可执行文件路径添加到系统的PATH环境变量中，以便您可以在命令行或脚本中直接使用PostgreSQL命令。
• 启动和配置数据库：根据安装向导的提示，启动PostgreSQL数据库服务器，并进行必要的配置，如监听地址、端口号、数据目录等。

2. 编译安装：

• 下载源代码：从PostgreSQL官方网站（https://www.postgresql.org/download/）下载源代码压缩包。
• 安装编译工具：确保您的操作系统已经安装了编译所需的工具和库，如GCC编译器、make命令等。
• 解压源代码：将源代码压缩包解压到您选择的目录中。
• 配置编译选项：进入源代码目录，运行./configure命令，可以根据需要指定一些编译选项，如安装路径、编译器选项等。
• 编译和安装：运行make命令进行编译，然后运行make install命令进行安装。这将会将编译后的可执行文件和相关文件复制到指定的安装路径中。
• 配置环境变量：同样，将PostgreSQL的可执行文件路径添加到系统的PATH环境变量中。
• 启动和配置数据库：按照二进制安装的方式，启动PostgreSQL数据库服务器，并进行必要的配置。

无论是二进制安装还是编译安装，安装完成后，您可以使用PostgreSQL的客户端工具（如psql）连接到数据库，并进行数据库的管理和操作。请记住，在安装和配置过程中，确保遵循官方文档和指南以获取最佳的安装结果。

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

1. PostgreSQL服务管理命令 pg_ctl：

• pg_ctl start：启动PostgreSQL数据库服务器。
• pg_ctl stop：停止正在运行的PostgreSQL数据库服务器。
• pg_ctl restart：重启PostgreSQL数据库服务器。
• pg_ctl reload：重新加载配置文件，不停止数据库服务器。
• pg_ctl status：检查数据库服务器的运行状态。
• pg_ctl promote：将一个热备份的从服务器提升为主服务器。

2. PostgreSQL客户端命令 psql：

• psql -U username dbname：以指定的用户名连接到指定的数据库。
• psql -l：列出当前数据库服务器上的所有数据库。
• psql -c "SQL statement"：执行指定的SQL语句并返回结果。
• psql -f filename：从文件中执行SQL命令。
• psql -h hostname -p port -U username -d dbname：在非默认主机、端口、用户名和数据库上进行连接。

总结起来，pg_ctl命令用于启动、停止和管理PostgreSQL数据库服务器，而psql命令用于与数据库进行交互。不同操作系统上的初始化操作略有差异，但通常都可以使用操作系统提供的服务管理工具或者命令行进行管理和操作。

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

PostgreSQL数据库的结构组织是基于以下几个关键概念：

1. 数据库（Database）：数据库是数据的集合，用于存储和组织相关数据表、视图、函数、触发器等对象。通过数据库，可以将数据按照不同的应用或者逻辑进行分隔和管理。
2. 模式（Schema）：模式是数据库中对象的命名空间，用于在同一个数据库中对对象进行逻辑分组和隔离。每个数据库可以包含多个模式，而模式又可以包含各种对象，如表、视图、函数等。模式提供了更好的组织和管理数据库对象的方式。
3. 表（Table）：表是数据库中最常见的对象，用于存储数据。每个表由一系列列组成，每列定义了表中存储的数据类型。表可以包含行，每一行代表一个数据记录。
4. 视图（View）：视图是虚拟的表，它基于一个或多个基本表或其他视图的查询结果。视图可以隐藏底层表的复杂性，提供简化的、特定需求的数据访问接口。
5. 索引（Index）：索引是一种特殊的数据结构，用于加快对表中数据的查询速度。通过创建索引，可以以更快的速度定位和检索数据。
6. 序列（Sequence）：序列是一种数据库对象，用于生成唯一的、递增或递减的数值序列。序列通常用于为表的主键提供唯一标识符。
7. 函数（Function）：函数是一段可重复使用的代码，接收输入参数并返回结果。函数可以在数据库中定义，并可以被查询语句、触发器等调用和使用。
8. 触发器（Trigger）：触发器是与表相关联的特殊函数，当表上的某个事件（如插入、更新、删除）发生时，触发器会自动执行。

通过合理使用这些结构组织，可以建立清晰的数据库模型，提高数据的组织性、可扩展性和性能。

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

要实现 PostgreSQL 的远程连接，需要进行以下操作：

1. 确保 PostgreSQL 配置文件允许远程连接。在 PostgreSQL 的配置文件 postgresql.conf 中，找到 listen_addresses 参数，并将其设置为 '*'，表示接受来自任意 IP 地址的连接请求。
2. 确保防火墙允许 PostgreSQL 的访问。打开服务器的防火墙配置，确保端口号（默认为 5432）对外开放。如果有需要，你也可以更改 PostgreSQL 的默认端口号。
3. 配置 PostgreSQL 的 pg_hba.conf 文件以允许远程连接。在该文件中，可以通过添加适当的规则，控制哪些主机可以使用密码或无密码进行连接。

• 如果要使用密码进行远程连接，请找到 pg_hba.conf 文件，并添加类似以下规则：

host    all             all             <远程主机IP地址/子网掩码>     md5

这表示任何来自指定 IP 地址的主机都可以使用密码进行连接，并使用 MD5 加密进行验证。当连接时，将需要提供密码。

• 如果要允许无密码连接，请添加类似以下规则：

host    all             all             <远程主机IP地址/子网掩码>     trust

这表示任何来自指定 IP 地址的主机都可以无需密码直接连接到 PostgreSQL。

4. 重启 PostgreSQL 服务，使配置生效。

完成上述操作后，你可以使用以下方式进行远程连接：

• 使用密码登录：在远程主机上使用 psql 命令连接到 PostgreSQL，指定数据库的用户名、密码、主机和端口。

psql -h <主机IP地址> -p <端口号> -U <用户名> -d <数据库名>

连接时，将会提示输入密码。

• 无密码登录：在远程主机上使用 psql 命令连接到 PostgreSQL，指定数据库的用户名、主机和端口。由于之前在 pg_hba.conf 中设置了 trust 规则，无需提供密码。

psql -h <主机IP地址> -p <端口号> -U <用户名> -d <数据库名>

请注意，在进行远程连接时，要确保网络安全，限制只允许受信任的主机进行连接，以减少潜在的安全风险。

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

在 PostgreSQL 中，可以执行以下操作来添加和删除库（database）、模式（schema）和表（table）：

1.添加库：

• 使用 CREATE DATABASE 语句创建一个新的数据库。

CREATE DATABASE database_name;

2.删除库：

• 使用 DROP DATABASE 语句删除一个已存在的数据库。

DROP DATABASE database_name;

3.添加模式：

• 使用 CREATE SCHEMA 语句在特定的数据库中创建一个新的模式。

CREATE SCHEMA schema_name;

4.删除模式：

• 使用 DROP SCHEMA 语句删除一个已存在的模式。

DROP SCHEMA schema_name;

5.添加表：

• 使用 CREATE TABLE 语句在指定的模式或默认模式下创建一个新表。

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

6.删除表：

• 使用 DROP TABLE 语句删除一个已存在的表。

DROP TABLE table_name;

需要注意的是，添加和删除操作需要适当的权限。确保登录的用户具有足够的权限来执行这些操作。此外，还可以使用其他选项和限制来自定义这些操作，如指定表的列、约束、索引等。详细的语法和选项，请参考 PostgreSQL 的官方文档。

在 PostgreSQL 中，进行表数据的 CRUD（Create、Read、Update、Delete）操作可以使用以下语句：

1.创建数据：

• 使用 INSERT INTO 语句向表中插入新的数据行。

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

2.读取数据：

• 使用 SELECT 语句从表中检索数据行。

SELECT column1, column2, ...
FROM table_name
WHERE condition;

3.更新数据：

• 使用 UPDATE 语句修改表中的数据行。

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

4.删除数据：

• 使用 DELETE FROM 语句删除表中的数据行。

DELETE FROM table_name
WHERE condition;

这些语句中，table_name 是要操作的表名，column1, column2, ... 是要操作的表的列名，value1, value2, ... 是要插入或更新的值，condition 是一个可选的条件，用于筛选要操作的数据行。

需要注意的是，对表进行 CURD 操作需要相应的权限。确保登录的用户具有足够的权限来执行这些操作。另外，还可以使用其他选项和语句来进一步自定义和优化这些操作，例如使用子查询、连接操作等。详细的语法和选项，请参考 PostgreSQL 的官方文档。

在 PostgreSQL 中，可以使用以下语句来查看数据库、模式、表和其他相关信息：

1. 查看数据库：

• 使用 \l 或者 \list 命令查看所有的数据库。
• 使用 \c 或者 \connect 命令连接到指定的数据库。

2. 查看模式：

• 使用 \dn 命令查看当前数据库中的所有模式。

3. 查看表：

• 使用 \dt 命令查看当前数据库中的所有表。
• 使用 \d table_name 命令查看指定表的结构和详细信息。

4. 查看索引：

• 使用 \di 命令查看当前数据库中的所有索引。
• 使用 \d table_name 命令查看指定表的索引信息。

5. 查看视图：

• 使用 \dv 命令查看当前数据库中的所有视图。
• 使用 \d view_name 命令查看指定视图的定义和相关信息。

6. 查看函数和存储过程：

• 使用 \df 命令查看当前数据库中的所有函数和存储过程。
• 使用 \df+ function_name 命令查看指定函数的定义和更多信息。

7. 查看触发器：

• 使用 \d table_name 命令查看指定表的触发器信息。

8. 查看用户和权限：

• 使用 \du 命令查看当前数据库中的所有用户。
• 使用 \dp 命令查看当前数据库中的所有表和对象的权限信息。

这些命令可以在 PostgreSQL 命令行界面（psql）中使用。在 psql 中，输入命令后按下回车即可查看相应的信息。此外，还可以使用 SQL 查询语句结合系统表（如 information_schema 等）来获取更详细的数据库元数据信息。

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

PostgreSQL 中的用户和角色管理涉及创建、修改和授权等操作。以下是对 PostgreSQL 用户和角色管理的总结：

1.创建用户或角色：

• 使用 CREATE ROLE 命令创建新的用户或角色。

CREATE ROLE username [OPTIONS];

2.修改用户或角色：

• 使用 ALTER ROLE 命令修改用户或角色的属性。

ALTER ROLE username [OPTIONS];

3.删除用户或角色：

• 使用 DROP ROLE 命令删除指定的用户或角色。

DROP ROLE username;

4.为用户或角色分配权限：

• 使用 GRANT 命令为用户或角色授予特定的权限。

GRANT {privilege [, ...] | ALL PRIVILEGES}
    ON [TABLE] tablename [, ...]
    TO {username | GROUP rolename} [, ...]
    [WITH GRANT OPTION];

5.撤销用户或角色的权限：

• 使用 REVOKE 命令从用户或角色中撤销指定的权限。

REVOKE {privilege [, ...] | ALL PRIVILEGES}
    ON [TABLE] tablename [, ...]
    FROM {username | GROUP rolename} [, ...];

6.分配角色给用户：

• 使用 GRANT 命令将角色授予用户，使其具有角色的权限。

GRANT rolename TO username;

7.取消用户的角色：

• 使用 REVOKE 命令从用户中撤销指定的角色。

REVOKE rolename FROM username;

8.查看用户和角色：

• 使用 \du 命令查看当前数据库中的所有用户和角色。

以上是 PostgreSQL 中常用的用户和角色管理操作。通过创建用户和角色，并授予适当的权限，可以实现灵活的访问控制和权限管理。请注意，在执行这些操作时需要相应的权限，确保以具有足够权限的用户连接到数据库。

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

CREATE USER mage WITH PASSWORD 'password';

CREATE DATABASE zabbix;

CREATE SCHEMA magedu AUTHORIZATION mage;

GRANT ALL PRIVILEGES ON DATABASE zabbix TO mage;

ALTER ROLE mage SET search_path = magedu;

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

PostgreSQL 是一个基于进程的数据库管理系统，它采用了多进程架构以实现高效的并发处理。以下是 PostgreSQL 的进程结构概要和进程间的协同工作方式：

1. 前台进程（Frontend Processes）：

• 前台进程由客户端应用程序启动，用于与 PostgreSQL 服务器进行通信。
• 每个前台进程都与一个后台进程关联，并通过套接字上的通信管道发送请求和接收响应。

2. 后台进程（Backend Processes）：

• 后台进程是由 PostgreSQL 服务器自动启动和管理的。
• 每个后台进程与一个前台进程关联，负责处理前台进程提交的请求。
• 各种类型的后台进程执行不同的任务，如查询处理、事务管理、自动保存点、日志记录等。

3. 主进程（Postmaster Process）：

• 主进程是 PostgreSQL 服务器的起始点，负责启动和管理其他进程。
• 它监听数据库服务器的连接请求，根据需要启动新的后台进程处理每个连接。

4. 共享内存（Shared Memory）：

• 共享内存是多个进程之间共享数据的机制。
• PostgreSQL 使用共享内存来存储系统缓存、锁、数据页等共享信息。

进程间的协同工作方式如下：

1. 客户端应用程序通过套接字连接到 PostgreSQL 服务器，启动前台进程与主进程建立通信。
2. 主进程接收连接请求，并根据需要分派一个后台进程来处理该连接。
3. 前台进程将请求发送给后台进程，后台进程执行相应的任务（如查询处理、事务管理等）。
4. 后台进程访问共享内存中的数据，并根据需要进行读取、更新和写入操作。
5. 后台进程将处理结果返回给前台进程，前台进程将结果传递给客户端应用程序。
6. 后台进程可以同时处理多个请求，每个请求都通过前台进程进行传递。

通过这种进程间的协同工作方式，PostgreSQL 实现了高效的并发处理能力和稳定的数据库服务。不同类型的后台进程负责不同的任务，并通过共享内存在进程之间共享数据，从而实现了数据库的正常运行和有效的资源利用。

Postmaster主进程

• 它是整个数据库实例的主控制进程，负责启动和关闭该数据库实例。
• 实际上，使用pg_ctl来启动数据库时，pg_ctl也是通过运行postgres来启动数据库的，只是它做了一些包装，更容易启动数据库。
• 它是第一个PostgreSQL进程，此主进程还会fork出其他子进程，并管理它们。
• 当用户和PostgreSQL建立连接时，首先是和Postmaster进程建立连接。首先，客户端会发出身份验证的信息给Postmaster进程，Postmaster进程根据消息中的信息进行身份验证判断，如果验证通过，它会fork出一个会话子进程为这个连接服务。
• 当某个服务进程出现错误的时候，Postmaster主进程会自动完成系统的恢复。恢复过程中会停掉所有的服务进程，然后进行数据库数据的一致性恢复，等恢复完成后，数据库又可以接受新的连接。
• 验证功能是通过配置文件pg_hba.conf和用户验证模块来提供。
• postmaster程序是指向postgres的软链接

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）传递消息来启动检查点请求

AutoVacuum自动清理进程

• 执行delete操作时，旧的数据只是被标识为删除状态，在没有并发的其他事务读到这些旧数据时，它们才会被清除掉
• AutoVacuum Launcher负责回收垃圾数据的master进程，如果开启了Autovacuum的话，那么postmaster会fork这个进程

PgStat统计数据收集进程

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

PgArch归档进程

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

SysLogger系统日志进程

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

startup启动进程

• 用于数据库恢复的进程

Session会话进程

• 每一用户发起连接后，一旦验证成功，postmaster进程就会fork一个新的子进程负责连接此用户
• 通常表现为进程形式：postgres postgres [local] idle

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

postgresql.conf 		#数据库实例的主配置文件，基本上所有的配置参数都在此文件中
pg_hba.conf					#认证配置文件，配置了允许哪些IP的主机访问数据库，认证的方法是什么等信息
pg_ident.conf				#认证方式ident的用户映射文件

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可查看），主进程状态

11.尝试将PostgreSQL新版本的运行日志存储到数据库

譬如将csv格式运行日志存储在数据库

vim /pgsql/data/postgresql.conf

#修改下面两行

log_destination='csvlog'
logging_collector=on

pg_ctl restart
psql

#先创建对应的表结构，只适用于PG12
testdb=# CREATE TABLE pg_log(
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
sesion_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sq1_state_code text,
message text,
detail text,
hint text,
interna7_query text,
interna7_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text,
PRIMARY KEY (session_id, session_line_num)
);

testdb=# copy pg_log from '/pgsql/data/log/postgresql-2020-10-07_090454.csv' with csv;

12.图文并茂总结LSN和WAL日志相关概念

LSN（Log Sequence Number）是用于管理事务日志（transaction log）的一种标识。它是一个递增的，唯一标识每个事务日志记录的数值。LSN被用于确保事务的持久性和一致性。

WAL（Write-Ahead Logging）是一种事务日志机制，它在数据库引擎中被广泛使用。WAL日志包含了对数据库进行修改的所有操作，包括插入、更新和删除等。WAL日志的使用主要有两个目的：

1. 持久性：在进行实际数据修改之前，首先将修改操作记录到WAL日志。这样可以确保即使在数据库崩溃或断电时，已提交的事务仍然可以恢复和重放，从而保证数据的持久性。
2. 并发控制：WAL日志的使用还可以帮助保证并发控制的正确性。通过记录每个事务的开始和提交等信息，可以在并发访问时避免脏读、不可重复读和幻读等问题，从而提供一致性视图。

在WAL日志中，每个日志记录都有一个唯一的LSN值。LSN用于标识日志记录的顺序，并确定哪些日志记录已经持久化到磁盘。通过比较LSN值，可以确定是否有新的日志记录需要被写入磁盘，以及哪些日志记录可以被清除。

总结起来，LSN是用于唯一标识和管理事务日志记录的数值，而WAL日志是一种持久性和并发控制机制，通过记录对数据库的修改操作来保证数据的持久性和一致性。LSN在WAL日志中起到了重要的作用，用于标识日志记录的顺序和持久化状态。

13.实现WAL日志多种类型的备份，及数据还原

开启WAL日志备份：

vim postgresql.conf

#配置归档需要开启如下参数：
wal_level=replica
archive_mode=on

#自定义归档命令，可以是shell命令或者复杂的shell脚本
archive_command = 'DIR=/archive/$(date +%F);[ -d ${DIR} ] || mkdir -p ${DIR};cp %p ${DIR}/%f'
archive_command = 'scp %p postgres@10.0.0.200:/pgsql/backup/%f'
archive_command = '[ ! -f /archive/%f ] && cp %p /archive/%f'

#用'%p'表示将要归档的wal文件包含完整路径的信息的文件名
#用'%f'代表不包含路径信息的wal文件的文件名

#重启数据库
systemctl restart postgresql
或
pg_ctl restart

逻辑备份：

pg_dump是PostgreSQL数据库的一种命令行工具，用于实现数据库的备份和还原操作。通过pg_dump命令，你可以将整个数据库或者特定的数据库对象以一种可恢复的格式导出到一个文件中。导出的文件可以被pg_restore工具用于还原数据库。

使用pg_dump命令需要提供数据库连接信息以及导出选项。以下是一些常用的pg_dump命令选项：

• -h 指定数据库服务器的主机名或IP地址
• -p 指定数据库服务器的端口号
• -U 指定连接数据库的用户名
• -d 指定要导出的数据库名称
• -f 指定导出文件的路径和名称
• -F 指定导出文件的格式，支持的格式包括：c (custom)，d (directory)，t (tar)，p (plain text)
• -C 在导出时包含创建数据库的命令

以下是一个示例命令，用于将数据库"mydatabase"导出为一个自定义格式的文件：

pg_dump -h localhost -p 5432 -U myuser -d mydatabase -F c -f /path/to/backup.dump

注意，在执行pg_dump命令时，需要确保具有足够的权限来连接和导出数据库。

pg_restore是PostgreSQL数据库的命令行工具，用于还原由pg_dump导出的数据库备份文件。通过pg_restore命令，你可以将备份文件中的数据和结构还原到一个新的或已存在的数据库中。

使用pg_restore命令需要提供数据库连接信息以及还原选项。以下是一些常用的pg_restore命令选项：

• -h 指定数据库服务器的主机名或IP地址
• -p 指定数据库服务器的端口号
• -U 指定连接数据库的用户名
• -d 指定要还原的目标数据库名称
• -c 在还原之前清空目标数据库
• -C 创建一个新的目标数据库（如果目标数据库不存在）
• -j 指定并行还原的作业数

以下是一个示例命令，用于从一个自定义格式的备份文件中还原到数据库"mydatabase"：

pg_restore -h localhost -p 5432 -U myuser -d mydatabase /path/to/backup.dump

注意，在执行pg_restore命令时，需要确保具有足够的权限来连接和还原数据库。另外，还原操作可能会覆盖目标数据库中的数据和结构，请谨慎操作并确保备份文件的可靠性。

pg_dumpall是PostgreSQL数据库的命令行工具，用于将整个PostgreSQL集群中的所有数据库和角色导出为一个文件。与pg_dump不同，pg_dumpall导出的是整个PostgreSQL集群的元数据而不是单个数据库的数据。

使用pg_dumpall命令需要提供数据库连接信息以及导出选项。以下是一些常用的pg_dumpall命令选项：

• -h 指定数据库服务器的主机名或IP地址
• -p 指定数据库服务器的端口号
• -U 指定连接数据库的用户名
• -f 指定导出文件的路径和名称

以下是一个示例命令，用于将整个PostgreSQL集群中的所有数据库和角色导出为一个SQL文件：

pg_dumpall -h localhost -p 5432 -U myuser -f /path/to/backup.sql

注意，在执行pg_dumpall命令时，需要确保具有足够的权限来连接和导出所有数据库和角色。另外，还原操作可能会覆盖集群中的所有数据库和角色，请谨慎操作并确保备份文件的可靠性。

在PostgreSQL中，COPY命令用于将数据从文件导入到数据库表中，或将数据库表中的数据导出到文件。它提供了高效的数据加载和导出功能。

COPY命令支持以下几种操作方式：

1.从文件导入数据到表：

COPY table_name(column1, column2, ...) FROM 'file_path' [OPTIONS];

这将从指定的文件中读取数据，并将其插入到指定的表中的对应列中。

2.从查询结果导出数据到文件：

COPY (SELECT column1, column2, ... FROM table_name WHERE condition) TO 'file_path' [OPTIONS];

这将执行指定的查询，将查询结果导出到指定的文件中。

3.从表导出数据到文件：

COPY table_name(column1, column2, ...) TO 'file_path' [OPTIONS];

这将将指定表中的数据导出到指定的文件中。

在以上命令中，可以使用一些选项来控制数据的格式、分隔符、引用标识符等。常用的选项包括：

• DELIMITER 'delimiter': 指定字段之间的分隔符，默认为制表符（'\t'）。
• NULL 'null_string': 指定表示NULL值的字符串，默认为空字符串（''）。
• FORMAT format_type: 指定导入/导出的数据格式，可选值为'csv'、'text'、'binary'等。
• HEADER [boolean]: 指定是否在导出时包含列名作为文件的第一行，默认为不包含。

下面是一个示例，将数据从CSV文件导入到数据库表中：

COPY mytable(column1, column2, ...) FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;

请注意，在执行COPY命令时，确保文件路径和表名正确，并且具有足够的权限来读取文件或写入表。另外，还要确保文件的格式与COPY命令中指定的格式相匹配。

物理备份：

在PostgreSQL中，进行冷备份（Cold Backup）是一种备份数据库的方法，它需要停止数据库服务并拷贝数据库文件。下面是进行冷备份的一般步骤：

1.停止数据库服务：通过执行以下命令来停止PostgreSQL数据库服务：

sudo systemctl stop postgresql

2.备份数据目录：默认情况