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 1pIKQN-0008LT-UG for pgsql-general@arkaria.postgresql.org; Thu, 19 Jan 2023 02:13:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pIKQM-0000dG-MD for pgsql-general@arkaria.postgresql.org; Thu, 19 Jan 2023 02:13:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pIKQM-0000d7-8q for pgsql-general@lists.postgresql.org; Thu, 19 Jan 2023 02:13:06 +0000 Received: from mail-oa1-x2a.google.com ([2001:4860:4864:20::2a]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pIKQK-00028i-07 for pgsql-general@lists.postgresql.org; Thu, 19 Jan 2023 02:13:05 +0000 Received: by mail-oa1-x2a.google.com with SMTP id 586e51a60fabf-12c8312131fso1091095fac.4 for ; Wed, 18 Jan 2023 18:13:03 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=XYGhHNPrTl9+qU3scItdk0x8ho/PcwItrUym+4nWPG0=; b=QdeLwqRbX45j8FDyyyfgeLACGn7OGvuPF4CyRgoZH4z5Sf/yLILFs0ub/urcK/PsPo Etk/sClBG2TRVIKnBhiTBKuNafjufRxAmVfDl/ULDkU8Ba2avsV1lx4P9Hl/HfFr0nvJ 51yJuHviOjGOPqawoWaiojdZVxPVDNyE/q3wsrQj5j/BFxzF01XZwERO9+9AbxkEcsyM c/tQg/tKZelF90b7DKoywN3hmQ+s2ZebdoNp+svsd9a1I7h7/ZeH6Fp4V+UVAKFj9oQS Gytx1zEmJE10STJTDc4CwUR5iicszledcHFPzr7/8edS7NudnR+IA6TubnAvjszZBl3J VLXw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=XYGhHNPrTl9+qU3scItdk0x8ho/PcwItrUym+4nWPG0=; b=gPz3RQlnRBsCbTzhRtp1xKQ8vDLBEhZYell2xuEjSD2I6/taxC5FNBUW8woKL8kOEl wC9CKHOXG0ggZbDqmbZ3ff/k4N5BfOHhRLLxnhEGo2MJ3tuIhkh06pxQHZrO2/JTXakf c7vI9NW9mlmoBzlC/R2gIXbHRhHDG0+dX2bSkgd+bX2f3tBhr4/K+jWItvmES3Rq0AK6 RPiM45tDN6NnIpqjtuA60H6xJ89Y7tuoh5FrE76amhuY3foFM/t6N7csdFU7XA7qNGdZ qO6J02SVodHWZj6PiSW6STTN3SDJPNtdniA4ZyyWJIsiZFDxyfJIXBdwRGB1vgVT2QrR vjxQ== X-Gm-Message-State: AFqh2kow27pHaTIardQmIak3JINg4IJloHriKFkrXUWiBBNx7j8zBFvN lZR9aBlytom/0xJ9IL5Y5x5KAWGCtcQ= X-Google-Smtp-Source: AMrXdXup7/OKPbNbeTSrnzqG1f6X2FbkinZihAMlJSqnYqJX4ovJiDwsgPDijVdpM7t1qlkx4Or3cA== X-Received: by 2002:a05:6870:e752:b0:156:6665:ed6d with SMTP id t18-20020a056870e75200b001566665ed6dmr5535963oak.38.1674094381472; Wed, 18 Jan 2023 18:13:01 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id r4-20020a4aad04000000b004a394578e14sm17428892oon.32.2023.01.18.18.13.01 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 18 Jan 2023 18:13:01 -0800 (PST) Content-Type: multipart/alternative; boundary="------------2r8hX70suURqB040XcmE313q" Message-ID: Date: Wed, 18 Jan 2023 20:13:00 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: Database size different on Primary and Standby? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------2r8hX70suURqB040XcmE313q Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/18/23 17:09, Hilbert, Karin wrote: > I manage some PostgreSQL clusters on Linux.  We have a Primary & two > Standby servers & for Production, there is also a DR server.  We use > repmgr for 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*. > > I even dropped the database on the Primary & then restored it from a > backup.  Then checked the sizes again & they still showed the difference. > > I also found that the template1 database on the Primary was *7821 kB*, but > on the Standbys, it was *7673 kB*. > */Is this normal?  Why would the sizes be different?/* Maybe it is.  8997 - 8849 = 7821 - 7673 = 148.  That's right: both primaries are exatly 148KB larger. This will tell you where the differences are: $ du -kc $PGDATA -- Born in Arizona, moved to Babylonia. --------------2r8hX70suURqB040XcmE313q Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 1/18/23 17:09, Hilbert, Karin wrote:
I manage some PostgreSQL clusters on Linux.  We have a Primary & two Standby servers & for Production, there is also a DR server.  We use repmgr for 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.

I even dropped the database on the Primary & then restored it from a backup.  Then checked the sizes again & they still showed the difference.

I also found that the template1 database on the Primary was 7821 kB, but on the Standbys, it was 7673 kB.
Is this normal?  Why would the sizes be different?

Maybe it is.  8997 - 8849 = 7821 - 7673 = 148.  That's right: both primaries are exatly 148KB larger.

This will tell you where the differences are: $ du -kc $PGDATA

--
Born in Arizona, moved to Babylonia.
--------------2r8hX70suURqB040XcmE313q--