on master
postgres=# create table tbl_checkpoint (chkpoint text,chk_time timestamp without time zone default now());
CREATE TABLE
postgres=# begin;
BEGIN
postgres=# insert into tbl_checkpoint (chkpoint,chk_time) select pg_xlogfile_name(pg_current_xlog_location()),now();
INSERT 0 1
postgres=# checkpoint;
CHECKPOINT
postgres=# commit;
COMMIT
postgres=# select * from tbl_checkpoint ;
chkpoint | chk_time
--------------------------+----------------------------
000000010000000400000038 | 2011-01-04 16:40:05.821066
000000010000000400000038 | 2011-01-04 16:41:30.54769
000000010000000400000038 | 2011-01-04 16:52:55.549503
000000010000000400000039 | 2011-01-04 16:54:29.153623
[root@db-172-16-3-39 pg_arch]# ll
total 65616
-rw------- 1 postgres postgres 254 Dec 30 15:17 000000010000000000000004.00000020.backup
-rw------- 1 postgres postgres 254 Jan 4 14:37 00000001000000020000003E.00000020.backup
-rw------- 1 postgres postgres 67108864 Jan 4 16:54 000000010000000400000038
-rw------- 1 postgres postgres 56 Dec 30 15:35 00000002.history
on slave
postgres=# select chkpoint from tbl_checkpoint where chk_time=(select max(chk_time) from tbl_checkpoint);
chkpoint
--------------------------
000000010000000400000039
(1 row)
postgres@db-172-16-3-39-> pg_archivecleanup $PGARCHIVE 000000010000000400000039
postgres@db-172-16-3-39-> cd $PGARCHIVE
[root@db-172-16-3-39 pg_arch]# ll
total 12
-rw------- 1 postgres postgres 254 Dec 30 15:17 000000010000000000000004.00000020.backup
-rw------- 1 postgres postgres 254 Jan 4 14:37 00000001000000020000003E.00000020.backup
-rw------- 1 postgres postgres 56 Dec 30 15:35 00000002.history
缩写:
pg_archivecleanup $PGARCHIVE `psql -h 127.0.0.1 postgres postgres -q -t -A -c "select chkpoint from tbl_checkpoint where chk_time=(select max(chk_time) from tbl_checkpoint)"`
on slave验证
重启slave,
postgres: startup process waiting for 000000010000000400000039
可以登录postgresql,验证正常.
postgres=# create table tbl_checkpoint (chkpoint text,chk_time timestamp without time zone default now());
CREATE TABLE
postgres=# begin;
BEGIN
postgres=# insert into tbl_checkpoint (chkpoint,chk_time) select pg_xlogfile_name(pg_current_xlog_location()),now();
INSERT 0 1
postgres=# checkpoint;
CHECKPOINT
postgres=# commit;
COMMIT
postgres=# select * from tbl_checkpoint ;
chkpoint | chk_time
--------------------------+----------------------------
000000010000000400000038 | 2011-01-04 16:40:05.821066
000000010000000400000038 | 2011-01-04 16:41:30.54769
000000010000000400000038 | 2011-01-04 16:52:55.549503
000000010000000400000039 | 2011-01-04 16:54:29.153623
[root@db-172-16-3-39 pg_arch]# ll
total 65616
-rw------- 1 postgres postgres 254 Dec 30 15:17 000000010000000000000004.00000020.backup
-rw------- 1 postgres postgres 254 Jan 4 14:37 00000001000000020000003E.00000020.backup
-rw------- 1 postgres postgres 67108864 Jan 4 16:54 000000010000000400000038
-rw------- 1 postgres postgres 56 Dec 30 15:35 00000002.history
on slave
postgres=# select chkpoint from tbl_checkpoint where chk_time=(select max(chk_time) from tbl_checkpoint);
chkpoint
--------------------------
000000010000000400000039
(1 row)
postgres@db-172-16-3-39-> pg_archivecleanup $PGARCHIVE 000000010000000400000039
postgres@db-172-16-3-39-> cd $PGARCHIVE
[root@db-172-16-3-39 pg_arch]# ll
total 12
-rw------- 1 postgres postgres 254 Dec 30 15:17 000000010000000000000004.00000020.backup
-rw------- 1 postgres postgres 254 Jan 4 14:37 00000001000000020000003E.00000020.backup
-rw------- 1 postgres postgres 56 Dec 30 15:35 00000002.history
缩写:
pg_archivecleanup $PGARCHIVE `psql -h 127.0.0.1 postgres postgres -q -t -A -c "select chkpoint from tbl_checkpoint where chk_time=(select max(chk_time) from tbl_checkpoint)"`
on slave验证
重启slave,
postgres: startup process waiting for 000000010000000400000039
可以登录postgresql,验证正常.
