Обсуждение: [ADMIN] 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 settingsshared_buffers = 30GBwork_mem = 3146kBmaintenance_work_mem = 2GBdynamic_shared_memory_type = posix # the default is the first optionwal_level = hot_standbywal_buffers = 16MBcheckpoint_segments = 128checkpoint_completion_target = 0.9max_wal_senders = 128wal_keep_segments = 8effective_cache_size = 90GBdefault_statistics_target = 100logging_collector = on # Enable capturing of stderr and csvloglog_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 willlog_min_duration_statement = 3000 # -1 is disabled, 0 logs all statementslog_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 andautovacuum_max_workers = 3 # max number of autovacuum subprocessesautovacuum_naptime = 15min # time between autovacuum runsautovacuum_vacuum_threshold = 50 # min number of row updates beforeautovacuum_analyze_threshold = 50 # min number of row updates beforeRegards,Dimitris
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
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 settingsshared_buffers = 30GBwork_mem = 3146kBmaintenance_work_mem = 2GBdynamic_shared_memory_type = posix # the default is the first optionwal_level = hot_standbywal_buffers = 16MBcheckpoint_segments = 128checkpoint_completion_target = 0.9max_wal_senders = 128wal_keep_segments = 8effective_cache_size = 90GBdefault_statistics_target = 100logging_collector = on # Enable capturing of stderr and csvloglog_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 willlog_min_duration_statement = 3000 # -1 is disabled, 0 logs all statementslog_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 andautovacuum_max_workers = 3 # max number of autovacuum subprocessesautovacuum_naptime = 15min # time between autovacuum runsautovacuum_vacuum_threshold = 50 # min number of row updates beforeautovacuum_analyze_threshold = 50 # min number of row updates beforeRegards,Dimitris
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 16MBSamed: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,Dimitris2017-02-01 13:02 GMT+02:00 Achilleas Mantzios <achill@matrix.gatewaynet.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 settingsshared_buffers = 30GBwork_mem = 3146kBmaintenance_work_mem = 2GBdynamic_shared_memory_type = posix # the default is the first optionwal_level = hot_standbywal_buffers = 16MBcheckpoint_segments = 128checkpoint_completion_target = 0.9max_wal_senders = 128wal_keep_segments = 8effective_cache_size = 90GBdefault_statistics_target = 100logging_collector = on # Enable capturing of stderr and csvloglog_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 willlog_min_duration_statement = 3000 # -1 is disabled, 0 logs all statementslog_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 andautovacuum_max_workers = 3 # max number of autovacuum subprocessesautovacuum_naptime = 15min # time between autovacuum runsautovacuum_vacuum_threshold = 50 # min number of row updates beforeautovacuum_analyze_threshold = 50 # min number of row updates beforeRegards,Dimitris-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
If recovery.conf has this configuration parameter:
primary_slot_name = repmgr_slot_1
The replication use replication slots otherwise when wal_keep_segments is reached, the redo logs are rotated.
With replication slot you only have the required wal files but with wal_keep_segments you only has the amount especified in it.
If you do not use replication slots there is no need of creating slots, see SELECT * from pg_replication_slots; on the master.
When a slot is used, wal_keep_segments has no effects and could be 0.
Regards.
De: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] En nombre de ???µ?d?? ??µ?t????
Enviado el: martes, 21 de febrero de 2017 08:23 a. m.
Para: pgsql-admin@postgresql.org
Asunto: Re: [ADMIN] Big sized materialized views break replication
Hello,
Is it possible to use replication with slots and without them in the same setup or it could cause a problem?
Regards,
Dimitris