RE: BUG #16317: max_wal_size does not remove WAL files, cause "no space left"

-----Original Message-----
From: depesz(at)depesz(dot)com <depesz(at)depesz(dot)com>
Sent: 2020年3月25日 19:39
To: Fan Liu <fan(dot)liu(at)ericsson(dot)com>; pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #16317: max_wal_size does not remove WAL files, cause "no space left"

On Wed, Mar 25, 2020 at 11:11:11AM +0000, PG Bug reporting form wrote:
> eric-adp-ss7-db-pg-0:/var/lib/postgresql/data/pgdata # du -h -d1
> 3.9G ./pg_wal
> 600K ./global
> …..
> 3.9G .
>
> postgres=# SHOW max_wal_size;
> max_wal_size
> --------------
> 1GB
> (1 row)

Please show us:
show archive_mode;
show archive_command;
select * from pg_stat_replication;
select * from pg_replication_slots;
select pg_current_wal_lsn();

and, from postgres account in shell:
ps uwwxf | grep -C3 archive

Best regards,

depesz
---------------------------------

Hi Depesz,
Here is the log.

ss7-db-pg-1:/ # psql -U postgres
psql (10.10)
Type "help" for help.

postgres=# show archive_mode;
archive_mode
--------------
off
(1 row)

postgres=# show archive_command;
archive_command
-----------------
(disabled)
(1 row)

postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | rep
lay_lag | sync_priority | sync_state
-------+----------+---------+----------------------+-----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+----
--------+---------------+------------
39252 | 16398 | replica | eric-adp-ss7-db-pg-2 | 192.168.187.43 | | 55926 | 2020-03-25 10:37:52.992757+00 | | streaming | 0/51207A8 | 0/51207A8 | 0/51207A8 | 0/51207A8 | | |
| 1 | sync
39253 | 16398 | replica | eric-adp-ss7-db-pg-0 | 192.168.254.251 | | 57518 | 2020-03-25 10:37:52.993196+00 | | streaming | 0/51207A8 | 0/51207A8 | 0/51207A8 | 0/51207A8 | | |
| 0 | async
(2 rows)

postgres=# select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
----------------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
eric_adp_ss7_db_pg_2 | | physical | | | f | t | 39252 | | | 0/51207A8 |
eric_adp_ss7_db_pg_0 | | physical | | | f | t | 39253 | | | 0/51207A8 |
(2 rows)

postgres=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/51207A8
(1 row)

postgres=# ps uwwxf | grep -C3 archive
postgres-# \q
ss7-db-pg-1:/ # ps uwwxf | grep -C3 archive
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
root 52808 0.0 0.0 18572 5108 pts/0 Ss 11:39 0:00 bash
root 54898 0.0 0.0 36504 1752 pts/0 R+ 11:49 0:00 \_ ps uwwxf
root 54899 0.0 0.0 8712 968 pts/0 S+ 11:49 0:00 \_ grep -C3 archive
ss7-db-pg-1:/ #

BRs,
Fan Liu