Re: pg_xlog on slaves has grown to 200GB - Mailing list pgsql-general

From Ian Barwick
Subject Re: pg_xlog on slaves has grown to 200GB
Date
Msg-id c1f64f04-7620-0980-28ea-338e7c4f3615@2ndquadrant.com
Whole thread Raw
In response to pg_xlog on slaves has grown to 200GB  (Vikas Sharma <shavikas@gmail.com>)
List pgsql-general
On 8/20/19 11:17 PM, Vikas Sharma wrote:> Hello,
 >  We are using postgresql 9.5 with repmgr 3.3.2 in streaming replication setup
 > with 1 master and 2 slaves. I have noticed that the pg_xlog on slaves has
 > grown to 200GB and is still growing.
 >
 > Please advise why pg_xlog is growing and not pruning itself, is there any
 > parameter I need to setup to accomplish this?  or repmgr will control it
 > itself.

This is not something which repmgr is designed to do.

Current repmgr versions do however provide options to check node status
for potential issues, e.g.:

     $ repmgr node check --slots
     CRITICAL (1 of 1 physical replication slots are inactive)

     $ repmgr node check --archive-ready
     WARNING (63 pending archive ready files (threshold: 16))

Note you are using version 3.3.2, which was released over two years ago
and which is no longer supported; I strongly recommend upgrading to
the latest version (4.4).

 > I am not 100% sure but feel it pg_xlog never used to grow this much or was
 > clearing itself, I can see there are WALs since May 2019 but not before
 > that. I want to understand why the WALs started accumulating since then. we
 > have this setup since more than a year.

As Stephen Frost mentioned his response, the most likely issues are:

   - an inactive replication slot
     (check with "SELECT * FROM pg_replication_slots WHERE active IS FALSE")

   - some sort of WAL archiving failure
     ("SELECT * FROM pg_stat_archiver" will show if issues are being encountered
      and the PostgreSQL log file will show specifics of any issues)


Regards

Ian Barwick




-- 
  Ian Barwick                   https://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services



pgsql-general by date:

Previous
From: Andreas Joseph Krogh
Date:
Subject: Sv: Two Postgres master process are showing - one is on and off
Next
From: Laurenz Albe
Date:
Subject: Re: Two Postgres master process are showing - one is on and off