PostgreSQL 如何配置归档，并自动删除7天前归档的redo(wal) - mtime 时间戳复制，find

digoal 2019-10-17

1851

作者

digoal

日期

2019-10-17

背景

PostgreSQL 如何配置归档，并自动删除7天前归档的redo(wal).

归档配置涉及几个参数如下

```

- Archiving -

是否开启归档

archive_mode = off # enables archiving; off, on, or always

                            # (change requires restart)

归档命令，注意 %p %f %% 格式化的含义。
%p 是被归档的redo文件的路径，
%f 是被归档的redo文档的文件名
%% 是百分号

archive_command = '' # command to use to archive a logfile segment

                            # placeholders: %p = path of file to archive  
                            #               %f = file name only  
                            # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

超时强制归档，例：如果10分钟数据库都没有什么活动，一个redo文件没有写完，就不会归档，
但是我们希望数据库至少10分钟要切换一个日志，则可以使用archive_timeout

archive_timeout = 0 # force a logfile segment switch after this

                            # number of seconds; 0 disables

```

归档配置如下：

假设存储redo归档的目录为/mnt/server/archivedir/

``` vi $PGDATA/arch.sh test ! -f /arch/$1 && cp --preserve=timestamps $2 /arch/$1 ; find /arch/ -type f -mtime +7 -exec rm -f {} \;

chmod 500 $PGDATA/arch.sh ```

wal_level = replica archive_mode = on archive_command = 'arch.sh %f %p'

解读：

```
--preserve=timestamps
拷贝文件时，拷贝文件的时间戳（包括文件的修改时间）

find -mtime +7 找到ARCH 目录中7天前修改的文件，删除
```

为什么拷贝时一定要带上修改时间戳呢，假设产生redo很快，归档较慢或者归档卡死了，延迟了几天才归档，不拷贝旧文件的时间戳，实际上写入的就是拷贝时刻的时间戳，相差会很大。

参考

1、拷贝文件时带上被拷贝文件的修改时间戳

```
[root@pg11-test ~]# stat t
File: ‘t’
Size: 21514 Blocks: 48 IO Block: 4096 regular file
Device: fd01h/64769d Inode: 132678 Links: 1
Access: (0644/-rw-r--r--) Uid: ( 0/ root) Gid: ( 0/ root)
Access: 2019-10-21 22:00:46.002833314 +0800
Modify: 2019-05-23 15:19:58.483476121 +0800
Change: 2019-05-23 15:19:58.483476121 +0800
Birth: -

带修改时间戳拷贝
[root@pg11-test ~]# cp --preserve=timestamps t newt

[root@pg11-test ~]# stat newt
File: ‘newt’
Size: 21514 Blocks: 48 IO Block: 4096 regular file
Device: fd01h/64769d Inode: 132691 Links: 1
Access: (0644/-rw-r--r--) Uid: ( 0/ root) Gid: ( 0/ root)
Access: 2019-10-21 22:01:20.232376490 +0800
Modify: 2019-05-23 15:19:58.483476121 +0800
Change: 2019-10-21 22:01:18.239286635 +0800
Birth: -

不带修改时间戳拷贝
[root@pg11-test ~]# cp t newt1
[root@pg11-test ~]# stat newt1
File: ‘newt1’
Size: 21514 Blocks: 48 IO Block: 4096 regular file
Device: fd01h/64769d Inode: 132692 Links: 1
Access: (0644/-rw-r--r--) Uid: ( 0/ root) Gid: ( 0/ root)
Access: 2019-10-21 22:12:05.022445790 +0800
Modify: 2019-10-21 22:12:03.275367026 +0800
Change: 2019-10-21 22:12:03.275367026 +0800
Birth: -
```

2、按修改时间找文件

[root@pg11-test ~]# find ./t ./newt -atime +140 [root@pg11-test ~]# find ./t ./newt -ctime +140 ./t [root@pg11-test ~]# find ./t ./newt -mtime +140 ./t ./newt

find 按文件的访问时间、修改时间、改变时间

```
man find

   -amin n  
          File was last accessed n minutes ago.  
   -atime n  
          File  was  last  accessed  n*24 hours ago.  When find figures out how many 24-hour periods ago the file was last accessed, any fractional  
          part is ignored, so to match -atime +1, a file has to have been accessed at least two days ago.  
   -cmin n  
          File's status was last changed n minutes ago.  
   -ctime n  
          File's status was last changed n*24 hours ago.  See the comments for -atime to understand how rounding affects the interpretation of file  
          status change times.  
   -mmin n  
          File's data was last modified n minutes ago.  
   -mtime n  
          File's  data was last modified n*24 hours ago.  See the comments for -atime to understand how rounding affects the interpretation of file  
          modification times.

```

这几种时间的差别

```
st_atime
Time when file data was last accessed. Changed by the
following functions: creat(), mknod(), pipe(),
utime(2), and read(2).

 st_mtime  
       Time when data was last modified. Changed by the  fol-  
       lowing  functions:  creat(), mknod(), pipe(), utime(),  
       and write(2).

 st_ctime  
       Time when file status was last changed. Changed by the  
       following   functions:   chmod(),   chown(),  creat(),  
       link(2),  mknod(),  pipe(),  unlink(2),  utime(),  and  
       write().

```

3、man cp
```
-p same as --preserve=mode,ownership,timestamps

   --preserve[=ATTR_LIST]  
          preserve the specified attributes (default: mode,ownership,timestamps), if possible additional attributes: context, links, xattr, all

```

4、

《使用SQL查询数据库日志 - file_fdw , csvlog , program , find - 1》

《PostgreSQL 两阶段(非库级一致性)逻辑备份 - PostgreSQL non-consistent backup script》

《PostgreSQL AWR报告(for 阿里云ApsaraDB PgSQL)》

《PostgreSQL 最佳实践 - 在线逻辑备份与恢复介绍》