On Tue, 3 Feb 2015 14:17:03 -0500
Adam Hooper <adam@adamhooper.com> wrote:
> On Tue, Feb 3, 2015 at 12:58 PM, Bill Moran <wmoran@potentialtech.com> wrote:
> > On Tue, 3 Feb 2015 10:53:11 -0500
> > Adam Hooper <adam@adamhooper.com> wrote:
> >
> >> This plan won't work: Step 2 will be too slow because pg_largeobject
> >> still takes 266GB. We tested `VACUUM FULL pg_largeobject` on our
> >> staging database: it took two hours, during which pg_largeobject was
> >> locked. When pg_largeobject is locked, lots of our website doesn't
> >> work.
> >
> > Sometimes CLUSTER is faster than VACUUM FULL ... have you tested CLUSTERing
> > of pg_largeobject on your test system to see if it's fast enough?
>
> On the 30GB that's left on staging, it takes 50min. Unfortunately, our
> staging database is now at 30GB because we already completed a VACUUM
> FULL on it. It seems difficult to me to revert that operation. But I
> need an orders-of-magnitude difference, and this clearly isn't it.
It was worth a shot.
> > How big is the non-lo data?
>
> It's 65GB, but I've used pg_repack to move it to a separate tablespace
> so it won't affect downtime.
My recommendation here would be to use Slony to replicate the data to a
new server, then switch to the new server once the data has synchornized.
Since slony replicates transactions and not the on-disk data structures,
the new database will be pretty compact. Since Slony allows you to set it
up and synchronize it without interrupting normal operation, you can
do all this without downtime (although I highly recommend that you do it
during a slow period). The final switchover should take less than a minute.
Since Slony replicates at the table level, you can decide to make changes
as part of the process and possibly not replicate the large objects at all.
Slony can be a bit intimidating to set up if you've never used it before,
but it's a very poweful tool. I've used it successfully for many years to
do a lot of things such as this.
--
Bill Moran