Deleting schema - saving up space - PostgreSQL 9.2

From: "drum(dot)lucas(at)gmail(dot)com" <drum(dot)lucas(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Deleting schema - saving up space - PostgreSQL 9.2
Date: 2016-03-16 20:59:04
Message-ID: CAE_gQfWzA7hzkXuK=wu3Pdn6sTFnCmtAqtXhqJC4iZJP7GfY-Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2016-03-16 21:21:28 Re: Deleting schema - saving up space - PostgreSQL 9.2
Previous Message Ranier VF 2016-03-16 20:49:31 Error: insufficient data in the message