Обсуждение: Why Size Of Data Backed Up Varies Significantly In SQL 6.5?

Please help!
I backed up a database at night and noted the size to be about over 300MB.
The following morning, I again backed up the same database and found out the
size to be less than 100MB. There was no massive deletes by users during
that morning.
What would account to that vast difference in size?
I'm really worried about this database because I don't understand what is
happening here.
Thank you very much
Wendy

Wendy <windy1a@yahoo.com> wrote:
>I backed up a database at night and noted the size to be about over 300MB.
>The following morning, I again backed up the same database and found out
>the size to be less than 100MB. There was no massive deletes by users
>during that morning.
>What would account to that vast difference in size?

An automatically executed script (e.g. cron job) that ran a VACUUM on the
database during the night.

>I'm really worried about this database because I don't understand what is
>happening here.

Deleted table entries still occupy disk space; VACUUMing cleans them out,
thereby shrinking the database's disk space usage.

HTH,
Ray
--
"Perhaps they spent some of the time writing the patent application. That
task was surely harder than thinking of the technique."
    RMS on Amazon's 1-Click(R) patent,
    http://linuxtoday.com/story.php3?sn=13652

Wendy writes:

> I backed up a database at night and noted the size to be about over 300MB.
> The following morning, I again backed up the same database and found out the
> size to be less than 100MB. There was no massive deletes by users during
> that morning.
> What would account to that vast difference in size?

If you have done a VACUUM in between it's possible that a lot of dead rows
were deleted.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

J.H.M. Dassen (Ray) wrote:
> Wendy <windy1a@yahoo.com> wrote:
> >I backed up a database at night and noted the size to be about over 300MB.
> >The following morning, I again backed up the same database and found out
> >the size to be less than 100MB. There was no massive deletes by users
> >during that morning.
> >What would account to that vast difference in size?
>
> An automatically executed script (e.g. cron job) that ran a VACUUM on the
> database during the night.
>
> >I'm really worried about this database because I don't understand what is
> >happening here.
>
> Deleted table entries still occupy disk space; VACUUMing cleans them out,
> thereby shrinking the database's disk space usage.

    What leads to the question if he's thinking that taking a tar
    archive from the data directory is a proper way of backing up
    a  PostgreSQL installation. You can do so, but have to ensure
    that the postmaster isn't running  while  doing  it.  And  on
    restore  you're  not  able  to  get  anything  less  than all
    database of the entire installation. Better look  at  pg_dump
    for backing up PostgreSQL.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Peter Eisentraut <peter_e@gmx.net> writes:
> Wendy writes:
>> I backed up a database at night and noted the size to be about over 300MB.
>> The following morning, I again backed up the same database and found out the
>> size to be less than 100MB. There was no massive deletes by users during
>> that morning.
>> What would account to that vast difference in size?

> If you have done a VACUUM in between it's possible that a lot of dead rows
> were deleted.

But what sort of backup is Wendy talking about?  If she's reporting the
size of pg_dump's output file then I'd be just as worried as she is.
If she's talking about a tar of the $PGDATA directory then the VACUUM
explanation holds water (and she's wasting her time making such dumps).

            regards, tom lane

On Thu, 26 Apr 2001, Wendy wrote:

> I backed up a database at night and noted the size to be about over 300MB.

Backuped with pg_dump, or dumped the raw database files ?  In the latter
case you want to stop the PostgreSQL server first.

> The following morning, I again backed up the same database and found out the
> size to be less than 100MB. There was no massive deletes by users during
> that morning.

If you dumped the raw database, this difference can be caused by VACUUM
being run automatically at night to free deleted items.

NB: personnally I recommend backuping using pg_dump or pg_dumpall. And
testing that restoration works.