From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Rajmohan Masa <razmohan6@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Database size will be Huge difference while Restoring
Date: Fri, 5 May 2023 11:06:41 -0700
Message-ID: <4e424783-9bb5-442f-5b8a-de26f35b72db@aklaver.com> (raw)
In-Reply-To: <CAMD-G95rZZ0rGs-WPrneTLcKZ6BNc8+BA2+FmEfqHV1OMb1gDA@mail.gmail.com>
References: <CAMD-G97NzB66a-0ZQZUQqY8pAkuuk2f9aJT=iihbrQd=3Ye0og@mail.gmail.com>
<58266925-de1e-69b5-1d72-73605ed3838a@aklaver.com>
<CAMD-G95rZZ0rGs-WPrneTLcKZ6BNc8+BA2+FmEfqHV1OMb1gDA@mail.gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, razmohan6@gmail.com
Subject: Re: Database size will be Huge difference while Restoring
In-Reply-To: <4e424783-9bb5-442f-5b8a-de26f35b72db@aklaver.com>
References: <CAMD-G97NzB66a-0ZQZUQqY8pAkuuk2f9aJT=iihbrQd=3Ye0og@mail.gmail.com> <58266925-de1e-69b5-1d72-73605ed3838a@aklaver.com> <CAMD-G95rZZ0rGs-WPrneTLcKZ6BNc8+BA2+FmEfqHV1OMb1gDA@mail.gmail.com> <4e424783-9bb5-442f-5b8a-de26f35b72db@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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