Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

Migrating From Oracle to PostgreSQL using ora2pg open source tools