Thread: releasing space

releasing space

From
Julie Nishimura
Date:
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?

Thank you!

-Julie

Re: releasing space

From
Adrian Klaver
Date:
On 10/17/19 10:20 AM, 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?

https://www.postgresql.org/docs/11/sql-dropdatabase.html
"DROP DATABASE drops a database. It removes the catalog entries for the 
database and deletes the directory containing the data. It can only be 
executed by the database owner. Also, it cannot be executed while you or 
anyone else are connected to the target database. (Connect to postgres 
or any other database to issue this command.)

...

DROP DATABASE cannot be undone. Use it with care!"
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Means there is no transaction involved unlike DROP TABLE, so I would say 
it is quicker.

If you want to see all that is involved:


https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/dbcommands.c;h=f47a13d1844ca36d81ba9815f807646a44750de4;hb=86ca7f81f7dfc17f04698189dec8973d358bc711

Start at line 767


> 
> Thank you!
> 
> -Julie


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: releasing space

From
Tomas Vondra
Date:
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



Re: releasing space

From
Julie Nishimura
Date:
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.vondra@2ndquadrant.com>
Sent: Saturday, October 19, 2019 5:44 AM
To: Julie Nishimura <juliezain@hotmail.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>; pgsql-general <pgsql-general@postgresql.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

Re: releasing space

From
Adrian Klaver
Date:
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.vondra@2ndquadrant.com>
> *Sent:* Saturday, October 19, 2019 5:44 AM
> *To:* Julie Nishimura <juliezain@hotmail.com>
> *Cc:* pgsql-general@lists.postgresql.org 
> <pgsql-general@lists.postgresql.org>; pgsql-general 
> <pgsql-general@postgresql.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.klaver@aklaver.com



Re: releasing space

From
Tomas Vondra
Date:
On Sat, Oct 19, 2019 at 04:34:32PM -0700, Adrian Klaver wrote:
>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?
>

My understanding is Julie wants to create a copy of a 9.6.2 cluster
using pg_basebackup and then run 9.6.15 on it. That's OK, it's
essentially a minor version upgrade.

FWIW Julie, please don't top post - it just makes it harder to follow
the discussion. Also, this seems like a completely separate question,
unrelated to the DROP DATABLASE one. It might be better to start a new
thread instead of repurposing an existing one.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services 



Re: releasing space

From
Adrian Klaver
Date:
On 10/19/19 4:51 PM, Julie Nishimura wrote:
> an entire cluster
> 
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Saturday, October 19, 2019 4:34 PM
> *To:* Julie Nishimura <juliezain@hotmail.com>; Tomas Vondra 
> <tomas.vondra@2ndquadrant.com>
> *Cc:* pgsql-general@lists.postgresql.org 
> <pgsql-general@lists.postgresql.org>; pgsql-general 
> <pgsql-general@postgresql.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.vondra@2ndquadrant.com>
>> *Sent:* Saturday, October 19, 2019 5:44 AM
>> *To:* Julie Nishimura <juliezain@hotmail.com>
>> *Cc:* pgsql-general@lists.postgresql.org 
>> <pgsql-general@lists.postgresql.org>; pgsql-general 
>> <pgsql-general@postgresql.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.klaver@aklaver.com


-- 
Adrian Klaver
adrian.klaver@aklaver.com