1总结pg和mysql的优劣势。
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公司控制。
MYSQLx相对pg的优势：
innodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触 发VACUUM，会带来多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀。
MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结构设计存在约束。
MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作。
MySQL相对于PG在国内的流行度更高，PG在国内显得就有些落寞了。
MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，myisam适合静态数据的查询场景。
总结：
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二进制安装和编译安装。
二进制安装包进行安装
各linux发行版中大多都内置了PGsql的二进制安装包，但内置版本相对旧一些，对于二进制包的安装方法是通过不同发行版本的Linux下的包管理器进行的，如在THEL系统相关版本下用yum命令，在Ubuntu下用apt命令。
源码编译安装
使用源码编译安装更为灵活，用户可以有更多的选择，可以选择较新的版本、配置不同的编译选项，编译出用户喜欢的功能
例1：利用官方源的二进制包安装PostgreSQL14
[root@Rocky8 ~]# cat install.sh
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql14-server
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14
[root@Rocky8 ~]# bash install.sh
验证：
[root@Rocky8 ~]# sudo -u postgres psql -c "SELECT version()";
could not change directory to "/root": Permission denied
version
--------------------------------------------------------------------------------------
-------------------
PostgreSQL 14.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat
8.5.0-18), 64-bit
(1 row)
[root@Rocky8 ~]# su - postgres
[postgres@Rocky8 ~]$ pslq
-bash: pslq: command not found
[postgres@Rocky8 ~]$ psql
psql (14.9)
Type "help" for help.
例2：源码编译安装
安装前准备
[root@Rocky8 ~]# ls
anaconda-ks.cfg date hellodb_innodb.sql postgresql-14.2.tar.gz
[root@Rocky8 ~]# tar xf postgresql-14.2.tar.gz
[root@Rocky8 ~]# cd postgresql-14.2/
[root@Rocky8 postgresql-14.2]# ls
aclocal.m4 configure contrib doc HISTORY Makefile src
config configure.ac COPYRIGHT GNUmakefile.in INSTALL README
[root@Rocky8 postgresql-14.2]# cat INSTALL
PostgreSQL Installation from Source Code
------------------------------------------------------------------------
This document describes the installation of PostgreSQL using this source
code distribution.
If you are building PostgreSQL for Microsoft Windows, read this document
if you intend to build with MinGW or Cygwin; but if you intend to build
with Microsoft's Visual C++, see the main documentation instead.
------------------------------------------------------------------------
Short Version
./configure
make
su
make install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test
The long version is the rest of this document.
开始编译安装
./configure --prefix=/apps/pgsql --with-pgport=5432
make -j 2 world
make install-world
创建数据用户和组
[root@Rocky8 postgresql-14.2]# useradd -s /bin/bash -m -d /home/postgres postgres
[root@Rocky8 postgresql-14.2]# echo -e '123456\n123456' | passwd postgres
Changing password for user postgres.
New password: BAD PASSWORD: The password is shorter than 8 characters
Retype new password: passwd: all authentication tokens updated successfully.
创建数据目录
[root@Rocky8 postgresql-14.2]# mkdir -pv /pgsql/data/
mkdir: created directory '/pgsql'
mkdir: created directory '/pgsql/data/'
[root@Rocky8 postgresql-14.2]# chown postgres.postgres /pgsql/data/
设置环境变量和验证
[root@Rocky8 postgresql-14.2]# cat /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
[root@Rocky8 postgresql-14.2]# su - postgres
[postgres@Rocky8 ~]$ ps
ps psfgettable psfxtable psql pstree
psfaddtable psfstriptable pslog pstack pstree.x11
[postgres@Rocky8 ~]$ psql --version
psql (PostgreSQL) 14.2
初始化数据库：
[postgres@Rocky8 ~]$ initdb
启动服务：
[postgres@Rocky8 ~]$ pg_ctl -D /pgsql/data -l start
pg_ctl: no operation specified
Try "pg_ctl --help" for more information.
[postgres@Rocky8 ~]$ pg_ctl -D /pgsql/data start
waiting for server to start....2023-10-18 11:27:08.101 CST [13619] LOG: starting PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit
2023-10-18 11:27:08.102 CST [13619] LOG: listening on IPv6 address "::1", port 5432
2023-10-18 11:27:08.102 CST [13619] LOG: listening on IPv4 address "127.0.0.1", port 5432
2023-10-18 11:27:08.103 CST [13619] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-10-18 11:27:08.104 CST [13620] LOG: database system was shut down at 2023-10-18 11:26:07 CST
2023-10-18 11:27:08.106 CST [13619] LOG: database system is ready to accept connections
done
server started
[postgres@Rocky8 ~]$ ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 127.0.0.1:5432 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::1]:5432 [::]:*
LISTEN 0 128 [::]:22 [::]:*
创建启动脚本
chmod +x /etc/init.d/postgresql
chkconfig --add postgresql
vi /etc/init.d/postgresql
# Installation prefix
prefix=/apps/pgsql
# Data directory
PGDATA="/pgsql/data"
开机自启动
[root@Rocky8 ~]# cat /etc/rc.local
#!/bin/bash
# THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES
#
# It is highly advisable to create own systemd services or udev rules
# to run scripts during boot instead of using this file.
#
# In contrast to previous versions due to parallel execution during boot
# this script will NOT be run after all other services.
#
# Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure
# that this script will be executed during boot.
touch /var/lock/subsys/local
#su - postgres -c "/app/pgsql/bin/pg_ctl -l logfile start"
/etc/init.d/postgresql start
3、总结pg服务管理相关命令 pg_ctl 和pgsql命令选项及示例和不同系统的初始化操作
初始化数据库 initdb -D $PGDATA #
-D 指定数据库实例的数据目录，使用环境变量PGDATA指定的路径 pg_ctl init
查看服务状态 pg_ctl status #-D
启动服务 pg_ctl -D $PGDATA -l logfile start
-D datadir#指定数据库实例的数据目录 -l #服务器日志输出到logfile中
停止数据库服务 pg_ctl stop -D $PGDATA -m #指定数据库停止方法 smart:等待所有连接终止后，关闭数据库 fast:快速关闭数据库，断开客户端的连接 immediate:立刻关闭数据库
重启服务 pg_ctl restart
加载配置 pg_ctl reload
将从服务器提升为主服务器，恢复读写操作 pg_ctl promote
psql命令 psql -h
4、总结pg数据库结构组织
数据的组织结构可以分为五层：
实例：一个postsql对应安装的数据目录$PGDATA,即一个instance实例
数据库：一个postgresql数据库服务下可以管理多个数据库，当应用连接到一个数据库时，一般只能访问这个数据库中的数据
模式：一个数据库可以创建多个不同的名称空间Schema,用于分隔不同的业务数据
表和索引：一个数据库可以有多个表和索引，在postgressql中的术语为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 ； 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的用户和角色管理。
创建用户后直接可以用密码登录
postgres=# create USER ju PASSWORD '123456';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
ju | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
创建角色以后需要授权才可以登录
postgres=# CREATE ROLE LIANG PASSWORD '123456';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
ju | | {}
liang | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
授权角色让其具有登录权限
postgres=# ALTER ROLE liang WITH LOGIN ;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
ju | | {}
liang | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
删除角色
postgres=# drop role liang;
DROP ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
ju | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
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后台写进程
-
为了提高，插入，删除和更新数据的性能，当数据更新时，并不会马上把数据持久化到数据文件中，而是先写入Buffer中。
-
改辅助进程可以周期性的把内存中的数据刷新到磁盘中。
WaWriter预写式日志进程
-
WAL是write ahead log的缩写，WAL log旧版中称为xlog相当于MYsql中的Redo log.
-
预写式日志是在修改数据之前，必须把这些修改记录到磁盘中，这样更新实际数据时,就不需要把数据实时到持久化到文件中,即使出现异常,导致部分数据没有刷新到文件中,在数据库重启时，通过读取WAL日志，把最后一部分WAL日志重新执行一遍，就能恢复到异常时的状态。
-
WAL日志保存在pg_wal目录中(早期版本为pg_xlog).文件默认16MB，为了满足恢复要求，在pg_log下会产生多个日志，未持久化的数据都可以通过WAL日志来恢复，那些不需要的WAL日志将会自动覆盖。
Checkpointer检查进程点。
-
检查点是事务序列中的点，保证在改检查点之前的日志信息都更新到文件中。
-
在检查点时，所有脏数据都冲刷到磁盘并向日志文件中写入一条特殊的检查点记录。
-
检查点进程（CKPT）在特定时间执行一个检查点，通过向数据库写入进程（BgWirter）传递消息来启动检查点请求。
AutoVacuum自动清理进程。
-
执行删除操作时，旧数据不会被立即删除，在更新数据时，也不会在旧数据上做更新，而是新生成一行数据。旧的数据只是被标识为删除状态，在没有并发的其他事务读到这些旧数据时，它们才会被清除掉。
-
Autovacuum launcher 负责回收垃圾数据的worker进程，如果开启了Autovacuum的话，Postmaster就会fork这个进程。
-
Autovacuum worker负责回收垃圾数据的worker进程,是launcher进程fork出来的。
PgStat统计数据收集进程。
-
此进程主要做数据的统计收集工作。
-
收集的信息主要用于查询优化时的代价估算。
-
系统表pg_statistic中存储了PgStat收集的各类统计信息。
PgArch归档进程
-
默认没有此进程，开启归档功能后才会启动改进程。
-
WAL日志文件会被循环利用，也就是说WAL文件会被覆盖，利用PgArxh进程会在覆盖前把WAL日志备份出来，类似于binlog,可以用来备份。
-
PGsql从8.0版本开始提供了PITR技术，就是对数据库做一次全量备份后，该技术将备份时间点后面的WAL日志通过备份归档来进行备份，将来可以用数据库的全量备份再加上后面产生的WAL日志，即可把数据库向前恢复到全量备份的任意一个时间点的状态。
Syslogger系统日志进程。
-
默认没有此进程，在配置文件postgresql.conf中将logging_collerct设置为‘on’时，主进程才会启动Syslogger辅助进程。
-
它从Postmaster主进程，所有服务进程以及其他的辅助进程收集所有的stderr输出，并将这些输出写入到日志文件中。
Starup启动进程。
-
用户数据库恢复的进程。
Session会话进程。
-
每一个用户发起连接后，一旦验证成功Postmaster进程就会fork一个新的子进程负责连接此用户。
-
通常表现为进程形式：postgres postgrest [local] idle
10、总结pgsql的数据目录中结构，说明每个文件的作用，并可以配上一些示例说明文件的作用。
数据库存放在环境变量PGDATA指向数据目录。这个目录在安装时指定的，所以在安装时需要指定一个合适的目录作为数据库目录的根目录，而其每一个实例都要有一个对应的目录，目录的初始化用initdb来完成。
初始化完成后，PGDATA目录下就会生成三个配置文件。
Postgresql.conf 数据库实例的主配置文件，基本上所有的配置项都在此文件中。
Pg_hba.conf 认证配置文件，配置了那些IP允许访问数据库，通过那种认证方式等。
Pg_ident.conf 认证方式ident的用户映射文件。
此外在PGDATA目录下还会生成一下子目录
base 默认表空间目录，每个数据库都对应一个base目录下的子目录，每个表和索引对应一个独立文件。
global 这个目录对应pg_golbal表空间，存放对象中的共享对象。
Pg_clog 存储事务提交状态数据。
Pg_bba.conf 数据访问控制文件。
Pg_log 数据库系统日志目录，在查询一些系统错误信息时就可以查看此目录文件中的上下文日志。
Pg_xact t提交日志commit log的目录，pg9之前是pg_clog.
Pg_multixact 共享行锁的事务状态数据。
Pg_notify 异步消息相关的状态数据，pg_serial 串行隔离级别的事务状态数据。
Pg_snaoshot 存储执行了事务snaoshot导出的状态数据pg_stat_tmp 统计信息的临时文件
Pg_subtrans 子事务数据状态。
Pg_stat 统计信息的存储目录，关闭服务时，将pg_stat_tmp目录中的内容移动至此目录实现保存。
Pg_tblsp 存储了指向各用户自建表空间实际目录的链接文件。
Pg_twophase 使用两阶段提交功能时分布事务的存储目录。
Pg_wal WAL日志的目录，早期版本目录为pg_xlog.
PG_VERSION 数据库版本信息。
Postmaster.opst 记录数据库启动时的命令行选项。
Postmaster.pid 数据库启动时的主进程信息文件，包括PID，SPGDATA目录，数据库启动时间，监听端口，socket文件路径，监听地址，共享内存的地址信息（ipsc可查看），主进程状态。
postgresql.conf 常用配置说明：
(1) listen_addresses='' #监听客户端的地址，默认是本地的，需要修改为或者0.0.0.0
(2) port = 5432 #pg端口，默认是5432
(3) max_connections = 2000 #最大连接数，默认100
(4) unix_socket_directories #socket文件的位置，默认在/tmp下面
(5) shared_buffers #数据缓存区，建议值1/4--1/2主机内存，和Oracle的buffer cache类似
(6) maintenance_work_mem #维护工作内存，用于vacuum,create index,reindex等。建议值 (1/4主机 内存)/autovacuum_max_workers
(7) max_worker_processes #总worker数
(8) max_parallel_workers_per_gather #单条QUERY中，每个node最多允许开启的并行计算 WORKER数
(9) wal_level #wal级别，版本11+默认是replica
(10) wal_buffers #类似Oracle的log buffer
(11) checkpoint_timeout #checkpoint时间间隔
(12) max_wal_size #控制wal的最大数量
(13) min_wal_size #控制wal的最小数量
(14) archive_command #开启归档命令，示例：'test ! -f /arch/%f && cp %p /arch/%f'
(15) autovacuum #开启自动vacuum
pg_ident.conf 配置文件使用说明：
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
日志文件说明：
(1)运行日志：$PGDATA/log (pg10之前为$PGDATA/pg_log),默认不存在，需要开启配置项 logging_collector
(2)在线重做日志：$PGDATA/pg_wal (pg10之前为$PGDATA/pg_xlog)
(3)事务提交日志：$PGDATA/pg_xact (pg10之前为$PGDATA/pg_clog)
(4)服务器日志：可以在启动的时候指定:pg_ctl start -l ./logfile
11、尝试将pgsql新版本的运行日志存储到数据库。
12、图文并茂总结LSN和WAL日志相关概念
LSN: Log Sequence Number(日志序列号)，用于记录WAL文件当前的位置，这是WAL日志唯一的，全局的标识。
WAL:日志中写入是有顺序的，所以必须记录WAL日志的写入顺序，而LSN就是负责给每条产生的WAL日志记录唯一的编号。
pg_current_wal_lsn()：查看当前WAL日志的写入位置
pg_walfile_name(pg_current_wal_lsn())：查看WAL日志文件的名称
pg_walfile_NAME_OFFSET(pg_current_wal_lsn())：查看WAL日志的偏移量
pg_ls_waldir ()：查看WAL目录中的普通文件名称，大小和最后修改时间
pg_switch_wal ()：强制生成新的WAL日志文件
pg_create_restore_point ( name text )：创建恢复点
pg_current_xact_id ()：返回当前事务ID
WAL日志的LSN编号规则：
高32位/低32位。
WAL文件名称为16进制的24个字符组成，每8个字符一组。
每组的意义如下：
00000000 00000001 000000001
时间戳 逻辑ID 物理ID
其中前8位：表示timeline
中间8位：表示logid 即LSN高32位
最后8位：表示logseg 即LSN低32位。
查看当前LSN
select pg_current_wal_lsn();
postgres=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/16A0208
(1 row)
查看WAL日志文件的名称
postgres=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
000000010000000000000001
(1 row)
13、实现WAL日志多种类型的备份，及数据还原。
归档日志记录的是checkpoint前的WAL日志，即数据的历史日志，即把pg_wal里面的在线日志备份出来，功能上归档日志相当于MYsql的二进制日志。
生产环境中为了保证数据的高可用，通常需要开启归档，当系统故障后可以通过归档的日志文件对数据进行恢复。
配置归档需要开启如下参数：
本地备份
创建本地备份目录并授权
[root@Rocky8 ~]# mkdir /archive
[root@Rocky8 ~]# chown -R postgres. /archive
编辑配置文件：
[postgres@Rocky8 ~]$ vi /pgsql/data/postgresql.conf
wal_level = replica # minimal, replica, or logical
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = '[ ! -f /archive/%f ] && cp %p /archive/%f' # command to use to archive alogfile segment
验证：
[root@Rocky8 ~]# ll /archive/
total 98304
-rw-------. 1 postgres postgres 16777216 Oct 26 11:35 000000010000000000000001
-rw-------. 1 postgres postgres 16777216 Oct 26 11:35 000000010000000000000002
-rw-------. 1 postgres postgres 16777216 Oct 26 11:35 000000010000000000000003
-rw-------. 1 postgres postgres 16777216 Oct 26 11:35 000000010000000000000004
-rw-------. 1 postgres postgres 16777216 Oct 26 11:37 000000010000000000000005
-rw-------. 1 postgres postgres 16777216 Oct 26 11:37 000000010000000000000006
远程备份
创建本地备份目录并授权
[root@Rocky8 ~]# mkdir /archive
[root@Rocky8 ~]# chown -R postgres. /archive
基于key认证
[postgres@Rocky8 ~]# ssh-keygen
[postgres@Rocky8 ~]# ssh-copy-id 10.0.0.8
编辑配置文件：
[postgres@Rocky8 ~]$ vi /pgsql/data/postgresql.conf
wal_level = replica # minimal, replica, or logical
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
#archive_command = '[ ! -f /archive/%f ] && cp %p /archive/%f' # command to use to archive alogfile segment
archive_command =`scp %p [email protected]:/archive/%f`
验证：
[root@Rocky8 ~]# ll /archive/
total 98304
-rw-------. 1 postgres postgres 16777216 Oct 26 11:35 000000010000000000000001
-rw-------. 1 postgres postgres 16777216 Oct 26 11:35 000000010000000000000002
-rw-------. 1 postgres postgres 16777216 Oct 26 11:35 000000010000000000000003
-rw-------. 1 postgres postgres 16777216 Oct 26 11:35 000000010000000000000004
-rw-------. 1 postgres postgres 16777216 Oct 26 11:37 000000010000000000000005
-rw-------. 1 postgres postgres 16777216 Oct 26 11:37 000000010000000000000006
14、实现WAL日志完成主从流复制，要求在从节点上进行crontab数据备份，同时手工让主节点宕机，让从节点切换为主节点，并添加新的从节点。
Master:10.0.0.4
Standby:10.0.0.13
Master节点配置:
创建复制的用户并授权
postgres=# create role repluser with replication login password'123456';
修改pg_hba.conf配置进行授权
# Allow replication connections from localhost, by a user with the
# replication privilege.
host replication repluser 0.0.0.0/0 md5
Standby节点配置
清空数据和归档
[postgres@Rocky8 ~]$ rm -rf /pgsql/data/*
[postgres@Rocky8 ~]$ rm -rf /archive/*
[postgres@Rocky8 ~]$ rm -rf /pgsql/backup/*
备份主数据库到备库
[root@Rocky8 ~]# pg_basebackup -D /pgsql/backup/ -Ft -Pv -Urepluser -h10.0.0.4 -p5432 -R
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/B000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_1893"
57451/57451 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/B000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
还原数据
[root@Rocky8 ~]# tar xf /pgsql/backup/base.tar -C /pgsql/data/
[root@Rocky8 ~]# tar xf /pgsql/backup/pg_wal.tar -C /archive/
修改postgrsql.conf配置文件
primary_conninfo = 'host=10.0.0.4 prot=5432 user=repluser password=123456'
restore_command = 'cp /archive/%f %p'
重启服务，查看状态。
[postgres@Rocky8 ~]$ pg_ctl restart
[postgres@Rocky8 ~]$ pg_controldata
pg_control version number: 1300
Catalog version number: 202107181
Database system identifier: 7291134621650662668
Database cluster state: in archive recovery
pg_control last modified: Fri 27 Oct 2023 1
15、总结日志记录的内容包含什么
历史事件：时间，地点，人物，事件
日志级别：事件的关键性程度，Loglevel
16、总结日志分类, 优先级别。图文并茂解释应用如何将日志发到rsyslog,并写到目标。
(1)日志分类：
#内置分类
Faclility:设施，从功能或程序上对日志进行分类。
auth, authpriv, cron, daemon,ftp,kern, lpr, mail, news, security(auth), user, uucp, syslog #自定义的分类
local0-local7
(2)优先级别：
Priority优先级别，从低到高排序。
debug,info, notice, warn(warning), err(error), crit(critical), alert, emerg(panic)
17、总结rsyslog配置文件格式
/etc/rsyslog.conf配置文件格式：由三部分组成
- MODULER:相关模块配置
- GLOBAL DIRECTIVES：全局配置
- RULES:日志记录相关的规则配置。
RULESPES配置格式：
Facility.priorty;facility.priority……target
Facility格式
*** #所有的facility**
Facility1,facility2,facility3,….. 指定的facility列表。
Priority格式：
*： 所有级别
None: 没有级别，即不记录。
PRIORITY: 指定级别（含）以上的所有级别。
= PRIORITY:仅记录指定级别的日志信息。
Target格式：
文件路劲：通常在/var/log/,文件路径前的-表示异步写入。
用户：将日志事件通知给指定用户，*表示登录的所有用户。
日志服务器：@host把日志传送给指定的远程UDP日志服务器，@@host将日志传送给指定的TCP日志服务器。
管道： | COMMAND ,转发给其他命令处理。
18.完成功能，sshd应用将日志写到rsyslog的local6分类，过滤所有级别，写入到/var/log/ssh.log。
修改ssh的配置文件
[root@Rocky8 ~]# cat /etc/ssh/sshd_config | grep LOCAL
SyslogFacility LOCAL6
修改rsyslog的配置文件
[root@Rocky8 ~]# cat /etc/rsyslog.d/test.conf
local6.* /var/log/ssh.log
[root@Rocky8 ~]#
重启服务
[root@Rocky8 ~]# systemctl restart rsyslog.service sshd
验证
[root@Rocky8 ~]# tail -f /var/log/ssh.log
Oct 27 17:05:03 Rocky8 sshd[37028]: Server listening on 0.0.0.0 port 22.
Oct 27 17:05:03 Rocky8 sshd[37028]: Server listening on :: port 22.
Oct 27 17:05:43 Rocky8 sshd[37070]: Accepted password for root from 10.0.0.14 port 49148 ssh2
Oct 27 17:05:49 Rocky8 sshd[37075]: Received disconnect from 10.0.0.14 port 49148:11: disconnected by user
Oct 27 17:05:49 Rocky8 sshd[37075]: Disconnected from user root 10.0.0.14 port 49148
19、完成功能，将3个主机(要求主机名为ip)的ssh日志，通过rsyslog服务将ssh日志写入到集中的主机上的rsyslog服务，写入到/var/log/all-ssh.log文件
环境：rsyslog-server 10.0.0.34
三台服务器：10.0.0.4 10.0.0.14 10.0.0.24
rsyslog-server配置
[root@Rocky8 ~]# cat /etc/rsyslog.conf | grep imudp
# for parameters see http://www.rsyslog.com/doc/imudp.html
module(load="imudp") # needs to be done just once
input(type="imudp" port="514")
新建一个配置文件，专门存放ssh的相关日志
[root@Rocky8 ~]# cat /etc/rsyslog.d/sshd.conf
local6.* /var/log/ssh.log
重启rsyslog服务，并且查看端口
[root@Rocky8 ~]# ss -nul
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
UNCONN 0 0 127.0.0.1:323 0.0.0.0:*
UNCONN 0 0 0.0.0.0:514 0.0.0.0:*
UNCONN 0 0 [::1]:323 [::]:*
UNCONN 0 0 [::]:514 [::]:*
其他三台服务器配置：
修改日志服务配置，并且重启
[root@Rocky8 ~]# cat /etc/ssh/sshd_config | grep local6
SyslogFacility local6
[root@Rocky8 ~]# cat /etc/rsyslog.d/test.conf
local6.* /var/log/ssh.log
20、总结/var/log/目录下常用日志文件作用。
/var/log/secure 系统安全日志，文本格式，应周期性分析。
/var/log/btmp 当前系统上，用户的失败尝试登录相关的日志，二进制格式，lastb可进行查看。
/var/log/wtmp 当前系统上，用户正常登录的相关日志信息，二进制格式，last可进行查看。
/var/log/lastlog 每一个用户最近一次的登录信息，二进制格式，lastlog可进行查看。
/var/log/dmesg Centos7之前版本系统引导过程的日志信息，文本格式，开机后的硬件变化不在记录，也可以通过命令dmesg查看，可持续查看硬件变化的信息。
/var/log/boot.log 系统启动的相关信息，文本格式。
/var/log/messages 系统中的大部分日志信息。
/var/log/anaconda anaconda的日志信息。
21、总结journalctl命令的选项及示例
日志的配置文件
/etc/systemd/journald.conf
Journalctl [options….] [MATCHES….]
查看所有日志（默认情况下只保存本次启动的日志）
Journalctl
查看内核日志（不显示应用日志）
Journalctl –k
查看系统本次启动的日志
Journalctl –b
Journalctl –b -0
查看上一次启动的日志（需更改设置）
查看指定时间的日志
journalctl --since='2023-8-15 07:00:00'
journalctl –since '20 min ago'
journalctl –since 'yesterday'
journalctl –since '2023-8-10 07:00:00' –until '2023-8-15 07:00:00'
journalctl –since 09:00 –until ‘1 hour ago’ journalctl
显示尾部最新10行日志
Journalctl –n
显示尾部指定行数的日志
Journalctl –n 20
实时动态显示日志
Journalctl –f
查看指定服务的日志
Journalctl /usr/lib/system/system
查看指定进程的日志
Journalctl _PID=1000
查看某个路劲的脚本日志
Journalctl /usr/bin/bash
查看指定用户的日志
Journalctl _UID=1000 –since tody
查看某个Unit的日志
Journalctl -u nginx.server
Journalctl -u nginx.server –since today
合并显示多个Unit的最新日志
Journalctl -u nginx.server –u php-fpm.server --since today
查看指定优先级（及以上级别）的日志，共有8级。
0:emerg
1: alert
2:crit
3:err
4:warning
5:notice
6:info
7:debug
日志默认分页输出，--no-pager 改为正常的标准输出。
Journalctl –-no-pager
日志管理Journalctl
以json格式（单行）输出
Journalctl –b –u nginx.server –o json
以json格式（多行）输出，可读性更好
Journalctl –b –u nginx.server –o json-pretty
显示日志占据的硬盘空间
Journalctl –disk-usage
指定日志占据的最大空间
Journalctl --vacuum-size=1G
指定日志文件保存多久
Journalctl –vacuum-time=1years
22、完成将多个主机（要求主机名为ip)的nginx日志集中写入到mysql表中
环境：MySQL：10.0.0.13
日志集中管理主机：10.0.0.34
远程主机：10.0.0.4 、10.0.0.14
MySQL：10.0.0.13配置：
[root@Rocky8 ~]# yum install -y mysql-server
[root@Rocky8 ~]# systemctl enable --now mysqld
mysql> create user rsyslog@'10.0.0.%' identified by '123456';
mysql> grant all on Syslog.* to rsyslog@'10.0.0.%';
mysql> source /root/mysql-createDB.sql
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Syslog |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use Syslog
Database changed
mysql> show tables;
+------------------------+
| Tables_in_Syslog |
+------------------------+
| SystemEvents |
| SystemEventsProperties |
+------------------------+
2 rows in set (0.00 sec)
集中日志服务器10.0.0.34配置：
[root@Rocky8 ~]# yum install rsyslog-mysql -y
[root@Rocky8 ~]# scp /usr/share/doc/rsyslog/mysql-createDB.sql 10.0.0.13:
[root@Rocky8 ~]# cat /etc/rsyslog.conf | grep ommysql
module(load="ommysql")
*.info;mail.none;authpriv.none;cron.none :ommysql:10.0.0.13,Syslog,rsyslog,123456
验证：
*************************** 34. row ***************************
ID: 34
CustomerID: NULL
ReceivedAt: 2023-10-30 11:06:05
DeviceReportedTime: 2023-10-30 11:06:05
Facility: 1
Priority: 5
FromHost: 10
Message: I an ROCKY
NTSeverity: NULL
Importance: NULL
EventSource: NULL
EventUser: NULL
EventCategory: NULL
EventID: NULL
EventBinaryData: NULL
MaxAvailable: NULL
CurrUsage: NULL
MinUsage: NULL
MaxUsage: NULL
InfoUnitID: 1
SysLogTag: root[1760]:
EventLogType: NULL
GenericFileName: NULL
SystemID: NULL
*************************** 35. row ***************************
ID: 35
CustomerID: NULL
ReceivedAt: 2023-10-30 11:06:05
DeviceReportedTime: 2023-10-30 11:06:05
Facility: 1
Priority: 5
FromHost: 10
Message: I an ROCKY
NTSeverity: NULL
Importance: NULL
EventSource: NULL
EventUser: NULL
EventCategory: NULL
EventID: NULL
EventBinaryData: NULL
MaxAvailable: NULL
CurrUsage: NULL
MinUsage: NULL
MaxUsage: NULL
InfoUnitID: 1
SysLogTag: root[1760]:
EventLogType: NULL
GenericFileName: NULL
SystemID: NULL
35 rows in set (0.00 sec)
23、尝试使用logrotate服务切割nginx日志，每天切割一次，要求大于不超过3M, 保存90天的日志, 旧日志以时间为后缀，要求压缩。
创建一个nginx应用的转储文件(测试用的)
[root@Rocky8 ~]#vim /etc/logrotate.d/nginx
/var/log/nginx.log {
daily #每天转储
rotate 90 #转储的次数
size 3M #每个文件大小为3M
dateext #旧文件带日期
missingok #日志不存在，不提示错误，继续处理下一个
notifempty #空文件不转储
compress #压缩
delaycompress #延时压缩
# create 644 nginx nginx #转储旧文件以后，创建新的文件并更改对应的属性
# postrotate
# if [ -f /apps/nginx/logs/nginx.pid ]; then #针对nginx特有的设置
# kill -USR1 cat /apps/nginx/logs/nginx.pid #针对nginx特有的设置
# if
# endscript
}
在/var/log/nginx中写入一个4M的文件
[root@Rocky8 ~]#dd if=/dev/zero of=/var/log/nginx.log bs=4M count=1
测试转储功能
[root@Rocky8 ~]#/usr/sbin/logrotate /etc/logrotate.d/nginx
查看转储文件
[root@Rocky8 ~]#ll /var/log/nginx.log-20230816
-rw-r--r-- 1 root root 4194304 Jan 31 11:09 /var/log/nginx.log-20230816
标签：第七,数据库,pg,日志,root,Rocky8,postgres From： https://www.cnblogs.com/LKzzZ/p/17800164.html