Re: Big sized materialized views break replication

Of course your problems is with the high latency cloud servers.
You are using 9.4 so this is the time to use the feature introduced in 9.4 called : replication slots.
Read the docs, replication slots address the problems you are now facing.

On 01/02/2017 13:13, Κοκμάδης Δημήτριος wrote:
> Hi,
>
> - max_replication_slots is 0 the default value
> - it is internal network 1GB
> - How can I check this? I think all my wal files are 16MB
>
> Samed:
> My Postgres version is 9.4 and I use the refresh command.
>
> I have also to notice that I have 4 replication servers and the problem happens most of the times in cloud servers. In bare metal server, most times refresh works without problem.
>
> Regards,
>
> Dimitris
>
>
>
> 2017-02-01 13:02 GMT+02:00 Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com <mailto:achill(at)matrix(dot)gatewaynet(dot)com>>:
>
> Για σου Δημήτρη,
>
> 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
>
>

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