Re: Database size will be Huge difference while Restoring

On 5/5/23 10:55 AM, Rajmohan Masa wrote:

Please reply to list also.
Ccing list.

> Hi Adrian,
> Are both the above the same for the original and restored database?
> No,
> *
> *
> *Backup Server Details:*
> Postgres version : *PostgreSQL 14.5*
> OS and version : *Windows server 2019 Datacenter*
>
> *Restore Server Details:*
> Postgres version : *PostgreSQL 13.2*
> OS and version : *Windows server 2012 R2 Datacenter*

From here:

https://www.postgresql.org/docs/current/app-pgdump.html

"Also, it is not guaranteed that pg_dump's output can be loaded into a
server of an older major version — not even if the dump was taken from a
server of that version. Loading a dump file into an older server may
require manual editing of the dump file to remove syntax not understood
by the older server."

> *
> *
> *_Provide the commands you used to arrive at sizes below:_

The command/process you used to determine sizes?

> *
> We are using a Windows server so By Using the PgAdmin4 tool, I'm taking
> Backup and restore.

Using what settings?

>
> *How was object count arrived at?*
>
> object count would be the same.

How was the object count determined?

>
> *Have you looked at the Postgres log to see if there might be relevant
> information?*
> Yeah,I checked with Log also but from there I didn't get anything.
>

The dump/restore should have left some trace, have you looked through
the log carefully for anything saying data was not added. In particular
the a COPY failed.

Where there any errors/warnings during the restore?

>
> Thanks& Regards,
> Rajamohan M.
>
>
> On Fri, May 5, 2023 at 9:52 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 5/5/23 08:35, Rajmohan Masa wrote:
> > Hi Team,
> > I took Postgres database Backup through Directory format with
> > compression.These are the details
>
> Postgres version?
>
> OS and version?
>
> Are both the above the same for the original and restored database?
>
> Provide the commands you used to arrive at sizes below.
>
> > Database Size: *3985 GB*
> > Backup file Size will be*212 GB.*
> > *
> > *
>
>
> Command used to restore the database?
>
> Again command used to determine size of below.
>
> > After that I Restored this backup file (212GB), but I'm getting
> Database
> > size will be 456 GB.
> >
> > So immediately I checked with *Table Bloats,latest statistics and
> last
> > vacuum status*. After that I applied "*Vacuum Full Verbose
> analyze*" and
> > " *Reindex*  " on that Database. Here I achieved Database size
> will be
> > R*educed from 3985 GB to 2582GB.*
> > *
> > *
> > After this process,again**I took  Backup through Directory format
> with
> > compression.These are the details
> > Database Size: *2582 GB*
> > Backup file Size will be* 212 GB.*
> >
> > After that I Restored this backup file (212 GB), but I'm getting
> > Database size will be 456 GB.
> >
>
> How was object count arrived at?
>
> > Here I checked with Object Counts, I'm getting the same count in
> Both Db's.
> > *_
> > _*
> > *_why am I unable to get Original Database size/Approximately nearby
> > original DB size in restore location ?_*
>
> Have you looked at the Postgres log to see if there might be relevant
> information?
>
> Where there any errors/warnings during the restore?
>
> > *_
> > _*
> > *_
> > _*
> > *_
> > _*
> > Thanks & Regards,
> > Rajamohan M.
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com