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:
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
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
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
Import Notes
Reply to msg id not found: BYAPR08MB5014A1BF2F44D1A798BBAF7BAC6F0@BYAPR08MB5014.namprd08.prod.outlook.com