Re: Move pg_largeobject to a different tablespace *without* turning on system_table_mods.

On Wed, Oct 19, 2016 at 06:33:55PM +0200, Andreas Joseph Krogh wrote:
> På onsdag 19. oktober 2016 kl. 18:29:31, skrev Bruce Momjian <bruce(at)momjian(dot)us
> >:
>
> On Tue, Oct 18, 2016 at 04:51:54PM +0200, Andreas Joseph Krogh wrote:
> >     > 2. Being able to move pg_largeobject to a different tablespace
> >     >    *without* turning on system_table_mods. This is important for
> >     >    people storing LOTS of large-objects on separate
> >     >    disks (non-SSD) and hence in a different tablespace.
> >     > Anyone willing to discuss this?
> >     > 
> >     This was proposed a few years ago but no one cared to draft a patch.
> >
> >  
> > So that why I'm re-raising the issue:-)
> > Having "everything in the database" adds lots of benefits, conceptually
> > (follows tx-semantics, consistent backups etc.), however it's currently
> not so
> > easy in practice.
>
> Yeah, rereading that old thread was interesting, and unfortunate that no
> one mentioned the system catalog change would break pg_upgrade, though
> pg_upgrade was not popular at the time that thread was started.
>
> I think an open question is why you would not want to move the other
> system tables at the same time you move pg_largeobject.
>
>  
> The thing is that I don't understand what the problem really is. I have no
> problem moving the other system-tables as well if that fixes the problem.
> I tried moving pg_largeobject back to the same tablespace as the database but
> that too gave the error.
>  
> Are you saying that if I move all system-tables to the tablespace I moved
> pg_largeobject to it'll work? If so, is there a convenient way to move all
> system-tables to a tablespace?

Sure, use:

ALTER DATABASE name SET TABLESPACE new_tablespace

...

The fourth form changes the default tablespace of the database. Only
the database owner or a superuser can do this; you must also have
create privilege for the new tablespace. This command physically
moves any tables or indexes in the database's old default tablespace
to the new tablespace. The new default tablespace must be empty for
this database, and no one can be connected to the database. Tables
and indexes in non-default tablespaces are unaffected.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +