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:49:27 +0200
Message-ID: <5891CB47.2090104@matrix.gatewaynet.com> (raw)
In-Reply-To: <CAFY9zEwhSOe29aocdzEzd=EfC0vQb6yKqhd==Ukh3ThpZu8pJg@mail.gmail.com>
References: <CAFY9zEyYS=Wu5eD4nQzU08mUMio8trLpjFmCTNFYhKWA56nvhA@mail.gmail.com>
<5891C050.3060100@matrix.gatewaynet.com>
<CAFY9zEwhSOe29aocdzEzd=EfC0vQb6yKqhd==Ukh3ThpZu8pJg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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@matrix.gatewaynet.com <mailto: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 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
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: <5891CB47.2090104@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