[GENERAL] Does it has a way to compact the database size?
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 thatit's about1209715345.
I think that something maybe wrong on my databasebecause 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 sizefor make it smallest as possibled?
NETsolutions Asia
Limited
+66 (2) 237 7247
Re: [GENERAL] Does it has a way to compact the database size?
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
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Does it has a way to compact the database size?
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
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Does it has a way to compact the database size?
Does it has a way to schedulethe process for make sure thati'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
Re: [GENERAL] Does it has a way to compact the database size?
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Does it has a way to compact the database size?
On 3/6/07, Premsun Choltanwanich [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
