Обсуждение: Vacuum Question
Hi, I use postgresql 7.2.x on Linux 2.4.18-6mdksmp #1 SMP i686 I have some big postgreSQL databases (4/5 GB at start) on this server. Every night I erase data and I import a lot of new data. For optimize my database I operate a vacuum "all" every night. The problem is that the size of database is growing every day whereas the volume of data stay remains stable. After six month my postgreSQL databases size is approximately (40/50 Gb). Yesterday I suppress the database and I restore it by pg_dump. By this way my database size is gone down again to 4/5 Gb. Is there any solution to reduce the size of the database without restore a SQL backup ? Thanks to your help Pascal PEYRE
"Pascal PEYRE" <peyre@cir.fr> writes:
> I have some big postgreSQL databases (4/5 GB at start) on this server.
> Every night I erase data and I import a lot of new data.
Exactly how do you erase the old data? If you're zapping the entire
contents of tables, TRUNCATE might be a good answer.
> For optimize my database I operate a vacuum "all" every night.
Plain vacuum, or vacuum full? If it's a plain vacuum, do you have the
free space map parameters (in postgresql.conf) set large enough to cover
your database?
> The problem is that the size of database is growing every day whereas
> the volume of data stay remains stable.
Exactly which tables are bloating? Look at the relpages column of
pg_class (just after a vacuum, so that the values are up to date).
Look to see which entries grow from day to day...
regards, tom lane
Reindex database also. Look for documentation 7.3.1 , Rounting Reindex. contrib/reindex can help you, even you have older version. Pascal PEYRE wrote: > Hi, > > I use postgresql 7.2.x on Linux 2.4.18-6mdksmp #1 SMP i686 > > I have some big postgreSQL databases (4/5 GB at start) on this server. > Every night I erase data and I import a lot of new data. > > For optimize my database I operate a vacuum "all" every night. > > The problem is that the size of database is growing every day whereas > the volume of data stay remains stable. After six month my postgreSQL > databases size is approximately (40/50 Gb). Yesterday I suppress the > database and I restore it by pg_dump. By this way my database size is > gone down again to 4/5 Gb. > > Is there any solution to reduce the size of the database without restore > a SQL backup ? > > Thanks to your help > > Pascal PEYRE > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Olleg Samoylov
Do a VACUUM FULL on your database. This should be the solution. Daniel ""Pascal PEYRE"" <peyre@cir.fr> schrieb im Newsbeitrag news:000001c2d97d$b11814c0$e3026b83@intranet.cir.fr... > Hi, > > I use postgresql 7.2.x on Linux 2.4.18-6mdksmp #1 SMP i686 > > I have some big postgreSQL databases (4/5 GB at start) on this server. > Every night I erase data and I import a lot of new data. > > For optimize my database I operate a vacuum "all" every night. > > The problem is that the size of database is growing every day whereas > the volume of data stay remains stable. After six month my postgreSQL > databases size is approximately (40/50 Gb). Yesterday I suppress the > database and I restore it by pg_dump. By this way my database size is > gone down again to 4/5 Gb. > > Is there any solution to reduce the size of the database without restore > a SQL backup ? > > Thanks to your help > > Pascal PEYRE > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster