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.
FAQ