Обсуждение: pg_xlog on slaves has grown to 200GB

Greetings,

* Vikas Sharma (shavikas@gmail.com) wrote:
> 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.
>
> 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.

Check your logs.  Seems most likely that something broke archiving,
though it may also be simply that you have an old replication slot
that's holding back the removal of the WAL.

Thanks,

Stephen

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