Обсуждение: drop database

Поиск
Список
Период
Сортировка

drop database

От
Julie Nishimura
Дата:
Guys, can anybody reply if they drop any postgresql database larger than 1 tb and how long did it take?

Thanks

Sv: drop database

От
Andreas Joseph Krogh
Дата:
På torsdag 17. oktober 2019 kl. 22:44:15, skrev Julie Nishimura <juliezain@hotmail.com>:
Guys, can anybody reply if they drop any postgresql database larger than 1 tb and how long did it take?
 
Thanks
 
About 280ms:
 
andreak@[local]:5432 12.0 andreak=# select pg_size_pretty(pg_database_size('rsm'));
┌────────────────┐
│ pg_size_pretty │
├────────────────┤
│ 26 GB          │
└────────────────┘
(1 row)

andreak@[local]:5432 12.0 andreak=# \timing
Timing is on.
andreak@[local]:5432 12.0 andreak=# drop DATABASE rsm;
DROP DATABASE
Time: 280,355 ms
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Вложения

Re: drop database

От
Ron
Дата:
On 10/17/19 3:44 PM, Julie Nishimura wrote:
P {margin-top:0;margin-bottom:0;}
Guys, can anybody reply if they drop any postgresql database larger than 1 tb and how long did it take?

A lot has to do with how quickly the underlying file system can delete files.

To be honest, though... does it really matter how long it takes?  (If I were worried about it -- which I might be -- then I'd put a DROP DATABASE script in crontab and run it from there.)

--
Angular momentum makes the world go 'round.

Sv: drop database

От
Andreas Joseph Krogh
Дата:
På torsdag 17. oktober 2019 kl. 22:44:15, skrev Julie Nishimura <juliezain@hotmail.com>:
Guys, can anybody reply if they drop any postgresql database larger than 1 tb and how long did it take?
 
Sorry, I missread you question as 1GB (not TB)...
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Вложения

Re: Sv: drop database

От
Ron
Дата:
On 10/17/19 3:58 PM, Andreas Joseph Krogh wrote:
På torsdag 17. oktober 2019 kl. 22:44:15, skrev Julie Nishimura <juliezain@hotmail.com>:
Guys, can anybody reply if they drop any postgresql database larger than 1 tb and how long did it take?
 
Thanks
 
About 280ms:
 
andreak@[local]:5432 12.0 andreak=# select pg_size_pretty(pg_database_size('rsm'));
┌────────────────┐
│ pg_size_pretty │
├────────────────┤
│ 26 GB          │
└────────────────┘
(1 row)

andreak@[local]:5432 12.0 andreak=# \timing
Timing is on.
andreak@[local]:5432 12.0 andreak=# drop DATABASE rsm;
DROP DATABASE
Time: 280,355 ms

1TB is 38x larger than 26GB. 


--
Angular momentum makes the world go 'round.

Re: drop database

От
Julie Nishimura
Дата:
Ive seen some performance degradation on some other RDBMS systems when "drop database" was in progress. We need to drop database which is 16 tb with minimal impact for our end users. There are 32 other databases with hundreds of connections on the same cluster, and I just want to release the space with minimal impact. Trying to find the best solution. I could even script 'truncate table' or 'drop table' in the loop if it helps. I dont have luxury to test such large db drop in action.

Thanks!



From: Ron <ronljohnsonjr@gmail.com>
Sent: Thursday, October 17, 2019 1:59 PM
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: drop database
 
On 10/17/19 3:44 PM, Julie Nishimura wrote:
Guys, can anybody reply if they drop any postgresql database larger than 1 tb and how long did it take?

A lot has to do with how quickly the underlying file system can delete files.

To be honest, though... does it really matter how long it takes?  (If I were worried about it -- which I might be -- then I'd put a DROP DATABASE script in crontab and run it from there.)

--
Angular momentum makes the world go 'round.

Re: drop database

От
Julie Nishimura
Дата:
I think you don't swallow an entire elephant at once, you can choke on it, it is more safe to be eaten piece by piece:)

From: Julie Nishimura <juliezain@hotmail.com>
Sent: Thursday, October 17, 2019 2:19 PM
To: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: drop database
 
Ive seen some performance degradation on some other RDBMS systems when "drop database" was in progress. We need to drop database which is 16 tb with minimal impact for our end users. There are 32 other databases with hundreds of connections on the same cluster, and I just want to release the space with minimal impact. Trying to find the best solution. I could even script 'truncate table' or 'drop table' in the loop if it helps. I dont have luxury to test such large db drop in action.

Thanks!



From: Ron <ronljohnsonjr@gmail.com>
Sent: Thursday, October 17, 2019 1:59 PM
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: drop database
 
On 10/17/19 3:44 PM, Julie Nishimura wrote:
Guys, can anybody reply if they drop any postgresql database larger than 1 tb and how long did it take?

A lot has to do with how quickly the underlying file system can delete files.

To be honest, though... does it really matter how long it takes?  (If I were worried about it -- which I might be -- then I'd put a DROP DATABASE script in crontab and run it from there.)

--
Angular momentum makes the world go 'round.

Re: drop database

От
Michael Lewis
Дата:
Your plan to loop over tables and truncate them seems great if you are worried. It seems simple to verify that space is being freed as you go, and also easy to change tactics if the need arises.

Re: drop database

От
Andrew Kerber
Дата:
If you are decommissioning the database, why not just rm -rf the whole system?

On Thu, Oct 17, 2019 at 4:31 PM Michael Lewis <mlewis@entrata.com> wrote:
Your plan to loop over tables and truncate them seems great if you are worried. It seems simple to verify that space is being freed as you go, and also easy to change tactics if the need arises.


--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: drop database

От
pabloa98
Дата:
Perhaps you want to TRUNCATE TABLEs. That will mitigate any I/O impact


On Thu, Oct 17, 2019 at 3:13 PM Andrew Kerber <andrew.kerber@gmail.com> wrote:
If you are decommissioning the database, why not just rm -rf the whole system?

On Thu, Oct 17, 2019 at 4:31 PM Michael Lewis <mlewis@entrata.com> wrote:
Your plan to loop over tables and truncate them seems great if you are worried. It seems simple to verify that space is being freed as you go, and also easy to change tactics if the need arises.


--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: drop database

От
Ron
Дата:
There are other databases in the cluster.

On 10/17/19 5:12 PM, Andrew Kerber wrote:
If you are decommissioning the database, why not just rm -rf the whole system?

On Thu, Oct 17, 2019 at 4:31 PM Michael Lewis <mlewis@entrata.com> wrote:
Your plan to loop over tables and truncate them seems great if you are worried. It seems simple to verify that space is being freed as you go, and also easy to change tactics if the need arises.


--
Angular momentum makes the world go 'round.