On 11/7/19 7:47 AM, Zwettler Markus (OIZ) wrote:
I am heading out the door so I will not have time to look at below until
later. For those that get a chance before then, it would be nice to have
the Patroni conf file information also. The Patroni information may
answer the question, but it case it does not what actually is failover
in 3) below?
1) 9.6
2)
$ cat postgresql.conf
# Do not edit this file manually!
# It will be overwritten by Patroni!
include 'postgresql.base.conf'
cluster_name = 'pcl_l702'
hot_standby = 'on'
hot_standby_feedback = 'True'
listen_addresses = 'localhost,tstm49003.tstglobal.tst.loc,pcl_l702.tstglobal.tst.loc'
max_connections = '100'
max_locks_per_transaction = '64'
max_prepared_transactions = '0'
max_replication_slots = '10'
max_wal_senders = '10'
max_worker_processes = '8'
port = '5436'
track_commit_timestamp = 'off'
wal_keep_segments = '8'
wal_level = 'replica'
wal_log_hints = 'on'
hba_file = '/pgdata/pcl_l702/pg_hba.conf'
ident_file = '/pgdata/pcl_l702/pg_ident.conf'
$
$
$
$ cat postgresql.base.conf
datestyle = 'iso, mdy'
default_text_search_config = 'pg_catalog.english'
dynamic_shared_memory_type = posix
lc_messages = 'en_US.UTF-8'
lc_monetary = 'de_CH.UTF-8'
lc_numeric = 'de_CH.UTF-8'
lc_time = 'de_CH.UTF-8'
logging_collector = on
log_directory = 'pg_log'
log_rotation_age = 1d
log_rotation_size = 0
log_timezone = 'Europe/Vaduz'
log_truncate_on_rotation = on
max_connections = 100
timezone = 'Europe/Vaduz'
archive_command = 'test ! -f /tmp/pg_archive_backup_running_on_pcl_l702* && rsync --checksum %p /pgxlog_archive/pcl_l702/%f'
archive_mode = on
archive_timeout = 1800
cluster_name = pcl_l702
cron.database_name = 'pdb_l72_oiz'
# effective_cache_size
listen_addresses = '*'
log_connections = on
log_destination = 'stderr, csvlog'
log_disconnections = on
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_line_prefix = '%t : %h=>%u@%d : %p-%c-%v : %e '
log_statement = 'ddl'
max_wal_senders = 5
port = 5436
shared_buffers = 512MB
shared_preload_libraries = 'auto_explain, pg_stat_statements, pg_cron, pg_statsinfo'
wal_buffers = 16MB
wal_compression = on
wal_level = replica
# work_mem
3)
12:00h: primary - standby
=> Some clients commited some transactions; Failover
12:05h: standby - primary
=> Some clients connected + commited some transactions; Failover
12:10h: primary - standby
On 11/7/19 7:18 AM, Zwettler Markus (OIZ) wrote:
I already asked the Patroni folks. They told me this is not related to Patroni but Postgresql. ;-)
Hard to say without more information:
1) Postgres version
2) Setup/config info
3) Detail if what happened between 12:00 and 12:10
- Markus
On 11/7/19 5:52 AM, Zwettler Markus (OIZ) wrote:
we are using Patroni for management of our Postgres standby databases.
we take our (wal) backups on the primary side based on intervals and thresholds.
our archived wal's are written to a local wal directory first and moved to tape afterwards.
we got a case where Patroni switched back and forth sides quickly, e.g.:
12:00h: primary - standby
12:05h: standby - primary
12:10h: primary - standby
we realised that we will not have a wal backup of those wal's generated between 12:05h and 12:10h in this scenario.
how can we make sure that the whole wal sequence trail will be backuped? any idea?
Probably best to ask the Patroni folks:
https://github.com/zalando/patroni#community
- Markus
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
