Скачать или смотреть Understanding Why Your PostgreSQL Replication Has Excessive Size and How to Fix It

Discover the cause of excessive size in your PostgreSQL replication and learn effective methods to reduce it. Stop unnecessary storage usage now!
---
This video is based on the question https://stackoverflow.com/q/63552624/ asked by the user 'kiitosu' ( https://stackoverflow.com/u/4093031/ ) and on the answer https://stackoverflow.com/a/63553096/ provided by the user 'jjanes' ( https://stackoverflow.com/u/1721239/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: postgresql replication have too much size

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding Why Your PostgreSQL Replication Has Excessive Size and How to Fix It

If you've recently set up PostgreSQL replication and found that your replica database is significantly larger than your primary database, you are not alone. A common issue that many database administrators face is an unexpected increase in the size of the replication storage. In this guide, we will take a closer look at the possible causes of this problem and guide you through the solutions to help you regain control over your storage.

The Problem: Imbalanced Sizes of Primary and Replicated Databases

In PostgreSQL, it's expected that the size of a replicated database will be similar to that of the primary database. However, in your case, you noticed a stark contrast:

Primary Database Size: 1.5 TB

Replicated Database Size: 7.2 TB

Upon further examination using the du command, you observed that certain directories like pg_wal, which houses Write-Ahead Logging (WAL) files, drastically contributed to the larger size of the replica. Here’s a quick breakdown of your findings:

Primary Database pg_wal Size: 273 MB

Replication Database pg_wal Size: 5.8 TB

This indicates a significant issue with how WAL files are being managed on your replication setup.

Why Is This Happening?

1. Potential Misconfiguration During Setup

When you set up the replica, there is a chance that you might have inadvertently copied over the pg_replslot directory. This directory is responsible for maintaining replication slots for WAL files. If it exists on the replica, it may be keeping WAL files that should otherwise be purged, as there is no cascading replica set up to consume them.

2. Archive Command Mismanagement

In some cases, if you have set archive_mode to always but the archive_command is not functioning properly on your replica, the WAL files will accumulate. This mismanagement could lead to disks filling up rapidly, exacerbating the size disparity.

How to Resolve the Issue

Step-by-Step Solutions

A. Remove the pg_replslot Directory

Access the Replicated Database: Log in to your replication database server.

Navigate to the Data Directory: Identify where your PostgreSQL data is located, typically under the directory that contains base, global, etc.

Delete pg_replslot: Execute the following command to remove the pg_replslot directory:

[[See Video to Reveal this Text or Code Snippet]]

Restart the Replica: Once deleted, restart your PostgreSQL replica so it can regenerate necessary WAL file management.

B. Check and Fix the archive_command

Verify archive_mode: Check your PostgreSQL configuration files (typically postgresql.conf) to confirm that archive_mode is set appropriately.

Test the archive_command: Ensure that it works correctly by running it manually or reviewing logs. If necessary, adjust the command to ensure it successfully archives your WAL files.

Monitor the Size: After making these changes, monitor the size of the replicated database over the next few days to confirm that it normalizes.

Important Considerations

Backup Data: Always back up your data before making substantial changes to your database setup.

Monitor Regularly: Implement regular monitoring scripts to track the size of your WAL files and overall database sizes, making adjustments as necessary.

Conclusion

Addressing the issue of excessive size in PostgreSQL replication isn't overly complicated, but it does require vigilance and proper configuration. By understanding the role of the WAL files and ensuring that your replica is set up correctly, you can significantly reduce storage size and optimize performance. Transitioning through these steps will not only help you reclaim the lost space but also maintain a healthy, efficient database environme