Subject: DB size value different than "/data" dir size

From: "Ghiurea, Isabella" <[email-masked]>
Date: Fri, 8 Aug 2025 23:17:19 +0000

Hi List,

I am running   PGSQL version 12.3   cfg   master-slave  with  streaming replication and I am facing a issue in the present active Pg db.

The database size reported from  the following : pg_size_pretty() or pg_database_size() or sum all of db  objects is  grater than the  sum size of  all directories/files reported  under /var/lib/pgsql/data1/tbl_space_name/PG_12,,,xxx/ .  ( db data tablespace)

[ Mention in standby PG db the size matches the /data  sum file size and also  matches with the size reported in Pg active for  pg_size_pretty() or pg_database_size ]

What can be the issue?

thank you !

Isabella

---

From: Ron Johnson <[email-masked]>
Date: Fri, 8 Aug 2025 19:30:44 -0400

On Fri, Aug 8, 2025 at 7:17 PM Ghiurea, Isabella <
[email-masked]> wrote:

> Hi List,
>
> I am running   PGSQL version 12.3
>

Ignoring the fact that PG 12 is nine months EOL, 12.3 is missing a _lot_ of
bug fixes.  You should be on 12.22.

>  cfg   master-slave  with  streaming replication and I am facing a issue
> in the present active Pg db.
>
> The database size reported from  the following : pg_size_pretty() or
> pg_database_size() or sum all of db  objects is  grater than the  sum size
> of  all directories/files reported  under
> /var/lib/pgsql/data1/tbl_space_name/PG_12,,,xxx/ .  ( db data tablespace)
>
>
> [ Mention in standby PG db the size matches the /data  sum file size and
> also  matches with the size reported in Pg active for  pg_size_pretty() or
> pg_database_size ]
>
>
> What can be the issue?
>

What's in $PGDATA?

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Laurenz Albe <[email-masked]>
Date: Sun, 10 Aug 2025 21:27:31 +0200

On Fri, 2025-08-08 at 23:17 +0000, Ghiurea, Isabella wrote:
> I am running   PGSQL version 12.3   cfg   master-slave  with  streaming replication
> and I am facing a issue in the present active Pg db. 
> The database size reported from  the following : pg_size_pretty() or pg_database_size()
> or sum all of db  objects is  grater than the  sum size of  all directories/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_default.

Yours,
Laurenz Albe

---

From: "Ghiurea, Isabella" <[email-masked]>
Date: Mon, 11 Aug 2025 14:13:36 +0000

I believe this is related to VACUUM FULL behavior in a streaming replication 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 but is  not the actual sum of /var/lib/pgsql/data1 files +dir size on the active PG were vacuum full reclaim the space.                                                                                                                                                                Is this a normal behavior  in PG with reporting incorrect db size if vacuum full has not reclaim the OS disk space on both server?

________________________________
From: Laurenz Albe <[email-masked]>
Sent: Sunday, August 10, 2025 12:27:31 PM
To: Ghiurea, Isabella; [email-masked]
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érieur du CNRC.

Yours,
Laurenz Albe

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 13 Aug 2025 07:09:21 +0200

On Mon, 2025-08-11 at 14:13 +0000, Ghiurea, Isabella wrote:
> I believe this is related to VACUUM FULL behavior in a streaming replication 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 but is  not the actual sum of
> /var/lib/pgsql/data1 files +dir size on the active PG were vacuum full reclaim the space.
> 
> Is this a normal behavior  in PG with reporting incorrect db size if vacuum full has
> not reclaim the OS disk space on both server?

VACUUM (FULL) will also reclaim the size on the standby.
There can be some delay in the case of a replication conflict.

Yours,
Laurenz Albe