AW: AW: broken backup trail in case of quickly patroni switchback andforth - Mailing list pgsql-general

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@aklaver.com