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