Re: releasing space

On 10/19/19 4:51 PM, Julie Nishimura wrote:
> an entire cluster
>
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> *Sent:* Saturday, October 19, 2019 4:34 PM
> *To:* Julie Nishimura <juliezain(at)hotmail(dot)com>; Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(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 10/19/19 4:17 PM, Julie Nishimura wrote:
>> 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?
>
> pg_basebackup backups an entire Postgres cluster which will be many
> databases. So when you say db do mean a Postgres cluster or an
> individual database?
>
>>
>> ------------------------------------------------------------------------
>> *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
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com