WAL Archive Cleanup?

From: Foo Bar <qubitrenegade(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: WAL Archive Cleanup?
Date: 2019-03-21 18:51:18
Message-ID: CACLHErRJmVj-9-MYoKrwcUaGmV4t6CG6Z4TvR+jpQ+y-282GFQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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/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 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'

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Munro 2019-03-21 21:45:06 Re: LDAP authenticated session terminated by signal 11: Segmentation fault, PostgresSQL server terminates other active server processes
Previous Message Eric Katchan 2019-03-21 15:47:55 RE: Postgres 9.6 Slave Creation