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 1pIJ5S-0004Z2-W6 for pgsql-general@arkaria.postgresql.org; Thu, 19 Jan 2023 00:47:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pIJ5Q-0004n4-Kh for pgsql-general@arkaria.postgresql.org; Thu, 19 Jan 2023 00:47:24 +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 1pIJ5Q-0004lp-42 for pgsql-general@lists.postgresql.org; Thu, 19 Jan 2023 00:47:24 +0000 Received: from mout-u-107.mailbox.org ([2001:67c:2050:101:465::107]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pIJ5M-00056n-Ue for pgsql-general@lists.postgresql.org; Thu, 19 Jan 2023 00:47:22 +0000 Received: from smtp2.mailbox.org (smtp2.mailbox.org [IPv6:2001:67c:2050:b231:465::2]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-107.mailbox.org (Postfix) with ESMTPS id 4Ny3qc3k47z9sQZ; Thu, 19 Jan 2023 01:47:08 +0100 (CET) Date: Thu, 19 Jan 2023 01:47:07 +0100 (CET) From: Erik Wienhold To: Ian Lawrence Barwick Cc: "Hilbert, Karin" , "pgsql-general@lists.postgresql.org" Message-ID: <1018934732.57323.1674089227907@office.mailbox.org> In-Reply-To: References: <1852652390.56939.1674085819457@office.mailbox.org> Subject: Re: Database size different on Primary and Standby? MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4Ny3qc3k47z9sQZ List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 19/01/2023 01:23 CET Ian Lawrence Barwick wrote: > > 2023=E5=B9=B41=E6=9C=8819=E6=97=A5(=E6=9C=A8) 8:50 Erik Wienhold : > > > > > On 19/01/2023 00:09 CET Hilbert, Karin wrote: > > > > > > I manage some PostgreSQL clusters on Linux. We have a Primary & two S= tandby > > > servers & for Production, there is also a DR server. We use repmgr fo= r our HA > > > solution & the Standbys are cloned from the Primary using the repmgr = standby > > > clone command. > > > > > > My manager asked for a report of all the user databases & their sizes= for each > > > server in the cluster. I used the psql "\l+" command & then extracted= the > > > database name & the size from the output. I expected the databases to= be the > > > same size on the Standbys as on the Primary, but I found that some of= the > > > databases were smaller on the Standby servers than on the Primary. > > > > > > For example, the output on the Primary for one of the user databases = showed > > > as: 8997 kB, but on the Standbys, it was 8849 kB. > > > > The standbys could be missing some indexes because schema changes are n= ot > > replicated and must be applied manually. > > This is incorrect; with streaming replication all changes applied on the = primary > are applied on the standby. Thanks. I was thinking about logical replication. > (...) > > Could also be different page sizes. But that's a compilation option. Wh= at does > > SHOW block_size say on those systems? > > It is impossible to start a standby using binaries built with a > different block size to > the primary. Makes sense for streaming replication. -- Erik