Thread: 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
Re: Why Size Of Data Backed Up Varies Significantly In SQL 6.5?
From
jdassen@cistron.nl (J.H.M. Dassen (Ray))
Date:
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.