Re: Deleting schema - saving up space - PostgreSQL 9.2

On Wed, Mar 16, 2016 at 1:59 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)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.