Recommended VACUM timing...
Recommended VACUM timing...
От:
"August Zajonc" <junk-postgre@aontic.com>
Дата:
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...
От:
David Lizano <david.lizano@izanet.com>
Дата:
>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...
От:
Matthew <matt@ctlno.com>
Дата:
> >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.