Re: is it safe to drop 25 tb schema with cascade option?

On 9/19/19 12:06 PM, Julie Nishimura wrote:
> Hello, we've recently inherited large Greenplum system (master with 
> standby and 8 segment nodes), which is running old version of GP:
> 
> 20190919:15:22:01:122002 gpstate:hitw-mck-gp1-mdw-1:gpadmin-[INFO]:-   
> Greenplum initsystem version   = 4.3.4.0 build 1
> 20190919:15:22:01:122002 gpstate:hitw-mck-gp1-mdw-1:gpadmin-[INFO]:-   
> Greenplum current version      = PostgreSQL 8.2.15 (Greenplum
>   Database 4.3.8.1 build 1) on x86_64-unknown-linux-gnu, compiled by GCC 
> gcc (GCC) 4.4.2 compiled on Apr 20 2016 08:08:56
> 20190919:15:22:01:122002 gpstate:hitw-mck-gp1-mdw-1:gpadmin-[INFO]:-   
> Postgres version               = 8.2.15
> 
> If I scan logs, for the last 6 months I see the following warning after 
> every transaction:
> 04 UTC,0,con9059926,cmd1,seg-1,,,,sx1,"WARNING","01000","database 
> ""my_db_name"" must be vacuumed within 1607900488 transactions",,"To 
> avoid a database shutdown, execute a full-database VACUUM in 
> ""my_db_name"".",,,,"set client_encoding to 'SQL_ASCII'",0,,"varsup.c",109,
> 
> The database "my_db_name" is 32 TB. According to the crontab logs, we 
> run VACUUM on pg_catalog every day (while the system is online). Should 
> I try to run VACUUM FULL on pg_catalog first, or I need to run VACUUM on 
> the entire "my_db_name"? I am not sure what I should try first.

The vacuum warning is about transaction id wrap around:

https://www.postgresql.org/docs/8.2/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

You will need to vacuum more then just pg_catalog. You will need to do 
what the message says, vacuum the entire database.

> 
> For the full picture: the largest schema on "my_db_name" was "temp", it 
> was consuming about 25 tb. So what we did - we renamed this schema to 
> "temp_orig", and created brand new schema "temp" (to make drop objects 
> from temp_orig easier and isolated). However, I was hesitating to drop 
> the entire schema that big in one transaction, and started dropping 
> tables from "temp_orig", however, there are millions of objects in that 
> schema, and as a result, number of "drop table" transactions are very 
> high. How safe is it to run "DROPSCHEMAtemp_orig CASCADE" if the schema 
> is almost 25 tb?

Not sure.

> 
> We are running out of space very quickly. we have only 5% left on a device
> 
> Last time when we dropped millions of objects from that old schema, we 
> were able to free up some space, but this time around even though I am 
> running a lot of "drop tables", the space temporarily goes down 
> (according to df -h), then it goes back again, even faster than I am 
> freeing it up. Which makes me believe the system catalog is bloated now.

Probably due to all the other operations hitting the database.

Have you tried vacuuming the system catalogs?

> 
> Any advice is appreciated.
> 
> Thanks a lot!
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com