Dear All,

I use PostgreSQL 8.0.3 for store my data started on Sep'06. Today, I
use database_size('name') function for checking my database size and
found that it's about 1209715345.

I think that something maybe wrong on my database because I backup my
database everyday and the backup size is about 10 MB. So I restore
databse from my backup file then use database_size('name') function
again and found that database size is about 56642193.

I can said both 1209715345 and 56642193 are coming from the same
database but I wonder that why it quiet difference on size.

Any Idea?
And How can I control or compact my database size for make it smallest
as possibled?

NETsolutions Asia Limited
+66 (2) 237 7247
http://www.nsasia.co.th

Search Discussions

  • Andreas Kretschmer at Mar 6, 2007 at 9:15 am

    am Tue, dem 06.03.2007, um 15:28:01 +0700 mailte Premsun Choltanwanich folgendes:
    Dear All,

    I use PostgreSQL 8.0.3 for store my data started on Sep'06. Today, I
    use database_size('name') function for checking my database size and
    found that it's about 1209715345.
    ...
    Any Idea?
    Yes, you need VACUUM or VACUUM FULL, regular. Check, if autovacuum runs.
    (see in the log)


    Andreas
    --
    Andreas Kretschmer
    Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
    GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
  • Richard Huxton at Mar 6, 2007 at 9:55 am

    Premsun Choltanwanich wrote:
    Dear All,

    I use PostgreSQL 8.0.3 for store my data started on Sep'06. Today, I use
    database_size('name') function for checking my database size and found that it's
    about 1209715345.

    I think that something maybe wrong on my database because I backup my database
    everyday and the backup size is about 10 MB. So I restore databse from my
    backup file then use database_size('name') function again and found that
    database size is about 56642193.

    I can said both 1209715345 and 56642193 are coming from the same database but I
    wonder that why it quiet difference on size.

    Any Idea?
    And How can I control or compact my database size for make it smallest as possibled?
    To expand on Andreas' answer.

    If you want to get your live database down to 56642193 you'll probably
    want to VACUUM FULL and REINDEX the whole database. That should
    basically get it as small as possible.

    While the database is in use, make sure you are running VACUUM often
    enough (and have free-space-map [fsm] settings high enough) to keep
    track of freed space in your database files. That way the database size
    should stay static.

    You'll never get as small as the backup file, because (1) it doesn't
    contain any indexes etc. and (2) it's compressed.

    --
    Richard Huxton
    Archonet Ltd
  • Premsun Choltanwanich at Mar 6, 2007 at 10:10 am
    Does it has a way to schedule the process for make sure that i'm running
    VACUUM often enough? How?
    (ie. create some script on PostgreSQL from running VACUUM FULL on 3.00
    AM for first date of every month.)



    Richard Huxton <dev@archonet.com> 3/6/2007 16:50 >>>
    Premsun Choltanwanich wrote:
    Dear All,

    I use PostgreSQL 8.0.3 for store my data started on Sep'06. Today, I use
    database_size('name') function for checking my database size and
    found that it's
    about 1209715345.

    I think that something maybe wrong on my database because I backup my database
    everyday and the backup size is about 10 MB. So I restore databse from my
    backup file then use database_size('name') function again and found that
    database size is about 56642193.

    I can said both 1209715345 and 56642193 are coming from the same
    database but I
    wonder that why it quiet difference on size.

    Any Idea?
    And How can I control or compact my database size for make it
    smallest as possibled?

    To expand on Andreas' answer.

    If you want to get your live database down to 56642193 you'll probably

    want to VACUUM FULL and REINDEX the whole database. That should
    basically get it as small as possible.

    While the database is in use, make sure you are running VACUUM often
    enough (and have free-space-map [fsm] settings high enough) to keep
    track of freed space in your database files. That way the database size

    should stay static.

    You'll never get as small as the backup file, because (1) it doesn't
    contain any indexes etc. and (2) it's compressed.

    --
    Richard Huxton
    Archonet Ltd

    NETsolutions Asia Limited
    +66 (2) 237 7247
    http://www.nsasia.co.th
  • Richard Huxton at Mar 6, 2007 at 10:49 am

    Premsun Choltanwanich wrote:
    Does it has a way to schedule the process for make sure that i'm running VACUUM
    often enough? How?
    (ie. create some script on PostgreSQL from running VACUUM FULL on 3.00 AM for
    first date of every month.)
    You might want to look at "autovacuum" in the contrib/ directory of the
    source, or the equivalent package for your platform. It's part of the
    core system in later versions.

    Otherwise, there's a command-line "vacuumdb" tool which you can schedule
    from cron (man 1 crontab / man 5 crontab). Depending on update activity
    you might want to run a normal vacuum daily and vacuum full weekly, or
    perhaps vacuum hourly and vacuum full overnight. You'll want to set the
    max_fsm_xxx parameters in your postgresql.conf once you have things
    running normally. There's a "verbose" option for vacuum full which can
    help suggest values for these.

    First of all though, run a vacuum full/reindex to compact everything,
    then run vacuum regularly.

    --
    Richard Huxton
    Archonet Ltd
  • Merlin Moncure at Mar 6, 2007 at 1:58 pm

    On 3/6/07, Premsun Choltanwanich wrote:
    I use PostgreSQL 8.0.3 for store my data started on Sep'06. Today, I use database_size('name') function for checking my database size and found that it's about 1209715345.

    I think that something maybe wrong on my database because I backup my database everyday and the backup size is about 10 MB. So I restore databse from my backup file then use database_size('name') function again and found that database size is about 56642193.

    I can said both 1209715345 and 56642193 are coming from the same database but I wonder that why it quiet difference on size.

    Any Idea?
    And How can I control or compact my database size for make it smallest as possibled?
    It's not a bug it's a feature...regular (non full) vacuum reclaims
    free space for the database to use but does not release it back to the
    operating system. PostgreSQL will allocate space for extra storage
    out of that 'free' space first before it asks for more from the
    operating system. I wouldn't worry about it too much unless you were
    concerned about running out of space. As long as you vacuum regularly
    (or use autovacuum), growth will moderate to the actual growth of your
    database.

    merlin

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMar 6, '07 at 9:03a
activeMar 6, '07 at 1:58p
posts6
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase