Обсуждение: DB size value different than "/data" dir size
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
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?
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
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?
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
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
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