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

>>-----Original Message-----
>>From: Fan Liu 
>>Sent: 2020年3月25日 19:52
>>To: depesz@depesz.com; pgsql-bugs@lists.postgresql.org
>>Subject: RE: BUG #16317: max_wal_size does not remove WAL files, cause "no space left"
>>
>>-----Original Message-----
>>From: depesz@depesz.com <depesz@depesz.com> 
>>Sent: 2020年3月25日 19:39
>>To: Fan Liu <fan.liu@ericsson.com>; pgsql-bugs@lists.postgresql.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
>>

More logs

ss7-db-pg-1:/var/lib/postgresql/data/pgdata # pg_controldata -D .
pg_control version number:            1002
Catalog version number:               201707211
Database system identifier:           6795532438034051129
Database cluster state:               in production
pg_control last modified:             Wed 25 Mar 2020 10:42:49 AM UTC
Latest checkpoint location:           0/5120700
Prior checkpoint location:            0/5120620
Latest checkpoint's REDO location:    0/51206C8
Latest checkpoint's REDO WAL file:    000000E90000000000000005
Latest checkpoint's TimeLineID:       233
Latest checkpoint's PrevTimeLineID:   233
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:665
Latest checkpoint's NextOID:          24596
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        549
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  665
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Wed 25 Mar 2020 10:42:49 AM UTC
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                on
max_connections setting:              100
max_worker_processes setting:         8
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           1
Mock authentication nonce:            bc46d88b034161423b3a4f0ca2e2b42f0d41c9654b3bef9716f850a412d51557

ss7-db-pg-1:/var/lib/postgresql/data/pgdata # pg_archivecleanup -d pg_wal 000000E90000000000000005
pg_archivecleanup: keeping WAL file "pg_wal/000000E90000000000000005" and later

ss7-db-pg-1:/var/lib/postgresql/data/pgdata # du -h pg_wal/
16K     pg_wal/archive_status
3.7G    pg_wal/

BRs,
Fan Liu