RE: BUG #16317: max_wal_size does not remove WAL files, cause "nospace left" - Mailing list pgsql-bugs
| From | Fan Liu |
|---|---|
| Subject | RE: BUG #16317: max_wal_size does not remove WAL files, cause "nospace left" |
| Date | |
| Msg-id | HE1PR0701MB2569B5A6A1F2D084B201F9C49ECE0@HE1PR0701MB2569.eurprd07.prod.outlook.com Whole thread Raw |
| In response to | RE: BUG #16317: max_wal_size does not remove WAL files, cause "nospace left" (Fan Liu <fan.liu@ericsson.com>) |
| Responses |
Re: BUG #16317: max_wal_size does not remove WAL files, cause "nospace left"
|
| List | pgsql-bugs |
>>-----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
pgsql-bugs by date: