On Wed, Mar 16, 2016 at 2:27 PM, drum.lu...@gmail.com <drum.lu...@gmail.com> wrote:
> > > On 17 March 2016 at 10:21, David G. Johnston <david.g.johns...@gmail.com> > wrote: > >> On Wed, Mar 16, 2016 at 1:59 PM, drum.lu...@gmail.com < >> drum.lu...@gmail.com> wrote: >> >>> >>> 1 - The problem here is that a VACUUM FULL will lock all the DB to >>> wirte, am I right? My DB is 1.7 TB, so it will take a while and the System >>> can't be offline >>> >>> 1. Migrate the files to the NFS server >>> 2. Delete the schema from the MASTER DB >>> 3. Put the slaves into read-only servers >>> 4. Run Vacuum FULL into the MASTER DB >>> 5. Once the vacuum is done, do a DUMP from the MASTER to the slaves >>> (excluding the GORFS schema of course) >>> >>> >> If you are removing the entire object there should be no cause to VACUUM >> FULL. A vacuum full reclaims unused space *within a given relation.* >> >> Both DROP TABLE and TRUNCATE have the effect of (near) immediately >> freeing up the disk spaced used by the named table and returning it to the >> operating system. >> >> You want to use VACUUM FULL tablename; if you remove a significant chuck >> of a table using DELETE or UPDATE and want to reclaim the spaced that was >> occupied by the older version of the row within "tablename". >> >> VACUUM FULL; simply does this for all tables - I'm not sure when locks >> are taken and removed. likely only the actively worked on tables are >> locked - but the I/O hit is global so targeted locking only buys you so >> much. >> >> David J. >> >> >> > > I see.. > > so in your opinion a DROP SCHEMA and maybe a VACUUM (not full) would be > enough? > > I don't deal with Hot Standby's in my day-to-day but if you DROP SCHEMA all of the spaced consumed by indexes and tables in that schema will be freed. The vacuum might make a small difference in performance on the system catalogs (pg_class, stats, etc) that were updated but with respect to the dropped schema there won't be anything present there for vacuum to touch. Create and populate a dummy table in a test setup, measure the HD space taken in PGDATA, then drop it and measure again to see it in action. I've only done this using "TRUNCATE" - I've got a system with space constraints a the same kind of "file data" table and freed up around 20GB with a single fast truncate (though ensuring FKs wouldn't be a problem was fun...). David J.
