Обсуждение: pg_wal keeps growing

On Wed, Feb 3, 2021 at 4:53 PM Yambu <hyambu@gmail.com> wrote:
>
> Hello
>
> My pg_wal directory keeps growing in size. Im using Pg v10
>
> Currently its at 53GB in size.
>
> Below are my settings
>
>  select name,setting,unit from pg_settings where name ilike '%wal%';
>              name             |  setting  | unit
> ------------------------------+-----------+------
>  max_wal_senders              | 10        |
>  max_wal_size                 | 1024      | MB
>  min_wal_size                 | 80        | MB
>  wal_block_size               | 8192      |
>  wal_buffers                  | 2048      | 8kB
>  wal_compression              | off       |
>  wal_consistency_checking     |           |
>  wal_keep_segments            | 0         |
>  wal_level                    | replica   |
>  wal_log_hints                | off       |
>  wal_receiver_status_interval | 10        | s
>  wal_receiver_timeout         | 60000     | ms
>  wal_retrieve_retry_interval  | 5000      | ms
>  wal_segment_size             | 2048      | 8kB
>  wal_sender_timeout           | 60000     | ms
>  wal_sync_method              | fdatasync |
>  wal_writer_delay             | 200       | ms
>  wal_writer_flush_after       | 128       | 8kB


My guess is you have one or more replication slots blocking it. Check
with SELECT * FROM pg_replication_slots, and drop anything that's not
in use (or fix the standby that's falling behind if there are only the
ones that should be in use).


-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

Are you sure you don’t have a replication slot with no replication consumer connected to it?

Replication slots cause WALs to accumulate until someone connects to the slot and downloads them.

—
Sent from my iPad

> On Feb 3, 2021, at 10:53 AM, Yambu <hyambu@gmail.com> wrote:
>
> 
> Hello
>
> My pg_wal directory keeps growing in size. Im using Pg v10
>
> Currently its at 53GB in size.
>
> Below are my settings
>
>  select name,setting,unit from pg_settings where name ilike '%wal%';
>              name             |  setting  | unit
> ------------------------------+-----------+------
>  max_wal_senders              | 10        |
>  max_wal_size                 | 1024      | MB
>  min_wal_size                 | 80        | MB
>  wal_block_size               | 8192      |
>  wal_buffers                  | 2048      | 8kB
>  wal_compression              | off       |
>  wal_consistency_checking     |           |
>  wal_keep_segments            | 0         |
>  wal_level                    | replica   |
>  wal_log_hints                | off       |
>  wal_receiver_status_interval | 10        | s
>  wal_receiver_timeout         | 60000     | ms
>  wal_retrieve_retry_interval  | 5000      | ms
>  wal_segment_size             | 2048      | 8kB
>  wal_sender_timeout           | 60000     | ms
>  wal_sync_method              | fdatasync |
>  wal_writer_delay             | 200       | ms
>  wal_writer_flush_after       | 128       | 8kB
> (18 rows)

On 2021-Feb-03, Yambu wrote:

> There were some unused replication slots. Can i now delete the old files in
> pg_wal, or they will clear on their own?

Never delete anything from pg_wal.  Let the system handle that.  They
should start to clear in a checkpoint or two.  (You may want to execute
a manual checkpoint to speed up the process.)

-- 
Álvaro Herrera       Valdivia, Chile