Thread: Recommended VACUM timing...

Recommended VACUM timing...

From
"August Zajonc"
Date:
For large tables that require a 24/7 uptime and see around 6-10 million
changes a day, how do I run VACUM without taking the database/table down.
Considering moving from MySQL, but can't quite understand how to run
PostgreSQL in a production enviroment properly.

August




Re: Recommended VACUM timing...

From
David Lizano
Date:
>For large tables that require a 24/7 uptime and see around 6-10 million
>changes a day, how do I run VACUM without taking the database/table down.
>Considering moving from MySQL, but can't quite understand how to run
>PostgreSQL in a production enviroment properly.


a) Using replication, you can "vacuum" one server meanwhile the other is
working.

         There are two utilities for PostgreSQL v7.1 (eRServer and RServ
v0.1), but I don't know the level of maturity of this utilities. I suppose
that they aren't in production level.

b)      1.- Stop the postmaster daemon
         2.- Make a copy of the database files directly from the filesistem.
         3.- start the postmaster daemon.

         The second should stop the service a short time compared with
"vacuumdb".


RE: Recommended VACUM timing...

From
Matthew
Date:
> >For large tables that require a 24/7 uptime and see around 6-10 million
> >changes a day, how do I run VACUM without taking the database/table down.
> >Considering moving from MySQL, but can't quite understand how to run
> >PostgreSQL in a production enviroment properly.
>
>
> a) Using replication, you can "vacuum" one server meanwhile the other is
> working.
>
>          There are two utilities for PostgreSQL v7.1 (eRServer and RServ
> v0.1), but I don't know the level of maturity of this utilities. I suppose
>
> that they aren't in production level.
>
> b)      1.- Stop the postmaster daemon
>          2.- Make a copy of the database files directly from the
> filesistem.
>          3.- start the postmaster daemon.
>
>          The second should stop the service a short time compared with
> "vacuumdb".
>
    vacuumdb does not stop the database. The only potential problem is
that some queries will get held up on the a lock while the vacuum is going
on, but the locks are only one table at a time.  option b presented above
does not address vacuum issues, it only addresses backup issues which was
not the question at hand.  If his database is getting 6-10 million changes a
day there is a lot of space he needs to reclaim.