Обсуждение: 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
- Migrate the files to the NFS server
- Delete the schema from the MASTER DB
- Put the slaves into read-only servers
- Run Vacuum FULL into the MASTER DB
- 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.
- Put a slave as a new Master
- Do a dump excluding the GORFS schema in the OLD master
- DELETE the old DB from the old master
- IMPORT the new dump file to the old master
- 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?)
- Import the dump into the others slaves and make them re-sync from the new master
Thank you.
Lucas
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
- Migrate the files to the NFS server
- Delete the schema from the MASTER DB
- Put the slaves into read-only servers
- Run Vacuum FULL into the MASTER DB
- 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:
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
- Migrate the files to the NFS server
- Delete the schema from the MASTER DB
- Put the slaves into read-only servers
- Run Vacuum FULL into the MASTER DB
- 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 17 March 2016 at 10:21, David G. Johnston <david.g.johnston@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
- Migrate the files to the NFS server
- Delete the schema from the MASTER DB
- Put the slaves into read-only servers
- Run Vacuum FULL into the MASTER DB
- 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 :)