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.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com