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

>>-----Original Message-----
>>From: Fan Liu
>>Sent: 2020年3月25日 19:52
>>To: depesz(at)depesz(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"
>>
>>-----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
>>

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