PostgreSQL修改归档模式

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:PostgreSQL修改归档模式

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

pg版本

postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)

归档配置参数

--pgdata中的postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /pg_archivedir/%f && cp %p /pg_archivedir/%f'
--重启pg systemctl restart postgresql-10.service
postgres=# show wal_level;
 wal_level
-----------
 replica
(1 row)
postgres=# show archive_mode;
 archive_mode
--------------
 on
(1 row)
postgres=# show archive_command;
                    archive_command
--------------------------------------------------------
 test ! -f /pg_archivedir/%f && cp %p /pg_archivedir/%f
(1 row)

测试归档效果

[root@localhost pg_wal]# ps -ef|grep "postgres: archiver process"
postgres  35300  35293  0 08:43 ?        00:00:00 postgres: archiver process   last was 000000010000000000000004
root      37504  20853  0 09:55 pts/1    00:00:00 grep --color=auto postgres: archiver process
[root@localhost pg_wal]# pwd
/opt/PostgreSQL/10/data/pg_wal
[root@localhost pg_wal]# ls -ltr
total 49156
-rw------- 1 postgres postgres      309 Aug  5 06:05 000000010000000000000002.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug  5 08:43 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Aug  5 08:50 000000010000000000000004
drwx------ 2 postgres postgres       94 Aug  5 08:50 archive_status
-rw------- 1 postgres postgres 16777216 Aug  5 08:53 000000010000000000000005
[root@localhost pg_wal]# ls -l /pg_archivedir/
total 65540
-rw------- 1 postgres postgres 16777216 Aug  5 08:37 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Aug  5 08:37 000000010000000000000002
-rw------- 1 postgres postgres      309 Aug  5 08:37 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Aug  5 08:43 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Aug  5 08:50 000000010000000000000004
--切换日志
postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/5000158
(1 row)
[root@localhost pg_wal]# ps -ef|grep "postgres: archiver process"
postgres  35300  35293  0 08:43 ?        00:00:00 postgres: archiver process   last was 000000010000000000000005
root      37531  20853  0 09:56 pts/1    00:00:00 grep --color=auto postgres: archiver process
[root@localhost pg_wal]# ls -ltr
total 49156
-rw------- 1 postgres postgres      309 Aug  5 06:05 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Aug  5 08:50 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Aug  5 09:55 000000010000000000000005
drwx------ 2 postgres postgres      130 Aug  5 09:55 archive_status
-rw------- 1 postgres postgres 16777216 Aug  5 09:55 000000010000000000000006
[root@localhost pg_wal]# ls -l /pg_archivedir/
total 81924
-rw------- 1 postgres postgres 16777216 Aug  5 08:37 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Aug  5 08:37 000000010000000000000002
-rw------- 1 postgres postgres      309 Aug  5 08:37 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Aug  5 08:43 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Aug  5 08:50 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Aug  5 09:55 000000010000000000000005