Re: WAL-files is not removing authomaticaly

On Thu, 2021-03-11 at 09:43 +0200, Andrew Anderson wrote:
> > Never, ever, remove WAL files manually.
> > A crash might render your database unrecoverable.
> You're right, it's not a good idea.
>
> > You'll have to figure out what is blocking WAL removal.
> This is my favorite dream!
>
> > Are there archive failures reported in "pg_stat_archiver" and the log?
> master=# select * from pg_stat_archiver;
> archived_count | last_archived_wal | last_archived_time | failed_count | last_failed_wal | last_failed_time | stats_reset
> ----------------+--------------------------+-------------------------------+--------------+-----------------+------------------+------------------------------
> 3021 | 000000010000000B000000C9 | 2021-03-11 08:53:56.133796+02 | 0 | | | 2021-01-06 14:33:40.70147+02
> (1 row)
>
> In today's logs there is no any errors:
> $ grep "FATAL\|WARNING" /opt/pglogs/master.log
> $
>
> $ grep "FATAL\|WARNING" /opt/pglogs/slave.log
> $
>
> > You say that replication is working fine, but try
> >
> > SELECT pg_current_wal_lsn();
> >
> > on the primary and compare that with the "restart_lsn" of the replication slot.
>
> It parameters is differ:
> master=# SELECT pg_current_wal_lsn();
> pg_current_wal_lsn
> --------------------
> B/CB099398
> (1 row)
>
> master=# select * from pg_replication_slots;
> slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
> ----------------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
> standby_slot | | physical | | | f | t | 2631806 | | | B/CB0AC068 |
> (1 row)
>
> But when I add new data to the table on master, it apears on slave.
>
> > Look for strange messages in the log file on both servers.
> Looking for strange messages gives nothing, in today's logs there is no any strange messages, just about connects of applications.

All that looks fine.

If that happens again, set "log_min_messages" to "debug2".
Then the log file will contain information abotu the WAL segments that
PostgreSQL is trying to remove. That might give you some insight.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com