Re: Big sized materialized views break replication

[Samed YILDIRIM <samed@xxxxxxxxxx>]

Hi Dimitri,

 

Could you please share Postgres installation version?

Also what do you mean by saying recreate materialized view, refresh or drop/create?

 

Best regards.

 

İyi çalışmalar.

Samed YILDIRIM

 

 

 

01.02.2017, 12:41, "Κοκμάδης Δημήτριος" <dkokmadis@xxxxxxxxx>:

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