linux 数据库 PostgreSQL 常见配置参数及相关命令

一、PostgreSQL常见配置参数

max_wal_size :

两个检查点（checkpoint）之间，WAL可增长的最大大小，即：自动WAL checkpoint允许WAL增长的最大值。

该值缺省是1GB。如果提高该参数值会提升性能，但也是会消耗更多空间、同时会延长崩溃恢复所需要的时间。

注意：这个参数是个软限制，不是硬限制，因此实际WAL可能会超过这个值（如：较大的 wal_keep_segments 设置）。

min_wal_size :

检查点（checkpoint）后用来保留的，用于未来循环使用的WAL文件。可以被用来确保有足够的WAL空间被保留来应付WAL使用的高峰，以供将来的检查点使用。这可以用来确保预留足够的WAL空间处理WAL使用中的峰值，比如当运行大批量工作时。

如果PG空闲时，会逐渐将WAL量减少到 min_wal_size。

该值缺省是80MB。请不要将该值设置的太小。

查看数据库 min_wal_size & max_wal_size 参数配置：

testdb01=> select name, setting, unit, short_desc from pg_settings where name like '%wal_size%';

max_wal_size | 1024 | MB | Sets the WAL size that triggers a checkpoint.

min_wal_size | 80 | MB | Sets the minimum size to shrink the WAL to.

也可以执行:

testdb01=> show max_wal_size;

1GB

testdb01=> show min_wal_size;

80MB

testdb01=>

PostgreSQL开启归档：

step1：修改 postgresql 的配置文件（postgresql.conf）、开启归档

wal_level = replica # minimal, replica, or logical. 根据实际业务设置wal记录日志级别

archive_mode = on # 默认为off关闭，on代表开启归档

archive_command = '' # command to use to archive a logfile segment. 归档操作执行的命令，如果开启归档，必须配置归档命令、否则不会归档

几种归档命令配置方式：

archive_command = 'test ! -f /mnt/server/pg_archive/%f && cp %p /mnt/server/pg_archive/%f' # Unix

archive_command = 'copy "%p" "C:\\server\\pg_archive\\%f"' # Windows

archive_command = 'rsync -a %p postgres@[SOME_OTHER_HOST]:/data/pg_archive/%f' # rsync 增量方式远程归档

archive_command ='scp %p postgres@[SOME_OTHER_HOST]:/data/pg_archive/%f' #

我配置为：

archive_command = 'test -f /var/lib/pgsql/12/data/pg_archive/archive_active && test ! -f /var/lib/pgsql/12/data/pg_archive/%f && cp %p /var/lib/pgsql/12/data/pg_archive/%f'

也可以配置成下面这种，是等效的：

archive_command = 'test ! -f /var/lib/pgsql/12/data/pg_archive/archive_active || (test ! -f /var/lib/pgsql/12/data/pg_archive/%f && cp %p /var/lib/pgsql/12/data/pg_archive/%f)'

另外，如果想要按照每天一个日期目录进行归档，则可以配置为：

archive_command = 'DATE=`date +%Y%m%d`;DIRBASE="/var/lib/pgsql/12/data/pg_archive";DIR="$DIRBASE/$DATE";test -f $DIRBASE/archive_active && (test -d $DIR || mkdir -p $DIR) && test ! -f $DIR/%f && cp %p $DIR/%f'

%p：是指相对路径

%f：是指文件名

更多WAL预写日志配置相关的参数介绍，参考官方文档：

http://postgres.cn/docs/10/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS

step2：创建归档路径

mkdir -p $PGDATA/pg_archive

touch $PGDATA/pg_archive/archive_active #为了方便控制、配合归档命令设置了归档开关，通过该文件是否存在来控制是否执行归档

chown -R postgres:postgres $PGDATA/pg_archive

step3：重启数据库

step4：验证归档是否正常

$ ls -l $PGDATA/pg_archive && echo ">>>>" && ls -l $PGDATA/pg_wal

total 0

-rw-r--r-- 1 postgres postgres 0 Feb 27 11:35 archive_active

>>>>

total 32768

-rw------- 1 postgres postgres 16777216 Mar 3 13:17 000000010000000000000002

-rw------- 1 postgres postgres 16777216 Feb 27 11:05 000000010000000000000003

drwx------ 2 postgres postgres 6 Feb 27 10:31 archive_status

-bash-4.2$

$ pg_controldata

pg_control version number: 1201

Catalog version number: 201909212

Database system identifier: 7204657628260517886

Database cluster state: in production

pg_control last modified: Fri 03 Mar 2023 01:17:05 PM CST

Latest checkpoint location: 0/284FE48

Latest checkpoint's REDO location: 0/284FE10

Latest checkpoint's REDO WAL file: 000000010000000000000002

Latest checkpoint's TimeLineID: 1

$ psql -Upostgres

postgres=# checkpoint;

CHECKPOINT

postgres=# select pg_switch_wal(); -- pg10.0之前版本对应命令为 select pg_switch_xlog();

pg_switch_wal

---------------

0/284FFF8

(1 row)

postgres=# exit

-bash-4.2$ ls -l $PGDATA/pg_archive && echo ">>>>" && ls -l $PGDATA/pg_wal

total 16384

-rw------- 1 postgres postgres 16777216 Mar 7 09:47 000000010000000000000002

-rw-r--r-- 1 postgres postgres 0 Feb 27 11:35 archive_active

>>>>

total 32768

-rw------- 1 postgres postgres 16777216 Mar 7 09:47 000000010000000000000002

-rw------- 1 postgres postgres 16777216 Mar 7 09:47 000000010000000000000003

drwx------ 2 postgres postgres 43 Mar 7 09:47 archive_status

-bash-4.2$

-bash-4.2$ pg_controldata

pg_control version number: 1201

Catalog version number: 201909212

Database system identifier: 7204657628260517886

Database cluster state: in production

pg_control last modified: Tue 07 Mar 2023 09:46:15 AM CST

Latest checkpoint location: 0/284FF30

Latest checkpoint's REDO location: 0/284FEF8

Latest checkpoint's REDO WAL file: 000000010000000000000002

Latest checkpoint's TimeLineID: 1

WAL空间使用情况：

如果日志量大于 max_wal_size，则WAL日志空间尽量保持在 max_wal_size 。因为会触发检查点，不需要的段文件将被移除直到系统回到这个限制以下。

如果日志量小于 max_wal_size，则WAL日志空间至少保持 min_wal_size。可以被用来确保有足够的WAL空间被保留来应付WAL使用的高峰，以供将来的检查点使用。

通常情况下，WAL日志空间大小在 min_wal_size ~ max_wal_size 之间动态评估。该估计基于在以前的检查点周期中使用的WAL文件数的动态平均值。如果实际使用量超过估计值，动态平均数会立即增加。

-bash-4.2$ cd $PGDATA

-bash-4.2$ du -sh pg_wal && ls -lh pg_wal/

1.2G pg_wal

total 1.2G

-rw-------. 1 postgres postgres 16M Feb 6 09:20 000000010000000000000001

-rw-------. 1 postgres postgres 16M Feb 14 19:04 000000010000000000000002

-rw-------. 1 postgres postgres 16M Feb 14 19:04 000000010000000000000003

-rw-------. 1 postgres postgres 16M Feb 14 19:04 000000010000000000000004

-rw-------. 1 postgres postgres 16M Feb 14 19:04 000000010000000000000005

......

-rw-------. 1 postgres postgres 16M Feb 14 19:11 00000001000000000000003E

-rw-------. 1 postgres postgres 16M Feb 14 19:11 00000001000000000000003F

-rw-------. 1 postgres postgres 16M Feb 14 19:11 000000010000000000000040

-rw-------. 1 postgres postgres 16M Feb 14 19:12 000000010000000000000041

-rw-------. 1 postgres postgres 16M Feb 14 19:12 000000010000000000000042

-rw-------. 1 postgres postgres 16M Feb 14 19:12 000000010000000000000043

-rw-------. 1 postgres postgres 16M Feb 14 19:12 000000010000000000000044

-rw-------. 1 postgres postgres 16M Feb 14 19:12 000000010000000000000045

-rw-------. 1 postgres postgres 16M Feb 14 19:12 000000010000000000000046

-rw-------. 1 postgres postgres 16M Feb 14 19:12 000000010000000000000047

drwx------. 2 postgres postgres 6 Dec 6 2021 archive_status

-bash-4.2$

pg_wal大小至少保留 80MB 的文件，也就是 000000010000000000000001~47 所有文件至少保留 80MB

执行checkpoint以后，pg_wal大小可能会降低到 max_wal_size 以内，多次执行checkpoint不一定会降低到 min_wal_size，这还处决于很多其他配置和因素。

关于checkpoint

checkpoint：

A checkpoint is a point in the write-ahead log sequence at which all data files have been updated to reflect the information in the log. All data files will be flushed to disk.

checkpoint是WAL(write-ahead log)日志中的一个位点，在这个点位之前数据库中的所有数据都和WAL日志中反映的信息相同，也就是说该位点之前所有 Shared Buffer 中的脏页均已被刷入到存储磁盘。

checkpoint 是一个名词，同时也是一个动词，执行一个 checkpoint SQL操作，会往 WAL 日志里写 checkpoint 位点。checkpoint SQL命令的官方介绍参考：http://postgres.cn/docs/10/sql-checkpoint.html

数据库什么时机会进行checkpoint操作

1）超级用户手动执行checkpoint命令；

2）checkpoint_timeout 配置参数中指定的间隔到达（默认300秒）；

3）写入WAL的数据量（du -sh $PGDATA/pg_wal）已达到参数 max_wal_size（默认值：1GB），软限制、实际可能会超出；

4）online backup 开始的时候；

5）pg_start_backup 执行时；

6）数据库实例关闭时；

7）CREATE/DROP DATABASE等数据库配置操作时；

执行checkpoint时，数据库主要完成以下几个工作：

1）识别Shared Buffers中所有的脏页；

2）将脏页写入相应的数据文件；

3）确保修改后的文件通过fsync()写入到磁盘。

PostgreSQL 写数据的过程：

以 INSERT INTO test01 VALUES(1); 为例，写数据的流程如下：

step1：将 INSERT 1 这个操作写入 WAL 日志中。 WAL 日志是物理日志，记录的是对某个文件某个块的修改。

step2：修改 Shared Buffer 中该页的信息（如果该页不在 Buffer 中，则从磁盘去取），test01表中写入1。此时如果有表的读取则直接读取Shared Buffer返回数据。

step3：background writer 写磁盘(disk)。background 进程会在某个时刻将 Shared Buffer 中的数据刷到磁盘。但是这并不是立刻发生的，而是一个异步操作。

PostgreSQL数据库checkpoint & 故障恢复：

如果上面step3过程中出现故障、background wirter 写磁盘失败了，那么PostgreSQL重启会进入恢复模式，会基于上次 checkpoint点位和 WAL(Redo) 日志进行重放，从而将数据刷到磁盘。

checkpoint过程：

step1：写入checkpoint start日志，该日志中包括所有的活跃Txn；

step2：写入所有dirty的TreeNode（有数据修改，但是没有写入过硬盘的TreeNode是dirty的）；

step3：从LeafNode节点开始写，先写子节点，再写入父节点，最后写入根节点；

step4：写入根节点以后，写入checkpoint complete日志。Checkpoint写入完成。

checkpoint 操作过程会往 WAL 日志里写 checkpoint 位点。例如：

WAL: | ... | INSERT 1 | INSERT 2 | ... | INSERT 3 | checkpoint |

|

-> 这里（INSERT 1之后）执行一个checkpoint操作，产生一个 redo point（redo重做位点），从这里开始到checkpoint写入时一个完整的checkpoint过程

checkpoint详细流程如下：

step1：写入checkpoint start日志。checkpoint 操作首先记录下 checkpoint 的开始位置（INSERT 2之后），记录为 redo point（重做位点）；

step2：checkpoint 将 Shared Buffer 中的脏数据刷到磁盘里面去，写入所有dirty的TreeNode（有数据修改，但是没有写入过硬盘的TreeNode是dirty的）；

step3：这时候数据库又来了一条 SQL：INSERT 3；

step4：checkpoint 刷脏结束，写checkpoint complete日志。redo point 之前的数据均已被刷到磁盘存储（数据1和2）；

step5：这时候在 WAL 日志里面记录 checkpoint 位点（INSERT 3后），表明 checkpoint 操作结束。checkpoint 位点会记录相关信息，比如 redo point 的值（从哪开始重做）；

step6：将最新的 checkpoint 位点记录在 pg_control 文件中，通过 pg_controldata 可查看控制信息。

从上面的流程可以看出，checkpoint 操作已经能保证将 redo point 位点之前的数据落盘了，那 redo point 之前的所有 WAL 日志都已经没有用了（即使下次故障，这部分数据已经被持久化落盘了，也不需要恢复），就可以请理了。不过有些特殊情况，即使WAL日志已经没有用了，也可能导致不被自动清理，例如创建了流复制插槽但未使用/消费。

故障恢复：

重启时数据库自动进入恢复模式，进行数据恢复。在做数据恢复时：

step1：扫描所有日志，找到最后一个完整的Checkpoint（即：Checkpoint Start日志和Checkpoint Complete日志同时存在）。先找到检查点结束的WAL位置（checkpoint complete位点）；

step2：然后根据这里的结束检查点时写入的WAL信息找到开始的位置（redo重做位点）；

step3：然后从开始的位置（redo重做位点）开始读取WAL并实施 replay恢复数据，至少要恢复到检查点结束（checkpoint complete）的WAL位置才能保证数据的一致性和完整性。

WAL日志清理：

数据库数据目录下pg_wal的WAL文件在开启归档的模式下，会将已归档WAL文件自动清理删除。

如果开启了归档，在pg_wal/archive_status目录下会有一些文件，以ready结尾的，表示可以归档但还没有归档，以done结尾的表示已经归档。

WAL文件的自动清理流程如下：

转储WAL段文件到disk，写满或者使用 pg_switch_wal() 后，会生成000000xxxx.ready文件，调用archive_command 命令且成功执行后，将ready文件更名为.done文件。而数据库会在执行checkpoint后计算出最旧的需保留的WAL文件，比该值更早的WAL文件均会被清理。

注意：有些情况会导致wal日志不会自动删除。例如下面情况：

1，查看 pg_replication_slots 视图，客户端连接接收到数据的 LSN 还在数据库服务端视图的 confirmed_flush_lsn 列中可用。pg_replication_slots 早于此 LSN 的数据才是不再可用、数据库负责回收磁盘空间，否则不会回收磁盘空间。

2，查看 pg_replication_slots 视图，该 restart_lsn 列包含客户端连接接收可能需要的最旧 WAL 的 LSN。如果 confirmed_flush_lsn 定期增长、且 restart_lsn 滞后，则数据库需要回收空间。

3，查看 pg_replication_slots 视图，如果存在 active 为 false，即：select * from pg_replication_slots where active='f'; 则数据库也不会回磁盘收空间，WAL日志也不会删除。

wal日志文件一直未自动清除：

$ ls -l pg_wal

-rw-------. 1 postgres postgres 16777216 Feb 6 09:20 000000010000000000000001

-rw-------. 1 postgres postgres 16777216 Feb 14 19:04 000000010000000000000002

-rw-------. 1 postgres postgres 16777216 Feb 14 19:04 000000010000000000000003

-rw-------. 1 postgres postgres 16777216 Feb 14 19:04 000000010000000000000004

......

-rw-------. 1 postgres postgres 16777216 Feb 16 19:50 0000000100000000000000BD

-rw-------. 1 postgres postgres 16777216 Feb 16 20:03 0000000100000000000000BE

drwx------. 2 postgres postgres 4096 Feb 16 19:52 archive_status

testdb01=> select * from pg_replication_slots;

slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn

--------------------+---------------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------

replication_slot01 | test_decoding | logical | 16437 | testdb01 | f | f | | | 590 | 0/180A540 | 0/180A578

replication_slot02 | pgoutput | logical | 16437 | testdb01 | f | f | | | 594 | 0/180C270 | 0/29AA7B0

(2 rows)

-bash-4.2$ pg_controldata

pg_control version number: 1201

Catalog version number: 201909212

Database system identifier: 7038444130751219893

Database cluster state: in production

pg_control last modified: Thu 16 Feb 2023 07:50:04 PM CST

Latest checkpoint location: 0/BC104648

Latest checkpoint's REDO location: 0/B9CCC548

Latest checkpoint's REDO WAL file: 0000000100000000000000B9

Latest checkpoint's TimeLineID: 1

删除没有使用的 replication slot （active = f）以后，手动执行 checkpoint ，会进行WAL空间回收，自动删除部分WAL日志文件：

testdb01=> select pg_drop_replication_slot('replication_slot02');

testdb01=> select pg_drop_replication_slot('replication_slot01');

testdb01=> \c - postgres

Password for user postgres:

You are now connected to database "testdb01" as user "postgres".

testdb01=#

testdb01=# checkpoint;

执行需要一端时间，过程中可以查看wal日志文件不断再减少：

$ ls -l $PGDATA/pg_wal/ | wc -l

shared_buffers：

shared_buffers 是缓存。在数据库系统中，我们主要关注磁盘IO，而且其大多是随机IO，因此从磁盘的读取比较慢，为了解决这个问题，postgresql将数据缓存在内存中，牺牲内存来换取随机读取的性能。

shared_buffers 参数用来设置数据库服务器将使用的共享内存缓冲区量。默认通常是 12MB，但是如果你的内核设置不支持（在initdb时决定），那么可以会更少，但不能小于 128kB。

不过为了更好的性能，通常会使用明显高于最小值的设置。 如果指定值时没有单位(MB等)，则以块为单位，即BLCKSZ字节，通常为8kB。此参数只能在服务器启动时设置。

如果服务器内存被数据库专用、服务器内存 >= 1GB，合理的shared_buffers初始值是设置为系统内存的25%。shared_buffers并非越大越好，shared_buffers增大，也会造成一些工作负载。超过内存 40% 反而会不好。

shared_buffers更大的设置通常要求对max_wal_size也做相应增加。生产系统可以根据实际业务需求适当调整该参数。

二、PostgreSQL常用命令

WAL日志及LSN查询：

1.查看视图 pg_replication_slots 信息：

testdb01=> select * from pg_replication_slots ;

slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn

--------------------+---------------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------

replication_slot01 | test_decoding | logical | 16437 | testdb01 | f | f | | | 590 | 0/180A540 | 0/180A578

replication_slot02 | pgoutput | logical | 16437 | testdb01 | f | f | | | 594 | 0/180C270 | 0/29AA7B0

(2 rows)

2.pg_controldata 查看控制信息：

$ pg_controldata

pg_control version number: 1201

Catalog version number: 201909212

Database system identifier: 7038444130751219893

Database cluster state: in production

pg_control last modified: Fri 10 Feb 2023 02:29:59 PM CST

Latest checkpoint location: 0/29AA700

Latest checkpoint's REDO location: 0/29AA6C8

Latest checkpoint's REDO WAL file: 000000010000000000000002

Latest checkpoint's TimeLineID: 1

Latest checkpoint's PrevTimeLineID: 1

Latest checkpoint's full_page_writes: on

Latest checkpoint's NextXID: 0:626

Latest checkpoint's NextOID: 35373

......

3.查询数据库LSN等信息：

-- PG10以前的版本

select pg_current_xlog_location(),pg_xlogfile_name(pg_current_xlog_location()),pg_xlogfile_name_offset(pg_current_xlog_location());

其中：

pg_current_xlog_location()：获得当前wal日志写入位置。

pg_xlogfile_name():转换wal日志位置为文件名。

pg_xlogfile_name_offset():返回转换后的wal日志文件名和偏移量。

-- PG10及以后版本

select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());

testdb01=> select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());

pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset

--------------------+--------------------------+-------------------------------------

0/29AA7B0 | 000000010000000000000002 | (000000010000000000000002,10135472)

(1 row)

其中：

pg_current_wal_lsn()： 获得当前wal日志写入位置。

pg_walfile_name()： 转换wal日志位置为文件名。

pg_walfile_name_offset()：返回转换后的wal日志文件名和偏移量。

说明

LSN:0/29AA7B0

0：代表wal文件的第二部分

2：代表wal文件的最后两位02

9AA7B0：代表对应日志文件内的偏移量

4.查看pg_wal目录下的日志文件：

$ ls -l $PGDATA/pg_wal

total 32768

-rw-------. 1 postgres postgres 16777216 Feb 6 09:20 000000010000000000000001

-rw-------. 1 postgres postgres 16777216 Feb 10 14:30 000000010000000000000002

drwx------. 2 postgres postgres 6 Dec 6 2021 archive_status

wal文件由三部分组成每个4字节（8个16进制字符）总共12字节24个16进制字符。第一部分（4字节）代表时间线（TimeLineID）、第二部分代表LSN高32位、第三部分代表LSN低32位（LSN低32位的最高两位代表该日志文件的LSN起始编号）。

000000010000000000000002 日志文件名的格式如下：

00000001：第一部分（4字节）代表数据库运行的时间轴，如果恢复过数据库（主备切换）这个值会增大；

00000000：第二部分、LSN高32位；

00000002：第三部分、LSN低32位。其中最高两位为0代表LSN该日志文件的LSN其实编号为0。

根据时间线 00000001 和 0/29AA7B0 可以推算出日志文件为：000000010000000000000002，对位的文件内的偏移为：0x9AA7B0 = 10135472

5.查询当前的最新LSN和WAL文件：

testdb01=> select * from pg_control_checkpoint();

checkpoint_lsn | redo_lsn | redo_wal_file | timeline_id | prev_timeline_id | full_page_writes | next_xid | next_oid | next_multixact_id | next_multi_offset |

oldest_xid | oldest_xid_dbid | oldest_active_xid | oldest_multi_xid | oldest_multi_dbid | oldest_commit_ts_xid | newest_commit_ts_xid | checkpoint_time

----------------+-----------+--------------------------+-------------+------------------+------------------+----------+----------+-------------------+-------------------+-

-----------+-----------------+-------------------+------------------+-------------------+----------------------+----------------------+------------------------

0/29AA700 | 0/29AA6C8 | 000000010000000000000002 | 1 | 1 | t | 0:626 | 35373 | 1 | 0 |

479 | 1 | 626 | 1 | 1 | 0 | 0 | 2023-02-10 14:29:56+08

(1 row)

testdb01=>

WAL(Write Ahead Log)预写日志

WAL(Write Ahead Log)预写式日志，类似于Oracle的Redo日志。区别是Oracle中Redo是配置固定几个Redo日志文件，然后轮着切换去写入。而PostgreSQL中WAL日志是动态切换的，单个WAL日志文件写满后继续写下一个WAL日志文件，连续不断生成很多WAL日志文件。

单个WAL日志文件的大小默认16MB。适当提高单个WAL日志文件的大小会降低磁盘IO操作、性能有一定提升。

PG11.0以下的版本不支持动态调整、而是在编译postgresql时通过参数"--with-wal-segsize"设置，编译后不能修改。

PG11.0及以后的版本支持通过参数"wal_segment_size"动态调整、数据库启动初始化时生效。另外也可以通过initdb配置选项"--with-wal-segsize"来修改配置。

WAL的核心概念是，对数据文件(表和索引所在的地方)的更改必须在写入了日志文件后这些更改之后才可以写入数据文件，也就是说，描述更改的日志记录被刷新到永久存储之后才可以写数据文件。

使用WAL可以显著减少磁盘写操作的数量，因为只需要将日志文件刷新到磁盘以确保提交事务，而不是事务更改的每个数据文件。日志文件是按顺序写入的，因此同步日志的成本要比刷新数据页的成本低得多。

备注：

1）数据库将脏数据刷到数据文件上，这个动作是随机I/O，性能远比写日志文件的顺序I/O差太多。

2）数据库事务：数据库事务在实现的时候，要保证数据落盘成功才能返回。落盘是指落盘到自己的事务日志文件里就返回成功，而不是直接写入到数据库表文件里。原因还是磁盘读写性能问题，事务只要落盘成功就可以，写到哪里不重要。写到一个日志文件中，就是顺序IO，而写到数据文件就变成随机IO了。

3）更多 WAL内部详情（包括故障恢复过程），可参考官方文档说明：http://postgres.cn/docs/10/wal-internals.html

WAL日志归档

1.手动触发WAL日志归档：

select pg_switch_xlog(); -- pg10.0之前的版本

select pg_switch_wal(); -- pg10.0之后的版本

执行手动日志归档以后，WAL会切换到新的日志文件，这时会将老的WAL日志归档。

2.自动WAL日志归档：

1）WAL日志写满后触发自动归档

WAL每个日志文件大小的默认配置是16MB，WAL日志文件写满后切换WAL日志文件，会触发自动归档。

PG11.0以下的版本不支持动态调整、而是在编译postgresql时通过参数"--with-wal-segsize"设置，编译后不能修改。

PG11.0及以后的版本支持通过参数"wal_segment_size"动态调整、数据库启动初始化时生效。另外也可以通过initdb配置选项"--with-wal-segsize"来修改配置。

2）archive_timeout 时间控制自动归档：

在 postgresql.conf 配置文件中的 archive_timeout 参数控制，如果设置 archive_timeout = 180s，则WAL日志会每 180s 切换一次WAL日志、并同时触发日志归档。

archive_timeout 的默认值是0、即未开启。

注意：不推荐设置 archive_timeout ，如果要设置，那也尽量不要把 archive_timeout 参数设置过小，如果太小会导致你配置的归档存储空间开销膨胀。因为，达到归档时间强制归档的日志，即使WAL日志文件没有写满，也会是默认的16MB（假设 wal_segment_size = 16MB 默认值）。

PostgreSQL中的oid和relfilenode：

PostgreSQL 创建一个普通表（外表和分区表除外）以后，pg_class 中存储它的 relfilenode 值，磁盘上也会创建一个对应的文件（），文件名和表的 relfilenode 值相同，表创建后，oid 和 relfilenode 相同。

查询数据库testdb01的oid：

testdb01=> select oid,datname from pg_database where datname='testdb01';

oid | datname

-------+----------

16437 | testdb01

(1 row)

查询表test01的oid：

testdb01=> select oid,relname,reltype,relfilenode from pg_class where relname like '%test%';

oid | relname | reltype | relfilenode

-------+----------------------------------+---------+-------------

27186 | test05 | 27188 | 27202

27192 | test05_pkey | 0 | 27204

27218 | test01 | 27220 | 27218

27221 | test01_pk | 0 | 27221

(11 rows)

查看库表对应的物理磁盘文件：

testdb01=> quit;

-bash-4.2$

-bash-4.2$ ls -l $PGDATA/base/16437/27218

-rw-------. 1 postgres postgres 8192 Feb 17 10:55 /var/lib/pgsql/12/data/base/16437/27218

-bash-4.2$

精彩文章

 您阅读本篇文章共花了：