How to DELETE current XLOG / WAL LOG in postgresql database ?
Below are ours server , xlog & archive details
PRIMARY / MASTER:
================
postgres@tutorialdba_1:> ps -ef|grep postgres
postgres 4436 1 0 Jun25 ? 00:05:04 /opt/10.6/bin/postgres -D /data
postgres 4437 4436 0 Jun25 ? 00:00:00 postgres: logger process
postgres 4439 4436 0 Jun25 ? 00:10:19 postgres: checkpointer process
postgres 4440 4436 0 Jun25 ? 00:00:40 postgres: writer process
postgres 4441 4436 0 Jun25 ? 00:04:55 postgres: wal writer process
postgres 4442 4436 0 Jun25 ? 00:01:31 postgres: autovacuum launcher process
postgres 4443 4436 0 Jun25 ? 00:00:27 postgres: archiver process last was 00000001000000450000005E
postgres 4444 4436 0 Jun25 ? 00:06:55 postgres: stats collector process
postgres 4445 4436 0 Jun25 ? 00:00:02 postgres: bgworker: logical replication launcher
postgres 4500 4436 0 Jun25 ? 00:05:07 postgres: wal sender process repmgr 53.99.98.119(44112) streaming 45/5FF3DB10
XLOGS :
postgres@tutorialdba_1:/xlog>
-rw------- 1 postgres postgres 16777216 Jul 22 04:02 000000010000004500000096
-rw------- 1 postgres postgres 16777216 Jul 22 04:01 000000010000004500000097
-rw------- 1 postgres postgres 16777216 Jul 22 04:01 000000010000004500000098
-rw------- 1 postgres postgres 16777216 Jul 22 04:01 000000010000004500000099
-rw------- 1 postgres postgres 16777216 Jul 22 04:01 00000001000000450000009A
-rw------- 1 postgres postgres 16777216 Jul 22 04:01 00000001000000450000009B
drwx------ 2 postgres postgres 36864 Jul 22 05:01 archive_status
XLOG STATUS :
postgres@tutorialdba_1:/xlog/archive_status> ls -l
total 0
ARCHIVE LOGS:
postgres@tutorialdba_1:/archive_log>
-rw------- 1 postgres postgres 16777216 Jul 22 04:01 000000010000004500000059
-rw------- 1 postgres postgres 16777216 Jul 22 04:02 00000001000000450000005A
-rw------- 1 postgres postgres 16777216 Jul 22 04:02 00000001000000450000005B
-rw------- 1 postgres postgres 16777216 Jul 22 04:02 00000001000000450000005C
-rw------- 1 postgres postgres 16777216 Jul 22 04:02 00000001000000450000005D
-rw------- 1 postgres postgres 16777216 Jul 22 04:02 00000001000000450000005E
drwx------ 2 postgres postgres 20480 Jun 14 13:55 archive_status
STANDBY / SLAVE :
=================
postgres@tutorialdba_2:/archive_log> ps -ef|grep postgres
postgres 4488 1 0 Jun25 ? 00:00:57 /opt/10.6/bin/postgres -D /data
postgres 4489 4488 0 Jun25 ? 00:00:00 postgres: logger process
postgres 4490 4488 0 Jun25 ? 00:16:08 postgres: startup process recovering 00000001000000450000005F
postgres 4493 4488 0 Jun25 ? 00:10:07 postgres: checkpointer process
postgres 4494 4488 0 Jun25 ? 00:00:33 postgres: writer process
postgres 4495 4488 0 Jun25 ? 00:01:08 postgres: stats collector process
postgres 4496 4488 0 Jun25 ? 00:44:00 postgres: wal receiver process streaming 45/5FF3DBF0
XLOGS :
postgres@tutorialdba_2:/xlog>
-rw------- 1 postgres postgres 16777216 Jul 22 04:01 000000010000004500000059
-rw------- 1 postgres postgres 16777216 Jul 22 04:02 00000001000000450000005A
-rw------- 1 postgres postgres 16777216 Jul 22 04:02 00000001000000450000005B
-rw------- 1 postgres postgres 16777216 Jul 22 04:02 00000001000000450000005C
-rw------- 1 postgres postgres 16777216 Jul 22 04:02 00000001000000450000005D
-rw------- 1 postgres postgres 16777216 Jul 22 20:39 00000001000000450000005E
-rw------- 1 postgres postgres 16777216 Jul 22 12:36 00000001000000450000006F
drwx------ 2 postgres postgres 970752 Jul 22 04:02 archive_status
XLOG STATUS :
postgres@tutorialdba_2:/xlog/archive_status>
-rw------- 1 postgres postgres 0 Jul 22 04:01 000000010000004500000057.done
-rw------- 1 postgres postgres 0 Jul 22 04:01 000000010000004500000058.done
-rw------- 1 postgres postgres 0 Jul 22 04:01 000000010000004500000059.done
-rw------- 1 postgres postgres 0 Jul 22 04:02 00000001000000450000005A.done
-rw------- 1 postgres postgres 0 Jul 22 04:02 00000001000000450000005B.done
-rw------- 1 postgres postgres 0 Jul 22 04:02 00000001000000450000005C.done
-rw------- 1 postgres postgres 0 Jul 22 04:02 00000001000000450000005D.done
-rw------- 1 postgres postgres 0 Jul 22 04:02 00000001000000450000005E.done
Archive log location :
postgres@tutorialdba_2:/archive_log> ls -l
total 0
Solution :
Slave win’t be archive if even archive enabled as well.
Check the status of xlog then delete the .done files before that check the master xlog status, walsender and last archived log for your confirmation.
so finally you can delete the below xlog untill (000000010000004500000059 – 00000001000000450000005E).