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

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]/messages/by-id/3073cc9b0910051618t693d15f3u265872908240d306@mail.gmail.com?

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.

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.

[1]: /messages/by-id/3073cc9b0910051618t693d15f3u265872908240d306@mail.gmail.com
/messages/by-id/3073cc9b0910051618t693d15f3u265872908240d306@mail.gmail.com

--
Euler Taveira Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

På tirsdag 18. oktober 2016 kl. 16:26:37, skrev Euler Taveira <
euler@timbira.com.br <mailto: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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

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.

--
Bruce Momjian <bruce@momjian.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 +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

På onsdag 19. oktober 2016 kl. 18:29:31, skrev Bruce Momjian <bruce@momjian.us
<mailto: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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

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.

--
Bruce Momjian <bruce@momjian.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 +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Oct 19, 2016 at 9:29 AM, Bruce Momjian <bruce@momjian.us> wrote:

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.

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.

​I think the theory of having all system tables except LO on SSD storage,
and having LO on a less performant device, makes sense.

David J.​

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

På onsdag 19. oktober 2016 kl. 18:42:11, skrev Bruce Momjian <bruce@momjian.us
<mailto: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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

På onsdag 19. oktober 2016 kl. 18:44:24, skrev Tom Lane <tgl@sss.pgh.pa.us
<mailto: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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

On Wed, Oct 19, 2016 at 09:44:05AM -0700, David G. Johnston wrote:

On Wed, Oct 19, 2016 at 9:29 AM, Bruce Momjian <bruce@momjian.us> wrote:

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.

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.

​I think the theory of having all system tables except LO on SSD storage, and
having LO on a less performant device, makes sense.

OK, so is this a TODO item?

--
Bruce Momjian <bruce@momjian.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 +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Bruce Momjian <bruce@momjian.us> writes:

On Wed, Oct 19, 2016 at 09:44:05AM -0700, David G. Johnston wrote:

​I think the theory of having all system tables except LO on SSD storage, and
having LO on a less performant device, makes sense.

OK, so is this a TODO item?

According to
/messages/by-id/200407110311.i6B3BBW24899@candle.pha.pa.us
it already is ;-)

Personally though I'd narrow the scope to just consider pg_largeobject
(and maybe pg_largeobject_metadata). I see no use-case for moving other
catalogs out of the DB's default tablespace, and doing so would create a
large space of poorly-tested opportunities for failure.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Oct 19, 2016 at 01:25:33PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

On Wed, Oct 19, 2016 at 09:44:05AM -0700, David G. Johnston wrote:

​I think the theory of having all system tables except LO on SSD storage, and
having LO on a less performant device, makes sense.

OK, so is this a TODO item?

According to
/messages/by-id/200407110311.i6B3BBW24899@candle.pha.pa.us
it already is ;-)

Personally though I'd narrow the scope to just consider pg_largeobject
(and maybe pg_largeobject_metadata). I see no use-case for moving other
catalogs out of the DB's default tablespace, and doing so would create a
large space of poorly-tested opportunities for failure.

Ah, I see it now:

Allow toast tables to be moved to a different tablespace

moving toast table to its own tablespace
patch : Allow toast tables to be moved to a different tablespace

--
Bruce Momjian <bruce@momjian.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 +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers