Re: WAL Archive Cleanup?

On 3/21/19 11:51 AM, Foo Bar wrote:
> Hello,
> 
> We're evaluating PostgreSQL for use with Artifactory in our 
> environment.  PostgreSQL seems like the obvious choice because it 
> provides hot-standby replication.  I've followed several guides I've 
> found by googling "postgres replication how to" (i.e.: this one from 
> DigitalOcean 
>
<https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps>,

> though I did it on Ubuntu 18 and CentOS 7), was able to make some test 
> inserts, and everything seemed to be working well.

Postgres version?

> 
> Fast forward two weeks, this cluster has been running but not seeing any 
> traffic.  And my master server has filled its archive directory.  I 

It's the standby that has not seen any traffic?

> found an older thread 
> <https://www.postgresql.org/message-id/417C5AF7C228B94490192951394BEFE7B4805F@AIPHLEXDAG01B.airgas.com> 
> that seemed to indicate that the wal_keep_segments is what was causing 
> psql to keep so many WAL files...  However, mine is set to 8, but there 

FYI, psql is the Postgres client program, Postgres(ql) is the server.

> were thousands of log files...

You also have max_replication_slots = 5 also. See:

https://www.postgresql.org/docs/11/warm-standby.html#STREAMING-REPLICATION-SLOTS

"Replication slots provide an automated way to ensure that the master 
does not remove WAL segments until they have been received by all 
standbys, and that the master does not remove rows which could cause a 
recovery conflict even when the standby is disconnected.

In lieu of using replication slots, it is possible to prevent the 
removal of old WAL segments using wal_keep_segments, or by storing the 
segments in an archive using archive_command. However, these methods 
often result in retaining more WAL segments than required, whereas 
replication slots retain only the number of segments known to be needed. 
An advantage of these methods is that they bound the space requirement 
for pg_wal; there is currently no way to do this using replication slots."

Looks like the slots take precedence.

> 
> I was able to delete everything in the archive/ directory, start the 
> database back up, and there was no data loss... which is fine in the 
> lab, but if we're going to roll this out to production, I'm concerned 
> that we'll be continually running into this issue every couple weeks...
> 
> There seems to be a pg_archivecleanup 
> <https://www.postgresql.org/docs/9.2/pgarchivecleanup.html> command, 
> which I could run as a cron job, but something tells me that isn't the 
> recommended way as no literature I've found recommends this...

Slots will clean up after themselves once the segments are no longer 
needed. The catch is that the segments need to be consumed by the 
standby. What needs to be determined here is why the standby never 
consumed the WAL's from the master?  Do you still have the logs from the 
standby and do they show anything relevant?

> 
> This SO thread 
> <https://stackoverflow.com/questions/21113210/archive-cleanup-command-when-is-called> 
> seems to indicate that archive_cleanup_command can be run every "restart 
> point", but googling "psql restart point" brings me to this page 
> <https://www.postgresql.org/docs/9.1/continuous-archiving.html> which a 
> ^F indicates does not mention a "restart point"...  So this /feels/ like 
> the configuration setting I want, but I can't find the documentation 
> that confirms it for me...
> 
> So at this point I'm kinda stumped.  I could definitely add more space, 
> but if those WAL files are never cleaned up, even adding a 1TB Store for 
> the archive is just delaying the inevitable.
> 
> Thanks!
> -QBR
> 
> These are my configs:
> 
> postgresql.local.conf  -
> 
> effective_cache_size=1500MB
> shared_buffers=500MB
> maintenance_work_mem=125MB
> work_mem=5MB
> temp_buffers=5MB
> 
> postgresql.conf -
> 
> listen_addresses = '0.0.0.0'
> port = 5432
> max_connections = 100
> external_pid_file = '/hab/svc/postgresql/var/postgresql.pid'
> authentication_timeout = 1min
> max_files_per_process = 1000
> max_locks_per_transaction = 64
> logging_collector = on
> log_directory = '/hab/svc/postgresql/var/pg_log'
> log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
> log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,client=%h %r (%x:%e)'
> log_min_messages = ERROR
> datestyle = 'iso, mdy'
> default_text_search_config = 'pg_catalog.english'
> data_directory = '/hab/svc/postgresql/data/pgdata'
> hba_file = '/hab/svc/postgresql/config/pg_hba.conf'
> wal_level = hot_standby
> wal_log_hints = 'on'
> hot_standby = 'on'
> hot_standby_feedback = true
> max_wal_senders = 5
> max_replication_slots = 5
> checkpoint_completion_target = 0.9
> max_wal_size = 1GB
> min_wal_size = 128MB
> wal_keep_segments = 8
> log_checkpoints = on
> log_lock_waits = on
> log_temp_files = 0
> log_autovacuum_min_duration = 0
> track_activity_query_size = 2048
> track_io_timing=on
> dynamic_shared_memory_type = 'none'
> archive_mode = 'on'
> archive_command = 'cp %p  /hab/svc/postgresql/data/archive/%f'
> archive_timeout = '10min'
> max_standby_archive_delay = '30s'
> synchronous_commit = local
> include '/hab/svc/postgresql/config/postgresql.local.conf'


-- 
Adrian Klaver
adrian.klaver@aklaver.com