The postgres database -- necessary? If so, how to cleanse?
I accidentally imported lots of data into the postgres database via something like:
psql -U postgres -q -f super_massive_database_dump.sql
Now, although I've manually dropped all the tables -- and \d+ shows nothing -- there are still various views, functions, etc that must account for a lot of disk space. From \l+, I see that postgres still occupies 65GB. So my question is, what's the easiest way to reclaim that space? Can I drop postgres and just recreate it? Or is it magical like unicorns and unwilling to be dropped? Or, how can I just clear out all the crap I imported?
Thanks!
…Rene
=?iso-8859-1?Q?Ren=E9_Fournier?= <m5@renefournier.com> writes:
I accidentally imported lots of data into the postgres database via something like:
psql -U postgres -q -f super_massive_database_dump.sql
Now, although I've manually dropped all the tables -- and \d+ shows
nothing -- there are still various views, functions, etc that must
account for a lot of disk space. From \l+, I see that postgres still
occupies 65GB.
[ raised eyebrow ... ] It's pretty hard to conceive of system catalogs
occupying 65GB. You might want to look into where that number is coming
from. (Large objects maybe?)
So my question is, what's the easiest way to reclaim
that space? Can I drop postgres and just recreate it?
Yeah, if you want.
regards, tom lane
OK, great. Thank. And when I recreate it, should I do so from a template, or just CREATE DATABASE postgres ?
On 2011-10-26, at 4:10 PM, Tom Lane wrote:
Show quoted text
=?iso-8859-1?Q?Ren=E9_Fournier?= <m5@renefournier.com> writes:
I accidentally imported lots of data into the postgres database via something like:
psql -U postgres -q -f super_massive_database_dump.sqlNow, although I've manually dropped all the tables -- and \d+ shows
nothing -- there are still various views, functions, etc that must
account for a lot of disk space. From \l+, I see that postgres still
occupies 65GB.[ raised eyebrow ... ] It's pretty hard to conceive of system catalogs
occupying 65GB. You might want to look into where that number is coming
from. (Large objects maybe?)So my question is, what's the easiest way to reclaim
that space? Can I drop postgres and just recreate it?Yeah, if you want.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 26/10/2011 23:14, Ren� Fournier wrote:
OK, great. Thank. And when I recreate it, should I do so from a template, or just CREATE DATABASE postgres ?
AIUI, CREATE DATABASE always uses a template - if you don't specify one,
it uses template1 (I think).
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie