Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fcQCp-0008Sw-QD for pgsql-admin@arkaria.postgresql.org; Mon, 09 Jul 2018 07:03:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fcQCn-0007xV-Rm for pgsql-admin@arkaria.postgresql.org; Mon, 09 Jul 2018 07:03:29 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fcQCm-0007xI-Rs for pgsql-admin@lists.postgresql.org; Mon, 09 Jul 2018 07:03:29 +0000 Received: from himalaya.serversfarm.com ([173.254.202.145] helo=rio.serversfarm.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fcQCj-0006Cl-Lq for pgsql-admin@postgresql.org; Mon, 09 Jul 2018 07:03:27 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=ibosstech-us.com; s=default; h=Content-Type:Mime-Version:Reply-To: References:In-Reply-To:Message-Id:Date:Subject:To:From:Sender:Cc: Content-Transfer-Encoding:Content-ID:Content-Description:Resent-Date: Resent-From:Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID:List-Id: List-Help:List-Unsubscribe:List-Subscribe:List-Post:List-Owner:List-Archive; bh=V1OOQJgNmIo/jD9mnXX3DCuOqN07TpIRD2bqoCNkQbk=; b=W8mUAzkYbfBAhsQdi919XCNXb YtEWc+8hDAYwwRrHmoJTx5i8lA9IprFtJ/iq0rWtTu5NLExRyrfnPhu8JC0hDiBsdgZIEmVaIQ+IO gKcWPd5RcY88DCz6tuLM6Nu45YMPJin2k89vStFSWYb2iUtmyPpLgTOLhTTJyhoIxFbFaDi4q4U3S I0OeAxU0LvZxqeFdii5WhccNHPjUBR90/au0efLdxdUAYDD9Y1FXctQkflMPHCxGOiWK4eW+0X2Yi Y/aOgPJDToQUTM7WTS6jjSAueRMgG+a/PZjXfWuw8IBBcCAN6b+MzT5cTgcz0WBRXPU6909k8ECrN aAUd8dYqw==; Received: from [111.93.60.54] (port=28474 helo=[192.168.3.137]) by rio.serversfarm.com with esmtpsa (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256) (Exim 4.91) (envelope-from ) id 1fcQCf-0082om-IR; Mon, 09 Jul 2018 03:03:23 -0400 From: "Anjul Tyagi" To: "Shreeyansh Dba" , "pgsql-admin@postgresql.org" Subject: Re: logical Replication Date: Mon, 09 Jul 2018 07:03:18 +0000 Message-Id: In-Reply-To: References: Reply-To: "Anjul Tyagi" User-Agent: eM_Client/7.1.31849.0 Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="------=_MBE53C0282-A20B-41E7-BD0B-DD4652AFBD2D" X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - rio.serversfarm.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - ibosstech-us.com X-Get-Message-Sender-Via: rio.serversfarm.com: authenticated_id: anjul@ibosstech-us.com X-Authenticated-Sender: rio.serversfarm.com: anjul@ibosstech-us.com X-Source: X-Source-Args: X-Source-Dir: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --------=_MBE53C0282-A20B-41E7-BD0B-DD4652AFBD2D Content-Type: text/plain; format=flowed; charset=utf-8 Content-Transfer-Encoding: quoted-printable Shreeyansh, Yes.. we have same structure in the subscription side as well. Regards, Anjul TYAGI =C3=BC Go Green ------ Original Message ------ From: "Shreeyansh Dba" To: "Anjul Tyagi" Cc: "pgsql-admin@postgresql.org" Sent: 06-07-2018 21:02:33 Subject: Re: logical Replication >Hi Anjul, > >Do you have a big table with a structure on subscription side which you=20 >are trying to replicate? > > > > > >On Fri, Jul 6, 2018 at 12:41 PM, Anjul Tyagi =20 >wrote: >>Team, >> >>we are trying to setup an logical replication on the Postgres 10.4, we=20 >>are able to replicate the small tables but facing issue with big=20 >>tables, when table has around 60 columns and 30 million rows. >> >>While checking the log on publication server, it shows copy command=20 >>but did not copy data to subscriber server. >> >>Postgres Version - 10.4 >>Linux - SUSE SLE-12.3 >> >>Following are the setting we have enabled on Primary server: >> >>Primary Server: >>wal_level =3D logical >>shared_buffers =3D 6GB >>effective_cache_size =3D 18GB >>maintenance_work_mem =3D 1536MB >>checkpoint_completion_target =3D 0.9 >>wal_buffers =3D 16MB >>default_statistics_target =3D 100 >>random_page_cost =3D 1.1 >>effective_io_concurrency =3D 200 >>work_mem =3D 3145kB >>min_wal_size =3D 1GB >>max_wal_size =3D 2GB >>max_replication_slots =3D 25 >>max_wal_senders =3D 30 >>max_parallel_workers_per_gather =3D 2 >>max_parallel_workers =3D 4 >> >>Secondary Server: >>shared_buffers =3D 6GB >>effective_cache_size =3D 18GB >>maintenance_work_mem =3D 1536MB >>checkpoint_completion_target =3D 0.9 >>wal_buffers =3D 16MB >>default_statistics_target =3D 100 >>random_page_cost =3D 1.1 >>effective_io_concurrency =3D 200 >>work_mem =3D 3145kB >>max_replication_slots =3D 25 >>max_logical_replication_workers =3D 30 >>max_worker_processes =3D 31 >> >>can you please help us to understand, if is there any issue on the=20 >>configuration. >> >> >>Regards, >> >>Anjul TYAGI >> >> >> >>=C3=BC Go Green >> >> > --------=_MBE53C0282-A20B-41E7-BD0B-DD4652AFBD2D Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Shreeyansh,

Yes.. we have same structure in t= he subscription side as well.=C2=A0

=C2=A0
=C2=A0
=C2=A0

Regards,

Anjul TYAGI

=C2=A0

=C3=BC<= /b>=C2=A0Go=C2= =A0Green


------ Original Message ------
From: "Shreeyansh Dba" <shreeyansh2014@gmail.com>
To: "Anjul Tyagi" <anjul@= ibosstech-us.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: 06-07-2018 21:02:33
Subject: Re: logical Replication

Hi= Anjul,

Do you have a big table with a structure on subscription= side which you are trying to replicate?





On Fri, Jul 6, 2018 at 12:41 PM, Anjul Tya= gi <anjul@ib= osstech-us.com> wrote:
Team,

we are trying to setup an logica= l replication on the Postgres 10.4, we are able to replicate the small tabl= es but facing issue with big tables, when table has around 60 columns and 3= 0 million rows.=C2=A0

While checking the log on= publication server, it shows copy command but did not copy data to subscrib= er server.=C2=A0

Postgres Version - 10.4
Linux - SUSE SLE-12.3

Following are the setti= ng we have enabled on Primary server:

Primary = Server:
wal_level =3D logical
shared_buffers =3D 6= GB
effective_cache_size =3D 18GB
maintenance_work_mem = =3D 1536MB
checkpoint_completion_target =3D 0.9
wal_bu= ffers =3D 16MB
default_statistics_target =3D 100
random= _page_cost =3D 1.1
effective_io_concurrency =3D 200
wor= k_mem =3D 3145kB
min_wal_size =3D 1GB
max_wal_size =3D= 2GB
max_replication_slots =3D 25<= /span>
max_wal_senders =3D 30=
max_parallel= _workers_per_gather =3D 2
max_parallel_workers =3D 4

Se= condary Server:
shared_buffers =3D 6GB
effective_cache_size =3D 18GB
maintenance_work_mem =3D 153= 6MB
checkpoint_completion_target =3D 0.9
wal_buffers= =3D 16MB
default_statistics_target =3D 100
random_page_co= st =3D 1.1
effective_io_concurrency =3D 200
work_mem= =3D 3145kB
max_replication_slots =3D 25=C2=A0
max_logical= _replication_workers =3D 30
max_worker_processes =3D 3= 1

can you please help us to understand, if is th= ere any issue on the configuration.
=C2=A0
=C2=A0

Regards= ,

Anjul <= b>TYAGI

=C2= =A0

=C3=BC=C2=A0Go=C2=A0Green



--------=_MBE53C0282-A20B-41E7-BD0B-DD4652AFBD2D--