Thread: Move pg_largeobject to a different tablespace *without* turning on system_table_mods.
Hi -hackers.
From time to time pg_largeobject comes up as an issue with being implemented as a system-catalog.
As I see it, there are 2 relevant use-cases for improving the situation:
1. Being able to pg_dump *without* any LOs (think of it as
without the contents of pg_largeobject). This is very handy
for testing/troubleshooting.
without the contents of pg_largeobject). This is very handy
for testing/troubleshooting.
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.
*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?
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment
På tirsdag 18. oktober 2016 kl. 16:26:37, skrev Euler Taveira <euler@timbira.com.br>:
On 18-10-2016 10:13, Andreas Joseph Krogh wrote:
> From time to time pg_largeobject comes up as an issue with being
> implemented as a system-catalog.
>
Did you read the archives [1]?
Yes..
> As I see it, there are 2 relevant use-cases for improving the situation:
> 1. Being able to pg_dump *without* any LOs (think of it as
> without the contents of pg_largeobject). This is very handy
> for testing/troubleshooting.
>
It could be an option (--no-blobs). The -b option has a limited use case.
Yes, it definitely should be an option to pg_dump. I guess because of pg_largeobject being a system-catalog it adds additional difficulties implementing it?
> 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.
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment
På onsdag 19. oktober 2016 kl. 18:29:31, skrev Bruce Momjian <bruce@momjian.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?
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment
På onsdag 19. oktober 2016 kl. 18:42:11, skrev Bruce Momjian <bruce@momjian.us>:
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@momjian.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.
The thing is; I've created the database with explicit tablespace, like this:
createdb --tablespace=mydb -O andreak mydb
Then I've moved pg_largeobject:
alter table pg_largeobject set tablespace mydb_lo
What options do I now have to make pg_upgrade work? I have 6TB db which I'd like to upgrade to 9.6 using pg_upgrade so any help accomplishing that is greatly appreciated:-)
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment
På onsdag 19. oktober 2016 kl. 18:44:24, skrev Tom Lane <tgl@sss.pgh.pa.us>:
Andreas Joseph Krogh <andreas@visena.com> writes:
> På onsdag 19. oktober 2016 kl. 18:29:31, skrev Bruce Momjian <bruce@momjian.us
> 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.
> 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?
Not sure about moving them after the fact, but you could create the
database with its default tablespace being the one you want pg_largeobject
in.
I think though that there's a fairly clear counterexample to Bruce's
question: if you're worried about moving pg_largeobject at all, you
probably are trying to put it on a relatively large and slow storage
device. You don't necessarily want all the system catalogs there.
regards, tom lane
Thanks for the tip. How do I conveniently move all the tables/indexes/sequences etc. (basically everything in schema=public) except the system-tables to another tablespace?
I don't see any "ALTER SCHEMA public SET TABLESPACE myspace" command...
This is great when dealing with new databases, but do you have any hints helping me out getting pg_upgrade working now that I already have moved pg_largeobject (see my answer to Bruce)?
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963