Re: releasing space

Thank you, Thomas. Do you know if it is safe to replicate 9.6.2 (smaller) db to 9.6.15 (larger capacity) using pg_basebackup? Would it be considered as an upgrade?

________________________________
From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
Sent: Saturday, October 19, 2019 5:44 AM
To: Julie Nishimura <juliezain(at)hotmail(dot)com>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>; pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: releasing space

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