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, 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 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 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 seems to indicate that archive_cleanup_command can be run every "restart point", but googling "psql restart point" brings me to this page 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'
