Patroni: WAL Files are not deleted after 20 GB pg_restore on primary node

PostgreSQL
1

Hello community,

I recently faced an issue after restoring a single table in our database and I’m hoping for some guidance.

Context:

  • Environment: Using PostgreSQL 16 with Percona Patroni 3.3.0 cluster for replication.
  • Task: Restored a single table (~20 GB) using pg_restore.
  • Duration: The restore process took around 8 hours.

Issue: Post-restore, I noticed that the WAL (Write-Ahead Logging) files are not being deleted from disk. Upon further investigation, I found that the percona_cluster_1 replication slot is several bytes behind and marked as inactive.

  • WAL files continue to accumulate, consuming significant disk space.
  • percona_cluster_1 replication slot shows as inactive and is lagging by a few bytes.
  • No apparent replication activity to clear or advance the slot.

Configuration:

loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  parameters:
    checkpoint_completion_target: 0.9
    default_statistics_target: 100
    effective_cache_size: 12GB
    effective_io_concurrency: 200
    hot_standby: 'on'
    huge_pages: false
    logging_collector: 'on'
    maintenance_work_mem: 4GB
    max_connections: 300
    max_parallel_maintenance_workers: 4
    max_parallel_workers: 8
    max_parallel_workers_per_gather: 4
    max_replication_slots: 10
    max_wal_senders: 5
    max_wal_size: 4GB
    max_worker_processes: 8
    min_wal_size: 1GB
    random_page_cost: 1.1
    shared_buffers: 4GB
    wal_buffers: 16MB
    wal_keep_segments: 10
    wal_level: replica
    wal_log_hints: 'on'
    work_mem: 16MB
  use_pg_rewind: true
  use_slots: true
retry_timeout: 10
slots:
  percona_cluster_1:
    type: physical
ttl: 30

What I did:

  1. Verified that the restore process completed successfully.
  2. Checked the replication status and slot activity.
  3. Attempted to manually advance the replication slot without success.

Questions:

  1. What could be causing the WAL files to not be deleted after the restore?
  2. How can I address the inactive replication slot that is lagging?
  3. Are there any recommended steps or best practices to ensure WAL files are managed effectively post-restore?
  4. Is this normal?
  5. How can I verify that all replicas have the correct data?

I would appreciate any insights or suggestions from the community. Thank you in advance for your help!

2

Hello vvic,
Welcome to Percona Community Forum!

There are several things required before WAL file can be removed.

  1. WAL files required during crash recovery cannot be archived. So if you have some very long transaction, it will prevent your WALs from being removed, because entire transaction will have to be rolled back, so all this information will have to be read from WALs.
  2. Checkpoint has to be completed on a WAL file before it can be removed.
  3. It have to be archived before it can be removed, so if your archive_command is not working properly it may cause WAL files to pile up.
  4. Replication slots with “wal_status” as reserved, also prevent WALs from being removed. If a slot is inactive, and you do not plan to use it for anything currently, it may be smart to drop the replication slot, and create new one when you will need it again.
  5. wal_keep_segments / wal_keep_size / min_wal_size parameters makes sure a certain number of WAL files is always present in pg_wal directory.

To answer your questions:

  1. See above reply, regarding required steps before WAL can be removed.
  2. To make slot active again, you would need to connect some replica, or application that uses the slot, to consume changes and advance it further. If you cannot do that, just drop the slot and recreate it when you will need it again.
select pg_drop_replication_slot('name');
  1. You never manually remove any WAL files. Make sure steps from above are done, so WALs are archived, there was a checkpoint run, there are no inactive replication slots, etc.
  2. Yes, in many cases it is “normal”, if some of above conditions for WAL removal are not met.
  3. Query pg_stat_replication view. If this is a physical streaming replication you will see all information about your replicas.
select * from pg_stat_replication;

state should be “streaming”
You will also find information about transactions which were sent, written, flushed and replayed on the replica, and information about lags for each of them.

Hope it helps!

1 Like
3

Hi @mateusz.henicz, thank you for the detailed answer. The replication slot was marked as wal_status: extended, with several bytes behind. I tried issuing CHECKPOINT commands, but nothing got Postgres to update that slot. I have finally dropped the replication slot in question, following your recommendation, but it was automatically created soon after. It has been 20 hours since I did that, and the slot is again several bytes behind, and marked as active: false and wal_status: extended.

Which brings the question: Is something wrong with my Patroni cluster configuration? The replicas are all marked as stable, and their data seems fine.

image
image1072×613 51.5 KB

4

It looks to be an additional physical replication slot added to your patroni configuration.
You should have something like

slots: 
  percona_cluster_1:
    type: physical

in your patroni config.
Sorry if the syntax is not exact, I did that from memory, but if you have this in your patroni configuration, please delete it, reload configuration, using patronictl -c /config/file.yml reload and see if the slot disappears.

5

I have that configuration copied from the Percona Distribution for PostgreSQL documentation:

I followed the tutorial to the letter in this cluster implementation, did not change anything other than what was requested. Is it not needed?

6

I think this can be confirmed as a problem on Percona’s documentation and instructions. The author of Patroni has said this configuration is incorrect: WAL Files are not deleted after 20 GB pg_restore on primary node · Issue #3117 · patroni/patroni · GitHub

Where can I file a bug report for the documentation?

7

Hey,
Yes, it is completely not needed. It probably got there by mistake.
I have sent a Pull Request with changes that remove the replication slot definition from a configuration file.
Thanks for pointing that out!

1 Like
8

Thank you for the attention on this!