How to fix xlog / wal log disk full issue in postgres database ?
if archive is full means you can use old log moving script , it will compress & remove the old archives.
if wal log disk full or postgres DB is down due to wal log disk full means you have to perform below steps .
i given sample issue for your knowledge which am faced in my environment and given solution for those.
Below is our master and slave xlog information
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).
Comments
Post a Comment