Re: Big sized materialized views break replication

Για σου Δημήτρη,

you will have to give more info on :
- setup of replication slots
- speed/latency of connection between primary and standby
- size of wals produced during the recreation of materialization

On 01/02/2017 12:39, Κοκμάδης Δημήτριος wrote:
> Hello,
>
> I use hot standby replication mode.
> When I try to recreate big sized materialised views, about 3G size, the replication breaks and I have to resync it.
>
> Is there any way to avoid it?
>
> My master server settings
>
> shared_buffers = 30GB
> work_mem = 3146kB
> maintenance_work_mem = 2GB
> dynamic_shared_memory_type = posix# the default is the first option
> wal_level = hot_standby
> wal_buffers = 16MB
> checkpoint_segments = 128
> checkpoint_completion_target = 0.9
> max_wal_senders = 128
> wal_keep_segments = 8
> effective_cache_size = 90GB
> default_statistics_target = 100
> logging_collector = on# Enable capturing of stderr and csvlog
> log_directory = '/var/log/postgresql'# directory where log files are written,
> log_filename = 'postgresql-%Y-%m-%d.log'# log file name pattern,
> log_rotation_age = 1d# Automatic rotation of logfiles will
> log_min_duration_statement = 3000# -1 is disabled, 0 logs all statements
> log_line_prefix = '%t '# special values:
> log_timezone = 'localtime'
> stats_temp_directory = '/var/run/postgresql/9.4-main.pg_stat_tmp'
> autovacuum = on# Enable autovacuum subprocess? 'on'
> log_autovacuum_min_duration = -1# -1 disables, 0 logs all actions and
> autovacuum_max_workers = 3# max number of autovacuum subprocesses
> autovacuum_naptime = 15min# time between autovacuum runs
> autovacuum_vacuum_threshold = 50# min number of row updates before
> autovacuum_analyze_threshold = 50# min number of row updates before
>
>
> Regards,
> Dimitris

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt