pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase size different on Primary and Standby?
6+ messages / 5 participants
[nested] [flat]
* Database size different on Primary and Standby?
@ 2023-01-18 23:09 Hilbert, Karin <ioh1@psu.edu>
0 siblings, 2 replies; 6+ messages in thread
From: Hilbert, Karin @ 2023-01-18 23:09 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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?
Thanks,
Karin Hilbert
Database Administration
Pennsylvania State University
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database size different on Primary and Standby?
@ 2023-01-18 23:50 Erik Wienhold <ewie@ewie.name>
parent: Hilbert, Karin <ioh1@psu.edu>
1 sibling, 1 reply; 6+ messages in thread
From: Erik Wienhold @ 2023-01-18 23:50 UTC (permalink / raw)
To: Hilbert, Karin <ioh1@psu.edu>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> On 19/01/2023 00:09 CET Hilbert, Karin <ioh1@psu.edu> 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.
The standbys could be missing some indexes because schema changes are not
replicated and must be applied manually.
> 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?
Is template1 identical (schema and data) on primary and standby?
Could also be different page sizes. But that's a compilation option. What does
SHOW block_size say on those systems?
--
Erik
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database size different on Primary and Standby?
@ 2023-01-19 00:23 Ian Lawrence Barwick <barwick@gmail.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 6+ messages in thread
From: Ian Lawrence Barwick @ 2023-01-19 00:23 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: Hilbert, Karin <ioh1@psu.edu>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
2023年1月19日(木) 8:50 Erik Wienhold <ewie@ewie.name>:
>
> > On 19/01/2023 00:09 CET Hilbert, Karin <ioh1@psu.edu> 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.
>
> The standbys could be missing some indexes because schema changes are not
> replicated and must be applied manually.
This is incorrect; with streaming replication all changes applied on the primary
are applied on the standby.
(...)
> Could also be different page sizes. But that's a compilation option. What does
> SHOW block_size say on those systems?
It is impossible to start a standby using binaries built with a
different block size to
the primary.
Regards
Ian Barwick
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database size different on Primary and Standby?
@ 2023-01-19 00:47 Erik Wienhold <ewie@ewie.name>
parent: Ian Lawrence Barwick <barwick@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Erik Wienhold @ 2023-01-19 00:47 UTC (permalink / raw)
To: Ian Lawrence Barwick <barwick@gmail.com>; +Cc: Hilbert, Karin <ioh1@psu.edu>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> On 19/01/2023 01:23 CET Ian Lawrence Barwick <barwick@gmail.com> wrote:
>
> 2023年1月19日(木) 8:50 Erik Wienhold <ewie@ewie.name>:
> >
> > > On 19/01/2023 00:09 CET Hilbert, Karin <ioh1@psu.edu> 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.
> >
> > The standbys could be missing some indexes because schema changes are not
> > replicated and must be applied manually.
>
> This is incorrect; with streaming replication all changes applied on the primary
> are applied on the standby.
Thanks. I was thinking about logical replication.
> (...)
> > Could also be different page sizes. But that's a compilation option. What does
> > SHOW block_size say on those systems?
>
> It is impossible to start a standby using binaries built with a
> different block size to
> the primary.
Makes sense for streaming replication.
--
Erik
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database size different on Primary and Standby?
@ 2023-01-19 01:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2023-01-19 01:09 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: Ian Lawrence Barwick <barwick@gmail.com>; Hilbert, Karin <ioh1@psu.edu>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Erik Wienhold <ewie@ewie.name> writes:
> On 19/01/2023 01:23 CET Ian Lawrence Barwick <barwick@gmail.com> wrote:
>> This is incorrect; with streaming replication all changes applied on the primary
>> are applied on the standby.
> Thanks. I was thinking about logical replication.
It's not entirely clear whether the OP is talking about physical or
logical replication --- the discrepancy would sure be a lot easier
to explain if it is logical rep. In physical rep the databases
*should* be pretty much physically identical. I think though that
the amount of WAL that's being kept around could be different.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database size different on Primary and Standby?
@ 2023-01-19 02:13 Ron <ronljohnsonjr@gmail.com>
parent: Hilbert, Karin <ioh1@psu.edu>
1 sibling, 0 replies; 6+ messages in thread
From: Ron @ 2023-01-19 02:13 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-01-19 02:13 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-18 23:09 Database size different on Primary and Standby? Hilbert, Karin <ioh1@psu.edu>
2023-01-18 23:50 ` Erik Wienhold <ewie@ewie.name>
2023-01-19 00:23 ` Ian Lawrence Barwick <barwick@gmail.com>
2023-01-19 00:47 ` Erik Wienhold <ewie@ewie.name>
2023-01-19 01:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-19 02:13 ` Ron <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox