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.
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 "DROP SCHEMA temp_orig CASCADE" if the schema is almost 25 tb?
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.
Any advice is appreciated.
Thanks a lot!
