当前位置：首页 > news >正文

postgresql数据库|wal日志的开启以及如何管理

news 文章来源：https://blog.csdn.net/alwaysbefine/article/details/129523648 2024/10/28 22:32:31

一，

wal的基本概念

WAL即Write Ahead Log预写式日志,简称wal日志,相当于oracle中的redo日志。只是oracle中redo是固定几个redo日志文件,然后轮着切换去写入。pg中wal日志是动态切换,单个wal日志写满继续写下一个wal日志，连续不断生成wal日志。（可以简单理解为MySQL里面的binlog日志，虽然运行机制是完全不同的，但两者效果基本是一致的）

WAL的主要用途是用于故障恢复，针对数据库的数据insert/delete/update操作都会形成一些列的WAL日志记录，多个WAL日志组成WAL的日志序列，这些日志记录记录了哪些page做了什么修改。如果此时数据库发生故障(主机),哪些未被提交的事务或者需要回滚的事务可以从WAL中进行恢复。

另一个用途是搭建主从数据库以及迁移数据库。

二，

wal的参数

1，

wal_segment_size 参数

单个WAL文件的大小,默认为16MB,参数是wal_segment_size,可以理解为PG把Wal日志存储到N个大小为16M(默认值)的WAL segment file,一般不做更改,Postgresql 11版本之前只能在编译pg时指定,Postgresql 11版本开始支持 initdb（初始化数据库的时候）和 pg_resetwal（一个postgresql的配置工具）修改 WAL 文件大小

附注：pg_resetwal工具的简单使用

重设wal文件大小之前，需要停止数据库，并使用pg数据库的管理用户操作，设定的值只能是2的倍数，例如，16,32,64，128以此类推，单位为M，不得超过1G

因此，下面的修改，18是不被接受的，64可以接受

[root@EULEER pg_wal]# su - pg1 -c"pg_resetwal --wal-segsize=18 -D /usr/local/pgsql/data"
pg_resetwal: error: argument of --wal-segsize must be a power of 2 between 1 and 1024
[root@EULEER pg_wal]# su - pg1 -c"pg_resetwal --wal-segsize=64 -D /usr/local/pgsql/data"
Write-ahead log reset

再次启动数据库的时候，又报这个错了：

意思是min_wal_size的值必须至少是wal_segment_size的两倍，查看postgresql.conf配置文件，发现min_wal_size的默认值是80，因此，修改为128后启动成功。

[root@EULEER pg_wal]# bash ~/start-pgsql.sh 
waiting for server to start....2023-03-16 00:17:01.023 CST [31444] FATAL:  "min_wal_size" must be at least twice "wal_segment_size"
2023-03-16 00:17:01.023 CST [31444] LOG:  database system is shut downstopped waiting
pg_ctl: could not start server

启动数据库成功后，查看PGDATA目录下的pg_wal目录，可以看到wal日志大小变为了64M，原来默认的是16M

[root@EULEER pg_wal]# pwd
/usr/local/pgsql/data/pg_wal
[root@EULEER pg_wal]# ls -alh
total 65M
drwx------  3 pg1 pg1 4.0K Mar 16 00:15 .
drwx------ 19 pg1 pg1 4.0K Mar 16 00:18 ..
-rw-------  1 pg1 pg1  64M Mar 16 00:24 00000002000000000000000A
-rw-------  1 pg1 pg1   33 Mar 15 20:15 00000002.history
drwx------  2 pg1 pg1 4.0K Mar 16 00:14 archive_status

三，

wal文件的查看

oracle的redo日志是没有内置程序查看的，但MySQL里有，postgresql的查看redo日志程序是pg_waldump

登陆psql客户端，执行一下命令，查询现在postgresql在使用哪个wal文件（此命令是函数嵌套）：

如果是开启了备份功能，也就是archive，该文件将会复制到备份目录内，也就是postgresql.conf这个配置文件里的cp命令干的事情（本例是/usr/local/pgsql/backup目录）。


archive_mode = on               # enables archiving; off, on, or always# (change requires restart)
archive_command = ' cp %p /usr/local/pgsql/backup/%f'

postgres=# select pg_walfile_name(pg_current_wal_lsn());pg_walfile_name      
--------------------------00000001000000000000000B
(1 row)

可以看到，确实是有 00000001000000000000000B

[root@EULEER pg_wal]# ls -al
total 65548
drwx------  3 pg1 pg1     4096 Mar 16 04:38 .
drwx------ 19 pg1 pg1     4096 Mar 16 04:23 ..
-rw-------  1 pg1 pg1 16777216 Mar 16 04:38 00000001000000000000000B
-rw-------  1 pg1 pg1 16777216 Mar 16 04:23 00000001000000000000000C
-rw-------  1 pg1 pg1 16777216 Mar 16 04:12 00000001000000000000000D
-rw-------  1 pg1 pg1 16777216 Mar 16 04:36 00000001000000000000000E
drwx------  2 pg1 pg1     4096 Mar 16 04:38 archive_status
[root@EULEER pg_wal]#

查看wal日志文件有多少个（和上面的命令互相印证，是一致的）：

postgres=# select count(*) from pg_ls_waldir();         count 
-------4
(1 row)

[root@EULEER pg_wal]# ls -al
total 65548
drwx------  3 pg1 pg1     4096 Mar 16 04:38 .
drwx------ 19 pg1 pg1     4096 Mar 16 04:23 ..
-rw-------  1 pg1 pg1 16777216 Mar 16 04:38 00000001000000000000000B
-rw-------  1 pg1 pg1 16777216 Mar 16 04:23 00000001000000000000000C
-rw-------  1 pg1 pg1 16777216 Mar 16 04:12 00000001000000000000000D
-rw-------  1 pg1 pg1 16777216 Mar 16 04:36 00000001000000000000000E
drwx------  2 pg1 pg1     4096 Mar 16 04:38 archive_status
[root@EULEER pg_wal]#

随便找一个表，做增删改操作，这里是删除一个表：

postgres=# \dt+List of relationsSchema |   Name   | Type  |  Owner   |    Size    | Description 
--------+----------+-------+----------+------------+-------------public | bonus    | table | postgres | 8192 bytes | public | dept     | table | postgres | 16 kB      | public | emp      | table | postgres | 16 kB      | public | salgrade | table | postgres | 16 kB      | 
(4 rows)
postgres=# drop table bonus;
DROP TABLE

持续刷新wal日志文件，类似于tail -f 命令：

pg_waldump -f 00000002000000000000000C
[root@EULEER data]# pg_waldump pg_wal/00000001000000000000000C 
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/0C000028, prev 0/0B0000D8, desc: CHECKPOINT_SHUTDOWN redo 0/C000028; tli 1; prev tli 1; fpw true; xid 0:518; oid 16420; multi 1; offset 0; oldest xid 479 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; shutdown
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C0000A0, prev 0/0C000028, desc: RUNNING_XACTS nextXid 518 latestCompletedXid 517 oldestRunningXid 518
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C0000D8, prev 0/0C0000A0, desc: RUNNING_XACTS nextXid 518 latestCompletedXid 517 oldestRunningXid 518
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/0C000110, prev 0/0C0000D8, desc: CHECKPOINT_ONLINE redo 0/C0000D8; tli 1; prev tli 1; fpw true; xid 0:518; oid 16420; multi 1; offset 0; oldest xid 479 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 518; online
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C000188, prev 0/0C000110, desc: RUNNING_XACTS nextXid 518 latestCompletedXid 517 oldestRunningXid 518
rmgr: Heap        len (rec/tot):     65/  1141, tx:        518, lsn: 0/0C0001C0, prev 0/0C000188, desc: HOT_UPDATE off 12 xmax 518 flags 0x00 ; new off 15 xmax 0, blkref #0: rel 1663/16386/16395 blk 0 FPW
rmgr: Transaction len (rec/tot):     34/    34, tx:        518, lsn: 0/0C000638, prev 0/0C0001C0, desc: COMMIT 2023-03-16 08:29:11.019821 CST
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C000660, prev 0/0C000638, desc: RUNNING_XACTS nextXid 519 latestCompletedXid 518 oldestRunningXid 519

刷新出来如下内容：

以上表明删除表的事务ID是572，因此，需要使用前一位的事务ID571来进行恢复，恢复前需要停止数据库：

root@EULEER data]# bash ~/stop-pgsql.sh 
waiting for server to shut down....2023-03-16 02:01:28.827 CST [8091] LOG:  received fast shutdown request
2023-03-16 02:01:28.830 CST [8091] LOG:  aborting any active transactions
2023-03-16 02:01:28.831 CST [8395] FATAL:  terminating connection due to administrator command
2023-03-16 02:01:28.832 CST [8200] FATAL:  terminating connection due to administrator command
2023-03-16 02:01:28.847 CST [8091] LOG:  background worker "logical replication launcher" (PID 8099) exited with exit code 1
2023-03-16 02:01:28.847 CST [8093] LOG:  shutting down
.2023-03-16 02:01:30.231 CST [8091] LOG:  database system is shut downdone
server stopped[root@EULEER data]# su - pg1 -c "pg_resetwal -x 571 -D /usr/local/pgsql/data/"
Write-ahead log reset

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/14000138, prev 0/14000100, desc: RUNNING_XACTS nextXid 518 latestCompletedXid 517 oldestRunningXid 518
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/14000170, prev 0/14000138, desc: CHECKPOINT_ONLINE redo 0/14000138; tli 1; prev tli 1; fpw true; xid 0:518; oid 16420; multi 1; offset 0; oldest xid 479 in DB 16386; oldest multi 1 in DB 16386; oldest/newest commit timestamp xid: 0/0; oldest running xid 518; online
rmgr: Heap        len (rec/tot):     59/  1511, tx:        518, lsn: 0/140001E8, prev 0/14000170, desc: DELETE off 7 flags 0x00 KEYS_UPDATED , blkref #0: rel 1664/0/1262 blk 0 FPW
rmgr: Heap        len (rec/tot):     59/   923, tx:        518, lsn: 0/140007D0, prev 0/140001E8, desc: DELETE off 11 flags 0x00 KEYS_UPDATED , blkref #0: rel 1664/0/1214 blk 0 FPW
rmgr: Heap        len (rec/tot):     54/    54, tx:        518, lsn: 0/14000B70, prev 0/140007D0, desc: DELETE off 12 flags 0x00 KEYS_UPDATED , blkref #0: rel 1664/0/1214 blk 0
rmgr: Heap        len (rec/tot):     54/    54, tx:        518, lsn: 0/14000BA8, prev 0/14000B70, desc: DELETE off 13 flags 0x00 KEYS_UPDATED , blkref #0: rel 1664/0/1214 blk 0
rmgr: Heap        len (rec/tot):     54/    54, tx:        518, lsn: 0/14000BE0, prev 0/14000BA8, desc: DELETE off 14 flags 0x00 KEYS_UPDATED , blkref #0: rel 1664/0/1214 blk 0
rmgr: Heap        len (rec/tot):     54/    54, tx:        518, lsn: 0/14000C18, prev 0/14000BE0, desc: DELETE off 10 flags 0x00 KEYS_UPDATED , blkref #0: rel 1664/0/1214 blk 0
rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 0/14000C50, prev 0/14000C18, desc: RUNNING_XACTS nextXid 519 latestCompletedXid 517 oldestRunningXid 518; 1 xacts: 518
rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 0/14000C88, prev 0/14000C50, desc: RUNNING_XACTS nextXid 519 latestCompletedXid 517 oldestRunningXid 518; 1 xacts: 518
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/14000CC0, prev 0/14000C88, desc: CHECKPOINT_ONLINE redo 0/14000C88; tli 1; prev tli 1; fpw true; xid 0:519; oid 16420; multi 1; offset 0; oldest xid 479 in DB 16386; oldest multi 1 in DB 16386; oldest/newest commit timestamp xid: 0/0; oldest running xid 518; online
rmgr: Database    len (rec/tot):     34/    34, tx:        518, lsn: 0/14000D38, prev 0/14000CC0, desc: DROP dir 1663/16386
rmgr: Transaction len (rec/tot):     82/    82, tx:        518, lsn: 0/14000D60, prev 0/14000D38, desc: COMMIT 2023-03-16 08:46:05.921552 CST; inval msgs: catcache 21 snapshot 1214; sync
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/14000DB8, prev 0/14000D60, desc: RUNNING_XACTS nextXid 519 latestCompletedXid 518 oldestRunningXid 519

查询当前事务ID：

postgres=# SELECT CAST(txid_current() AS text);txid_current 
--------------520
(1 row)

利用arclog目录内的归档文件恢复指定事务：

那么现在恢复到删除表前的话，应该是518这个事务了，编辑主配置文件，然后基础备份文件解压替换原来的data文件，重启就可以了。

restore_command = 'cp /usr/local/pgsql/arclog/%f %p'
recovery_target_xid = '518'

以上简单的演示了归档文件的查看和使用，其实可以确定的一点就是，arclog归档文件就是配合pg_basebackup命令恢复用的

四，

arclog文件的管理

[postgres@node1 arclog]$ pwd
/usr/local/pgsql/arclog[postgres@node1 arclog]$ ll
total 1179656
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 16:31 000000010000000100000003
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 16:31 000000010000000100000004
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 16:36 000000010000000100000005
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 16:37 000000010000000100000006
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 16:37 000000010000000100000007
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 17:21 000000010000000100000008
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 17:21 000000010000000100000009
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 17:21 00000001000000010000000A
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 17:21 00000001000000010000000B
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 17:31 00000001000000010000000C
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 18:01 00000001000000010000000D
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 18:06 00000001000000010000000E
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 18:09 00000001000000010000000F
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 18:17 000000010000000100000010
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 18:17 000000010000000100000011
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 18:18 000000010000000100000012
-rwxr-x--- 1 postgres postgres      340 Sep 30 18:18 000000010000000100000012.00000028.backup
-rwxr-x--- 1 postgres postgres 67108864 Sep 30 18:18 000000010000000100000013
-rw-r----- 1 postgres postgres 67108864 Sep 30 18:46 000000020000000100000014
-rw-r----- 1 postgres postgres       34 Sep 30 18:31 00000002.history

如何清理这些文件呢？如果你确定最近短期内不会进行任何恢复工作的话：

[root@node1 pg_wal]# pg_controldata |grep "REDO WAL"
Latest checkpoint's REDO WAL file:    000000020000000100000015[root@node1 pg_wal]# pg_archivecleanup -d /usr/local/pgsql/arclog/ 000000020000000100000015

可以先试运行，在正式删除，参数-n是试运行，-d 是详细输出：

[root@node1 arclog]# pg_archivecleanup  -n  /usr/local/pgsql/arclog/  000000020000000100000019
/usr/local/pgsql/arclog//000000020000000100000015
/usr/local/pgsql/arclog//000000020000000100000016
/usr/local/pgsql/arclog//000000020000000100000017
/usr/local/pgsql/arclog//000000020000000100000018
[root@node1 arclog]# pg_archivecleanup  -d  /usr/local/pgsql/arclog/  000000020000000100000019
pg_archivecleanup: keeping WAL file "/usr/local/pgsql/arclog//000000020000000100000019" and later
pg_archivecleanup: removing file "/usr/local/pgsql/arclog//000000020000000100000015"
pg_archivecleanup: removing file "/usr/local/pgsql/arclog//000000020000000100000016"
pg_archivecleanup: removing file "/usr/local/pgsql/arclog//000000020000000100000017"
pg_archivecleanup: removing file "/usr/local/pgsql/arclog//000000020000000100000018"

五，

arclog的拷贝命令

将归档文件复制到指定目录：
archive_command = 'cp %p /path/to/archive/%f'
1.
使用rsync将归档文件复制到远程服务器：
archive_command = 'rsync -av %p user@remote:/path/to/archive/%f'
2.
将归档文件压缩后复制到指定目录：
archive_command = 'gzip -c %p > /path/to/archive/%f.gz'
3.
将归档文件发送到AWS S3存储桶：
archive_command = 'aws s3 cp %p s3://my-bucket/archive/%f'

这些主要是针对磁盘空间比较少的情况，通常使用压缩或者rsync 推送到远程服务器上，比如，在本地压缩：

archive_mode = on               # enables archiving; off, on, or always# (change requires restart)
archive_command = 'test ! -f /usr/local/pgsql/arclog/%f && gzip -c  %p >/usr/local/pgsql/arclog/%f.gz' 
[root@node1 arclog]# ls -alh
total 65M
drwxr-x---  2 postgres postgres 161 Sep 30 21:57 .
drwxr-x---. 8 postgres postgres  82 Sep 30 13:28 ..
-rwxr-x---  1 postgres postgres 340 Sep 30 18:18 000000010000000100000012.00000028.backup
-rw-r-----  1 postgres postgres 64M Sep 30 21:56 000000020000000100000019
-rw-r-----  1 postgres postgres 64K Sep 30 21:57 00000002000000010000001A.gz
-rw-r-----  1 postgres postgres  34 Sep 30 18:31 00000002.history

可以看到，很明显的压缩后，归档文件小了很多很多

未完待续！！！

一，

wal的基本概念

二，

wal的参数

三，

wal文件的查看

四，

arclog文件的管理

五，

arclog的拷贝命令

相关文章：