Deleting schema - saving up space - PostgreSQL 9.2
Hi all,
I've got four servers:
1 - Master
2 - Slave Hot Standby (Same hardware)
3 - Slave Hot Standby (Same hardware)
4 - Slave Hot Standby (VM - Very slow machine)
On the master server, I've got a schema named "GORFS" with 80 GB, according
to this SQL:
SELECT schema_name,
pg_size_pretty(sum(table_size)::bigint),
(sum(table_size) / pg_database_size(current_database())) * 100
FROM (
SELECT pg_catalog.pg_namespace.nspname as schema_name,
pg_relation_size(pg_catalog.pg_class.oid) as table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON relnamespace =
pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name
On that schema, we have all the user's files, like Photos, notes, docs,
etc...
We're migrating it to a NFS server, taking out from the DB to save up space
and also related to performance....
*QUESTION:*
Once the migration is completed, how can I save up (remove) the schema from
the DB?
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)
2 - I think this is the most recommended option - But I've some questions
about doing this.
1. Put a slave as a new Master
2. Do a dump excluding the GORFS schema in the OLD master
3. DELETE the old DB from the old master
4. IMPORT the new dump file to the old master
5. Turn the old master into the NEW master (*What has been changed into
the slave that became a master, how can those changes be in the new master?*
)
6. Import the dump into the others slaves and make them re-sync from the
new master
Thank you.
Lucas
On Wed, Mar 16, 2016 at 1:59 PM, drum.lucas@gmail.com <drum.lucas@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 offline1. 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.
On 17 March 2016 at 10:21, David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Wed, Mar 16, 2016 at 1:59 PM, drum.lucas@gmail.com <
drum.lucas@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 offline1. 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?
On Wed, Mar 16, 2016 at 2:27 PM, drum.lucas@gmail.com <drum.lucas@gmail.com>
wrote:
On 17 March 2016 at 10:21, David G. Johnston <david.g.johnston@gmail.com>
wrote:On Wed, Mar 16, 2016 at 1:59 PM, drum.lucas@gmail.com <
drum.lucas@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 offline1. 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.
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.
Have made some tests just by doing:
1 - Creating a master server
2 - Creating a slave server
3 - Creating a table on public schema and creating a gorfs schema with
another table
4 - inserting some random data into gorfs.test (*insert into gorfs.test
(descr) values (unnest(array(select md5(random()::text) from
generate_series(1, 30000000))));*)
5 - The data has been replicated into the slave - *PASS*
6 - Dropping the schema on the master server - *PASS* (Had to use the
CASCADE option)
7 - The schema has gone on the slave server as well
8 - Checked the free space - *PASS* (*I had more free space after deleting
the schema*)
So it seems that only by doing a DROP SCHEMA will be enough :)