Thread: Reg: Size difference
Hi Team
I have been using PostgreSQL for the past 6 years. PostgreSQL has significantly impacted my life, providing me with great opportunities for knowledge and self-development.
I'm currently facing a strange issue with PostgreSQL 15.0. I have a primary-standby setup that is in sync, with a replication slot in place. There are 18 databases, and one of the databases on the primary side is 104 GB, while the same database on the standby side is 216 GB. Both are in sync with zero delay.
Could this be a bug? If so, has it been resolved in newer releases? If it is not a bug, how can this issue be fixed? Is there a solution or any supporting documentation available?
WAL and log files are being rotated properly. The issue is with a database named services_mfs. On the primary cluster, the services_mfs database is 104GB, but on the standby cluster, it is 216GB, even though both cluster are in sync. The standby database is only used in case of a crash, which is managed by a Patroni cluster with etcd.
Thanks,
Vinay Kumar
On 9/14/24 10:19, Vinay Oli wrote: > Hi Team > > I have been using PostgreSQL for the past 6 years. PostgreSQL has > significantly impacted my life, providing me with great opportunities > for knowledge and self-development. > > I'm currently facing a strange issue with PostgreSQL 15.0. I have a > primary-standby setup that is in sync, with a replication slot in place. > There are 18 databases, and one of the databases on the primary side is > 104 GB, while the same database on the standby side is 216 GB. Both are > in sync with zero delay. How are you measuring the size? If you are measuring on the files system what is the size of the base/ and pg_wal/ sub-directories in the %PG_DATA directoty? > > Could this be a bug? If so, has it been resolved in newer releases? If > it is not a bug, how can this issue be fixed? Is there a solution or any > supporting documentation available? > > WAL and log files are being rotated properly. The issue is with a > database named services_mfs. On the primary cluster, the services_mfs > database is 104GB, but on the standby cluster, it is 216GB, even though > both cluster are in sync. The standby database is only used in case of a > crash, which is managed by a Patroni cluster with etcd. > > > > Thanks, > > Vinay Kumar > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, 2024-09-14 at 22:49 +0530, Vinay Oli wrote: > I'm currently facing a strange issue with PostgreSQL 15.0. I have a > primary-standby setup that is in sync, with a replication slot in place. > There are 18 databases, and one of the databases on the primary side > is 104 GB, while the same database on the standby side is 216 GB. > Both are in sync with zero delay. Try and identify if any of the database objects are different in size. That shouldn't happen. If all the database objects have the same size on both systems, the explanation is likely some unrelated crap sitting in the data directory on the standby. Try to identify files that exist on one system, but not on the other. Yours, Laurenz Albe
On Sat, Sep 14, 2024 at 1:19 PM Vinay Oli <olivinay541@gmail.com> wrote:
Hi Team
I have been using PostgreSQL for the past 6 years. PostgreSQL has significantly impacted my life, providing me with great opportunities for knowledge and self-development.
I'm currently facing a strange issue with PostgreSQL 15.0.
That's two years old, and eight patch updates.
Could this be a bug? If so, has it been resolved in newer releases?
Death to ... butter sauce.
Don't boil me.
I'm still alive!
Iraq crustacean!
Hi
I've checked the database size by meta command \l+ and even I checked from file system level du -sh 49181 folder. 49181 is the db oid.
Pgwal directory is same 40gb at primary and standby servers.
All the directories are of same size, 49181 folder (oid) is only having different size.
Thanks,
Vinay kumar
On Sat, Sep 14, 2024, 10:59 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 9/14/24 10:19, Vinay Oli wrote:
> Hi Team
>
> I have been using PostgreSQL for the past 6 years. PostgreSQL has
> significantly impacted my life, providing me with great opportunities
> for knowledge and self-development.
>
> I'm currently facing a strange issue with PostgreSQL 15.0. I have a
> primary-standby setup that is in sync, with a replication slot in place.
> There are 18 databases, and one of the databases on the primary side is
> 104 GB, while the same database on the standby side is 216 GB. Both are
> in sync with zero delay.
How are you measuring the size?
If you are measuring on the files system what is the size of the base/
and pg_wal/ sub-directories in the %PG_DATA directoty?
>
> Could this be a bug? If so, has it been resolved in newer releases? If
> it is not a bug, how can this issue be fixed? Is there a solution or any
> supporting documentation available?
>
> WAL and log files are being rotated properly. The issue is with a
> database named services_mfs. On the primary cluster, the services_mfs
> database is 104GB, but on the standby cluster, it is 216GB, even though
> both cluster are in sync. The standby database is only used in case of a
> crash, which is managed by a Patroni cluster with etcd.
>
>
>
> Thanks,
>
> Vinay Kumar
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi ,
I've verified there's no crap sitting. I've checked the database size by meta command \l+ and even I checked from file system level du -sh 49181 folder. 49181 is the db oid.
Thanks,
Vinay kumar
On Sat, Sep 14, 2024, 11:00 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Sat, 2024-09-14 at 22:49 +0530, Vinay Oli wrote:
> I'm currently facing a strange issue with PostgreSQL 15.0. I have a
> primary-standby setup that is in sync, with a replication slot in place.
> There are 18 databases, and one of the databases on the primary side
> is 104 GB, while the same database on the standby side is 216 GB.
> Both are in sync with zero delay.
Try and identify if any of the database objects are different in size.
That shouldn't happen.
If all the database objects have the same size on both systems, the
explanation is likely some unrelated crap sitting in the data directory
on the standby. Try to identify files that exist on one system, but
not on the other.
Yours,
Laurenz Albe
On 9/14/24 11:10, Vinay Oli wrote: > Hi > > I've checked the database size by meta command \l+ and even I checked > from file system level du -sh 49181 folder. 49181 is the db oid. Minimal information is not going to solve this issue. Provide the exact output of commands from: 1) The Primary database. 2) The Standby database. 3) Per post from Laurenz Albe, any objects in 49181/ that differ in size between the primary and standby. Also: 1) Describe the exact type of replication you are using? 2) The replication settings you are using. 3) The exact Postgres versions on both sides. 4) The OS and version on both sides. 5) Have you checked the Postgres logs on the standby side to see if the server is showing any relevant errors? > > Pgwal directory is same 40gb at primary and standby servers. > > All the directories are of same size, 49181 folder (oid) is only > having different size. > > > > Thanks, > Vinay kumar > > On Sat, Sep 14, 2024, 10:59 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 9/14/24 10:19, Vinay Oli wrote: > > Hi Team > > > > I have been using PostgreSQL for the past 6 years. PostgreSQL has > > significantly impacted my life, providing me with great > opportunities > > for knowledge and self-development. > > > > I'm currently facing a strange issue with PostgreSQL 15.0. I have a > > primary-standby setup that is in sync, with a replication slot in > place. > > There are 18 databases, and one of the databases on the primary > side is > > 104 GB, while the same database on the standby side is 216 GB. > Both are > > in sync with zero delay. > > How are you measuring the size? > > If you are measuring on the files system what is the size of the base/ > and pg_wal/ sub-directories in the %PG_DATA directoty? > > > > > Could this be a bug? If so, has it been resolved in newer > releases? If > > it is not a bug, how can this issue be fixed? Is there a solution > or any > > supporting documentation available? > > > > WAL and log files are being rotated properly. The issue is with a > > database named services_mfs. On the primary cluster, the > services_mfs > > database is 104GB, but on the standby cluster, it is 216GB, even > though > > both cluster are in sync. The standby database is only used in > case of a > > crash, which is managed by a Patroni cluster with etcd. > > > > > > > > Thanks, > > > > Vinay Kumar > > > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com