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