Обсуждение: releasing space
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
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
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
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
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
>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
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
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
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