Re: VACUUM FULL pg_largeobject without (much) downtime?

On Tue, 3 Feb 2015 14:48:17 -0500
Adam Hooper <adam(at)adamhooper(dot)com> wrote:

> On Tue, Feb 3, 2015 at 2:29 PM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
> > On Tue, 3 Feb 2015 14:17:03 -0500
> > Adam Hooper <adam(at)adamhooper(dot)com> wrote:
> >
> > 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.
>
> Looks exciting. But then I notice: "Slony-I does not automatically
> replicate changes to large objects (BLOBS)." [1]
>
> Does that still apply?

I'm not 100% sure, so if you decide to go this route, you'll need to
verify what I'm about to say: I'm pretty sure the key word there is
"automatically" ... meaning that in order to replicate changes to
large objects, you have to replicate the pg_largeobject table, which
means futzing about with PG's internals (since pg_largeobject is
essentially a system table) and therefore not done automatically.
That being said, I believe you can replicate large objects by
explicitly telling Slony to replicate pg_largeobject.

> It's doable for us to VACUUM FULL and add a notice to our website
> saying, "you can't upload files for the next two hours." Maybe that's
> a better idea?

It's really going to depend on what options you have available. Keep
in mind that users won't be able to read large objects either, so
you'll need to disable whatever features of the site view the files
as well. Whether that's easier or harder depends on how much work it
would be to disable those features of the site.

--
Bill Moran