Re: pg_xlog on slaves has grown to 200GB

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