Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kaHPt-0006T9-00 for pgsql-admin@arkaria.postgresql.org; Wed, 04 Nov 2020 11:57:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kaHPq-00034k-Lp for pgsql-admin@arkaria.postgresql.org; Wed, 04 Nov 2020 11:57:26 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kaHPq-00031s-De for pgsql-admin@lists.postgresql.org; Wed, 04 Nov 2020 11:57:26 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kaHPo-0007Bk-Ha for pgsql-admin@lists.postgresql.org; Wed, 04 Nov 2020 11:57:25 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id A425938014D; Wed, 4 Nov 2020 12:57:22 +0100 (CET) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id eo35m-nPB9p1; Wed, 4 Nov 2020 12:57:22 +0100 (CET) Received: from [192.168.178.197] (ip-37-201-154-46.hsi13.unitymediagroup.de [37.201.154.46]) by rs.plausibolo.de (Postfix) with ESMTPSA id 5C9AB3800FC; Wed, 4 Nov 2020 12:57:22 +0100 (CET) Subject: Re: Logical replication alternative To: pgsql-admin@lists.postgresql.org References: From: Holger Jakobs Message-ID: Date: Wed, 4 Nov 2020 12:57:21 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.4.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------D5B14ECD8D827A2D0D5B2316" Content-Language: de-DE List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------D5B14ECD8D827A2D0D5B2316 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit Okay, updating some tables once a day isn't actually replication. For this to happen, I would suggest you connect the two machines by using the Foreign Data Wrapper and then: * set up a foreign table * create a materialized view on the "slave" as "select * from foreign_table" * set up a job for refreshing the materialized view Regards, Holger Am 04.11.20 um 12:34 schrieb Yambu: > Hi Holger > > 1. We want to update the few tables once a day > 2. We want to avoid restart the mission critical database, changing > wal_level requires a restart > > regards  > > On Wed, Nov 4, 2020 at 1:28 PM Holger Jakobs > wrote: > > Hi, > > Why do you need an alternative to logical replication? Please tell us > why logical replication isn't for you. > > Regards, > > Holger > > > Am 04.11.20 um 11:55 schrieb Yambu: > > Hi > > > > May i know what is the alternative to logical replication if i > want to > > replicate a few tables on postgres v10? > > > > regards > > -- > Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 > > > -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 --------------D5B14ECD8D827A2D0D5B2316 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

Okay,

updating some tables once a day isn't actually replication.

For this to happen, I would suggest you connect the two machines by using the Foreign Data Wrapper and then:

  • set up a foreign table
  • create a materialized view on the "slave" as "select * from foreign_table"
  • set up a job for refreshing the materialized view

Regards,

Holger

Am 04.11.20 um 12:34 schrieb Yambu:
Hi Holger

1. We want to update the few tables once a day
2. We want to avoid restart the mission critical database, changing wal_level requires a restart

regards 

On Wed, Nov 4, 2020 at 1:28 PM Holger Jakobs <holger@jakobs.com> wrote:
Hi,

Why do you need an alternative to logical replication? Please tell us
why logical replication isn't for you.

Regards,

Holger


Am 04.11.20 um 11:55 schrieb Yambu:
> Hi
>
> May i know what is the alternative to logical replication if i want to
> replicate a few tables on postgres v10?
>
> regards

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012



-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------D5B14ECD8D827A2D0D5B2316--