On Thu, Oct 17, 2019 at 05:20:09PM +0000, Julie Nishimura wrote:
Hello everybody, We are running PostgreSQL 9.6.2 cluster master -> standby (streaming replication). 22 tb of space (constantly struggling with the space, pruning the old data, but not fast enough). The biggest db takes 16 tb. So, we've copied it to another server, and now we would like to delete it from our original source, to free up the space. What would be the right approach for this? Just issue drop database command (16tb). How long it might take? Should we do it gradually (drop biggest tables first)? Any suggestions? Caveats?
Generally speaking, DROP DATABASE simply recursively drops all the various objects - indexes, tables, etc. It mostly just deleting the files, which should not be very expensive (we certainly don't need to delete all the data or anything), but there's certain number of I/O involved. But it does depend on the OS / filesystem / hardware if that's an issue. So if you want to be on the safe side, you can drop the objects one by one, with a bit of delay between them, to throttle the I/O a bit. FWIW the latest minor release for 9.6 is 9.6.15, you're 13 minor versions (~30 months) of fixes behind. You might want to consider upgrading ... -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
