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.
Fast forward two weeks, this cluster has been running but not seeing any traffic. And my master server has filled its archive directory. I found an older thread <https://www.postgresql.org/message-id/417c5af7c228b94490192951394befe7b48...@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 were thousands of log files... 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... 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'
