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@xxxxxxxxxxxxxxxxxxxxxxxx>
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@xxxxxxxxxxx
