From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@postgresql.org
Subject: Re: Big sized materialized views break replication
Date: Wed, 1 Feb 2017 13:02:40 +0200
Message-ID: <5891C050.3060100@matrix.gatewaynet.com> (raw)
In-Reply-To: <CAFY9zEyYS=Wu5eD4nQzU08mUMio8trLpjFmCTNFYhKWA56nvhA@mail.gmail.com>
References: <CAFY9zEyYS=Wu5eD4nQzU08mUMio8trLpjFmCTNFYhKWA56nvhA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Για σου Δημήτρη,
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: Big sized materialized views break replication
In-Reply-To: <5891C050.3060100@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox