Re: Vacuum full of parent without partitions possible?

I am thinking that you are not using all child tables all time ,so it may
not be the best solution but if you don't want to lock your active
processes, alternatively you can try to disconnect parent-child (which is
not actively in use ) relation using no inherintence then rename old child
table . After that you should create new child table with same name then
copy all data from old child table to new one , then drop old one.

2016-05-03 16:22 GMT+03:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Jan Keirse <jan(dot)keirse(at)tvh(dot)com> writes:
> > I have a table that used to contain all data.
> > because it grew too big I added a partition trigger a long time ago and
> > since than all new data was added to small partitions. By now all data in
> > the original parent table has become obsolete and was deleted, however
> the
> > disk space cannot be reclaimed without a vacuum full. The problem is, a
> > vacuum full of only the parent table should be instantaneous since it
> > contains no rows, but because the vacuum full triggers a vacuum of all
> > partitions too,
>
> No, a VACUUM on a single table processes only that table.
>
> I'm inclined to think your actual problem is that VACUUM FULL wants
> an exclusive lock and can't get one because of other traffic on the
> table. Plain VACUUM doesn't need an exclusive lock ... unless it's
> trying to truncate the relation, which in this case it presumably would
> be. Maybe your conclusion that you needed a VACUUM FULL was based
> on observing that VACUUM didn't reduce disk consumption; but if the
> table is empty, that would only be because it couldn't get exclusive
> lock.
>
> I'd suggest waiting for a low-traffic time of day and then doing a
> plain VACUUM. Or alternatively, if you're sure the table is empty
> and will stay that way, you could just cut to the chase and TRUNCATE
> it. But none of these alternatives are going to reclaim any disk
> space without taking an exclusive lock on the table, because they
> simply cannot truncate the file while other queries are scanning it.
>
> regards, tom lane
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>