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