Re: AW: AW: broken backup trail in case of quickly patroni switchback and forth

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(at)%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(dot)klaver(at)aklaver(dot)com