From: "Ghiurea, Isabella" <Isabella.Ghiurea@nrc-cnrc.gc.ca>
To: Laurenz Albe <laurenz.albe@cybertec.at>, "pgsql-admin@postgresql.org"
	<pgsql-admin@postgresql.org>
Subject: Re: Re: DB size  value different than  "/data"  dir size
Thread-Topic: Re: DB size  value different than  "/data"  dir size
Thread-Index: AQHcCLqKBXBvdCp5JEiZd/7jSflgvrRcizqAgAD15TU=
Date: Mon, 11 Aug 2025 14:13:36 +0000
Message-ID: <ea8a8186d36e4321ada5886c7bf66f3a@nrc-cnrc.gc.ca>
References: 
 <aa68b26ba58b43c980648d76c27f4ce9@nrc-cnrc.gc.ca>,<9e1ebefd12e1fc955eee580caca5e9c37fd005ca.camel@cybertec.at>
In-Reply-To: <9e1ebefd12e1fc955eee580caca5e9c37fd005ca.camel@cybertec.at>
Accept-Language: en-US
Content-Language: en-US
Content-Type: multipart/alternative;
	boundary="_000_ea8a8186d36e4321ada5886c7bf66f3anrccnrcgcca_"
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/ea8a8186d36e4321ada5886c7bf66f3a%40nrc-cnrc.gc.ca>
Precedence: bulk

--_000_ea8a8186d36e4321ada5886c7bf66f3anrccnrcgcca_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I believe this is related to VACUUM FULL behavior in a streaming replicatio=
n cfg ,   for the standby server the space is not been reclaimed , But the =
sieze of db reported with pg_pretty_db_size is identical on both db size bu=
t is  not the actual sum of /var/lib/pgsql/data1 files +dir size on the act=
ive PG were vacuum full reclaim the space.                                 =
                                                                           =
                                                    Is this a normal behavi=
or  in PG with reporting incorrect db size if vacuum full has not reclaim t=
he OS disk space on both server?

________________________________
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Sunday, August 10, 2025 12:27:31 PM
To: Ghiurea, Isabella; pgsql-admin@postgresql.org
Subject: EXT: Re: DB size value different than "/data" dir size

***Attention*** This email originated from outside of the NRC. ***Attention=
*** Ce courriel provient de l'ext=E9rieur du CNRC.
On Fri, 2025-08-08 at 23:17 +0000, Ghiurea, Isabella wrote:
> I am running   PGSQL version 12.3   cfg   master-slave  with  streaming r=
eplication
> and I am facing a issue in the present active Pg db.
> The database size reported from  the following : pg_size_pretty() or pg_d=
atabase_size()
> or sum all of db  objects is  grater than the  sum size of  all directori=
es/files
> reported  under /var/lib/pgsql/data1/tbl_space_name/PG_12,,,xxx/ .  ( db =
data tablespace)

Perhaps some part of the data is in a different tablespace, maybe pg_defaul=
t.

Yours,
Laurenz Albe


--_000_ea8a8186d36e4321ada5886c7bf66f3anrccnrcgcca_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-=
1">
<meta name=3D"Generator" content=3D"Microsoft Exchange Server">
<!-- converted from text --><style><!-- .EmailQuote { margin-left: 1pt; pad=
ding-left: 4pt; border-left: #800000 2px solid; } --></style>
</head>
<body>
<meta content=3D"text/html; charset=3DUTF-8">
<style type=3D"text/css" style=3D"">
<!--
p
	{margin-top:0;
	margin-bottom:0}
-->
</style>
<div dir=3D"ltr">
<div id=3D"x_divtagdefaultwrapper" dir=3D"ltr" style=3D"font-size:12pt; col=
or:#000000; font-family:Calibri,Helvetica,sans-serif">
<p>I believe this is related to VACUUM FULL behavior in a streaming replica=
tion cfg ,&nbsp; &nbsp;for the standby server the space is not been reclaim=
ed , But the sieze of db reported with pg_pretty_db_size is identical on bo=
th db size but is&nbsp; not the actual sum of /var/lib/pgsql/data1
 files&nbsp;&#43;dir size on the active PG were vacuum full reclaim the spa=
ce.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &n=
bsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; =
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp=
; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nb=
sp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &=
nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;=
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbs=
p; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Is this a normal behavior&nbsp; in
 PG&nbsp;with reporting incorrect db size if vacuum full has not reclaim th=
e OS disk space on both server?</p>
</div>
<hr tabindex=3D"-1" style=3D"display:inline-block; width:98%">
<div id=3D"x_divRplyFwdMsg" dir=3D"ltr"><font face=3D"Calibri, sans-serif" =
color=3D"#000000" style=3D"font-size:11pt"><b>From:</b> Laurenz Albe &lt;la=
urenz.albe@cybertec.at&gt;<br>
<b>Sent:</b> Sunday, August 10, 2025 12:27:31 PM<br>
<b>To:</b> Ghiurea, Isabella; pgsql-admin@postgresql.org<br>
<b>Subject:</b> EXT: Re: DB size value different than &quot;/data&quot; dir=
 size</font>
<div>&nbsp;</div>
</div>
</div>
<font size=3D"2"><span style=3D"font-size:10pt;">
<div class=3D"PlainText">***Attention*** This email originated from outside=
 of the NRC. ***Attention*** Ce courriel provient de l'ext=E9rieur du CNRC.
<br>
On Fri, 2025-08-08 at 23:17 &#43;0000, Ghiurea, Isabella wrote:<br>
&gt; I am running&nbsp; &nbsp;PGSQL version 12.3&nbsp; &nbsp;cfg&nbsp;&nbsp=
;&nbsp;master-slave&nbsp; with&nbsp; streaming replication<br>
&gt; and I am facing a issue in the present active Pg db.&nbsp;<br>
&gt; The database size reported from&nbsp; the following :&nbsp;pg_size_pre=
tty() or pg_database_size()<br>
&gt; or sum all of db&nbsp; objects is&nbsp; grater than the&nbsp; sum size=
 of&nbsp; all&nbsp;directories/files<br>
&gt; reported&nbsp; under /var/lib/pgsql/data1/tbl_space_name/PG_12,,,xxx/ =
.&nbsp; ( db data tablespace)<br>
<br>
Perhaps some part of the data is in a different tablespace, maybe pg_defaul=
t.<br>
<br>
Yours,<br>
Laurenz Albe<br>
<br>
</div>
</span></font>
</body>
</html>

--_000_ea8a8186d36e4321ada5886c7bf66f3anrccnrcgcca_--