PostgreSQL开启归档

postgresql数据库打开归档，启用归档模式

一、查看当前归档模式

postgres=# show archive_mode;
 archive_mode 
--------------
 off
(1 row)

二、创建存放归档的目录

postgres=# \! mkdir $PGDATA/pg_archive 

postgres=# show data_directory;
    data_directory     
-----------------------
 /usr/local/pgsql/data
 
postgres=# \! ls -l /usr/local/pgsql/data/pg_archive 
total 0

三、修改postgresql.conf配置文件

postgres-# \! vi $PGDATA/postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'date_dir=$(date +%F);archive_dir="$PGDATA/pg_archive/${date_dir}";(test -d ${archive_dir} || mkdir -p ${archive_dir}) && cp %p ${archive_dir}/%f'

postgres=# \! tail -3 $PGDATA/postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'date_dir=$(date +%F);archive_dir="$PGDATA/pg_archive/${date_dir}";(test -d ${archive_dir} || mkdir -p ${archive_dir}) && cp %p ${archive_dir}/%f'


#命令详解
wal_level: 设置日志级别，minimal, replica, or logical
archive_mode: 开启归档模式
archive_command: 配置归档命令, %p代表原始的WAL文件，%f代表WAL文件的文件名。

四、重启postgresql

[postgres@server ~]$ pg_ctl restart -l postgres.log 

五、检查归档是否成功开启

postgres=# show archive_mode;
 archive_mode 
--------------
 on
(1 row)

六、切换日志

postgres=# \! ls -l /usr/local/pgsql/data/pg_wal
total 49152
-rw-------  1 postgres postgres 16777216 Jul 25 20:49 000000010000000000000005
-rw-------. 1 postgres postgres 16777216 Jul 17 14:48 000000010000000000000006
-rw-------  1 postgres postgres 16777216 Jul 25 15:08 000000010000000000000007
drwx------. 2 postgres postgres        6 Mar  4  2021 archive_status

postgres=# select * from pg_ls_waldir();
           name           |   size   |      modification      
--------------------------+----------+------------------------
 000000010000000000000005 | 16777216 | 2024-07-25 20:49:19+08
 000000010000000000000006 | 16777216 | 2024-07-17 14:48:30+08
 000000010000000000000007 | 16777216 | 2024-07-25 15:08:52+08
(3 rows)

postgres=# \! ls -l $PGDATA/pg_archive
total 0

postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/50002A8
(1 row)

postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/6000078
(1 row)

postgres=# select * from  pg_ls_waldir() order by modification desc;
           name           |   size   |      modification      
--------------------------+----------+------------------------
 000000010000000000000007 | 16777216 | 2024-07-25 20:54:10+08
 000000010000000000000006 | 16777216 | 2024-07-25 20:51:22+08
 000000010000000000000005 | 16777216 | 2024-07-25 20:50:41+08
(3 rows)

七、查看归档日志

postgres=# \! ls -l $PGDATA/pg_archive
total 0
drwx------ 2 postgres postgres 70 Jul 25 20:51 2024-07-25

postgres=# \! ls -l $PGDATA/pg_archive/2024-07-25
total 32768
-rw------- 1 postgres postgres 16777216 Jul 25 20:50 000000010000000000000005
-rw------- 1 postgres postgres 16777216 Jul 25 20:51 000000010000000000000006

postgres=# \! ls -l /usr/local/pgsql/data/pg_wal
total 49152
-rw-------  1 postgres postgres 16777216 Jul 25 20:50 000000010000000000000005
-rw-------. 1 postgres postgres 16777216 Jul 25 20:51 000000010000000000000006
-rw-------  1 postgres postgres 16777216 Jul 25 20:54 000000010000000000000007
drwx------. 2 postgres postgres       80 Jul 25 20:51 archive_status
postgres=# select * from  pg_ls_waldir() order by modification desc;
           name           |   size   |      modification      
--------------------------+----------+------------------------
 000000010000000000000007 | 16777216 | 2024-07-25 20:54:10+08
 000000010000000000000006 | 16777216 | 2024-07-25 20:51:22+08
 000000010000000000000005 | 16777216 | 2024-07-25 20:50:41+08
(3 rows)

八、清理归档日志

[postgres@server ~]$ ls -l $PGDATA/pg_archive/2024-07-25
total 49152
-rw------- 1 postgres postgres 16777216 Jul 25 20:50 000000010000000000000005
-rw------- 1 postgres postgres 16777216 Jul 25 20:51 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Jul 25 22:00 000000010000000000000007

#清理比000000010000000000000007归档日志更早生成的归档日志
[postgres@server ~]$ pg_archivecleanup /usr/local/pgsql/data/pg_archive/2024-07-25 000000010000000000000007

[postgres@server ~]$ ls -l $PGDATA/pg_archive/2024-07-25
total 16384
-rw------- 1 postgres postgres 16777216 Jul 25 22:00 000000010000000000000007