Обсуждение: Database size will be Huge difference while Restoring

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

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

On 5/5/23 10:55 AM, Rajmohan Masa wrote:
> Hi Adrian,
> Are both the above the same for the original and restored database?

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

 From here

https://www.pgadmin.org/docs/pgadmin4/7.1/restore_dialog.html

you might try

Under Options --> Miscellaneous/Behavior turn on Exit on error.


That may help determine what is going on.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 5/22/23 04:38, Rajmohan Masa wrote:
> Hi Adrian,
> 
> I Found one thing in my base Directory.
> 
> Generally we are having OID in the Base Directory with a unique OID But 
> in my machine I found some object Id's with sequence like 
> 121193051,121193051.1 ....121193051.1200 and each file having the same 
> size (1GB) and same time.
> 
> By using the *pg_class* catalog, I tried to check those object names 
> with OID's but I'm unable to get any objects with those id's.
> 
> I applied Vacuum, Vacuum Full On that particular Database but the size 
> is still the same on these objects.
> 
> *My questions are,*
> 
> 1. Why object id's (OID) having like this 
> (121193051,121193051.1,121193051.2,121193051.3,121193051.4...........121193051.1200) with the same size?
> 2. Why are these oid's not having any objects ?
> 3. I think maybe  someone deleted/dropped these objects but why do these 
> objects occupy the same size even if I applied *Vacuum / vacuum full.?*


It all explained here:

https://www.postgresql.org/docs/current/storage-file-layout.html

In particular:


Caution

Note that while a table's filenode often matches its OID, this is not 
necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER 
and some forms of ALTER TABLE, can change the filenode while preserving 
the OID. Avoid assuming that filenode and table OID are the same. Also, 
for certain system catalogs including pg_class itself, 
pg_class.relfilenode contains zero. The actual filenode number of these 
catalogs is stored in a lower-level data structure, and can be obtained 
using the pg_relation_filenode() function.


> *
> Here I'm attaching Document, please check for your reference.
> 
> 
> Thanks & Regards,
> Rajamohan Masa
> *
> *


-- 
Adrian Klaver
adrian.klaver@aklaver.com