Re: releasing space

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(dot)klaver(at)aklaver(dot)com