Re: moving system catalogs to another tablespace

Hi,

it seems like we can't do this. At least a get this error:

db=# alter table pg_largeobject set tablespace otro;
ERROR: permission denied: "pg_largeobject" is a system catalog

but pg_largeobject seems sensible to move to another table space for
space considerations, no? are there any reasons for this?
i guess i still could this with symlinks, no?

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> writes:
> it seems like we can't do this. At least a get this error:

> db=# alter table pg_largeobject set tablespace otro;
> ERROR: permission denied: "pg_largeobject" is a system catalog

You can move *all* of the system catalogs with ALTER DATABASE SET
TABLESPACE. pg_largeobject might be a special case, but in general
I would think there's no use-case for moving individual catalogs.

regards, tom lane

Jaime Casanova escreveu:
> it seems like we can't do this. At least a get this error:
>
> db=# alter table pg_largeobject set tablespace otro;
> ERROR: permission denied: "pg_largeobject" is a system catalog
>
> but pg_largeobject seems sensible to move to another table space for
> space considerations, no? are there any reasons for this?
> i guess i still could this with symlinks, no?
>
This was discussed some time ago [1]. A possible solution was proposed in [2]
(it's in pt-br but you can check the commands to accomplish your goal).

[1] http://archives.postgresql.org/pgsql-hackers/2004-06/msg00835.php
[2] http://listas.postgresql.org.br/pipermail/pgbr-geral/2009-March/014374.html

--
Euler Taveira de Oliveira
http://www.timbira.com/

On Mon, Oct 5, 2009 at 7:15 PM, Euler Taveira de Oliveira
<euler(at)timbira(dot)com> wrote:
>>
>> db=# alter table pg_largeobject set tablespace otro;
>> ERROR:  permission denied: "pg_largeobject" is a system catalog
>>
>
> [1] http://archives.postgresql.org/pgsql-hackers/2004-06/msg00835.php

seems like the original idea was to forbid this in all system catalogs
except pg_largeobject, what happen then?

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> writes:
> seems like the original idea was to forbid this in all system catalogs
> except pg_largeobject, what happen then?

Nothing ... nobody got around to doing anything about it.

regards, tom lane

On Mon, Oct 5, 2009 at 10:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> writes:
>> seems like the original idea was to forbid this in all system catalogs
>> except pg_largeobject, what happen then?
>
> Nothing ... nobody got around to doing anything about it.
>

ah! well, having slept a while my thinking is a little bit more sane...
now i think that what Euler shows me [1] is a fair compromise (this is
to allow this only when in standalone mode with system catalogs
allowed) otherwise we will have diferent behaviour for specific
(random) catalogs...

[1] http://listas.postgresql.org.br/pipermail/pgbr-geral/2009-March/014374.html

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova wrote:

> now i think that what Euler shows me [1] is a fair compromise (this is
> to allow this only when in standalone mode with system catalogs
> allowed) otherwise we will have diferent behaviour for specific
> (random) catalogs...
>
> [1] http://listas.postgresql.org.br/pipermail/pgbr-geral/2009-March/014374.html

Hmm, I don't necessarily agree that having the system effectively shut
down for the duration of the pg_largeobject move is a good idea.

I don't agree that pg_largeobject is a random catalog either -- it is,
in fact, the only catalog in which an interesting size is to be
expected.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> I don't agree that pg_largeobject is a random catalog either -- it is,
> in fact, the only catalog in which an interesting size is to be
> expected.

Yeah, I have sometimes thought that pg_largeobject shouldn't be
considered a system catalog at all. It's more nearly like a toast
table, ie, it's storing "out of line" user data.

This has some interesting connections with the proposed changes
for associating privilege data with large objects. The proposed
"meta" table would certainly qualify as a system catalog still.
Would there be any sense in redefining pg_largeobject as an actual
toast table attached to that catalog? Probably not, or at least
it wouldn't directly contribute to solving Jaime's problem.
But it seems like now would be a good time to think outside the
box a little bit about where we want to go with pg_largeobject.

regards, tom lane

On Tue, Oct 6, 2009 at 9:43 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Jaime Casanova wrote:
>
>> now i think that what Euler shows me [1] is a fair compromise (this is
>> to allow this only when in standalone mode with system catalogs
>> allowed) otherwise we will have diferent behaviour for specific
>> (random) catalogs...
>>
>> [1] http://listas.postgresql.org.br/pipermail/pgbr-geral/2009-March/014374.html
>
> Hmm, I don't necessarily agree that having the system effectively shut
> down for the duration of the pg_largeobject move is a good idea.
>

well, my thinking was that if you know how to start in standalone and
know to allow system catalogs changes is more probable you did your
homework and read about the dangers it implies in other catalogs...

but yeah! the size of the pg_largeobject could be large enough to make
this something to worry about... let me ask the opinion of the bottle
of coke that is supporting me...

> I don't agree that pg_largeobject is a random catalog either -- it is,
> in fact, the only catalog in which an interesting size is to be
> expected.
>

i have just read Tom's comments and yes that question was around my
mind to: a system catalog that doesn't behaves like other system
catalogs and in which we want different sets of permissions (see
kaigai san's patch about largeobject controls in which he actually add
syntax for row level permission in that catalog, something we don't
have in any other place yet) is really a system catalog?

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova escreveu:
> i have just read Tom's comments and yes that question was around my
> mind to: a system catalog that doesn't behaves like other system
> catalogs and in which we want different sets of permissions (see
> kaigai san's patch about largeobject controls in which he actually add
> syntax for row level permission in that catalog, something we don't
> have in any other place yet) is really a system catalog?
>
IMHO it's hibrid (catalog and regular table). That' why people proposed the
SET TABLESPACE and ACL.

--
Euler Taveira de Oliveira
http://www.timbira.com/

Hi all,

On Tue, 2009-10-06 at 16:58 +0200, Tom Lane wrote:
> Yeah, I have sometimes thought that pg_largeobject shouldn't be
> considered a system catalog at all. It's more nearly like a toast
> table, ie, it's storing "out of line" user data.

pg_largeobject in it's current form has serious limitations, the biggest
one is that it can't have triggers, and thus it can't be replicated by
trigger based replication like slony.

I ended up rolling my own large object table, modeling exactly the
behavior of pg_largeobject but on the client side, except I can
replicate it... and a few other simple things like easily duplicating an
entry from client side code, and easier control of the large object ID
ranges - BTW, OID is not the best data type for a client visible primary
key, then better BIGINT, oid is unsigned and in Java for example won't
cleanly map to any data type (java long is twice as big as needed and
int is signed and won't work for all OID values - we finally had to use
long, but then BIGINT is a better match). Considering that the postgres
manual says: "using a user-created table's OID column as a primary key
is discouraged", I don't see why use OID as the primary key for a table
which can potentially outgrow the OID range.

The backup is also not a special case now, it just dumps the table. I
don't know what were the reasons of special casing pg_largeobject, but
from a usability POV is fairly bad.

Cheers,
Csaba.

Nothing like replying to a 3 year old post, but I would like to confirm
whether this is possible. I have a 8.4 postgres database that was originally
designed (not by me) to store a lot of BLOBS and CLOBS. We're well past the
point of doing a VACUUM FULL given the 400GB size of the thing, and are in
the process of walking all the rows that have BLOB or TEXT columns and are
copying them out to a filesystem path, and were updating a newly-added
"path" column with where that path is. Unfortunately, it appears as the
pg_largeobject table is growing commensurate with what we're looking at. I
ran a VACUUM (not FULL), which took about 14 hours and indeed finished.

I was hoping to alter the entire database to move the tablespace to a
newly-attached 2TB drive, as we actually got close to where unexpected
memory swap increases might have have failed. Even if I tried "ALTER
DATABASE postgres SET TABLESPACE system;" seem to not work, and of course, I
can't omit the "postgres" name in that sentence either.

Can you actually move the entire system catalogs? I only really care about
public.pg_largeobject, but they can all go as a unit.

If this is "Yeah, was only possible with version 7", then that's cool too.
We're now "rsync -aP"-ing the data directory, and will successively do it
again with a full "pg_ctl stop -D data" preceding it, then will bring it
back up again with -D modified, but I'd really like to know if one could
move the system catalogs et al as you originally mentioned.

Many thanks.
./scc

--
View this message in context: http://postgresql.1045698.n5.nabble.com/moving-system-catalogs-to-another-tablespace-tp2014761p5726202.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Mon, Oct 1, 2012 at 3:08 PM, scc <scott(at)corscadden(dot)ca> wrote:
> Nothing like replying to a 3 year old post, but I would like to confirm
> whether this is possible.

I haven't tried this in a long time but AFAIR this is possible by
shutting down the server, start in standalone mode with change in
catalogs allowed (postgres --single -O -D /data) and then you can make
the ALTER TABLE to move pg_largeobject to a new tablespace

--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157

Thanks so much - sounds like I have a backup option if plan A fails.

Do you or any others here foresee any issues with me doing a database stop
(i.e., "pg_ctl stop -D data") followed by one more "rsync -aP" to true up
the directory on the external drive, with finally a "pg_ctl -D
/path-to-attached-drive"?

./scc

--
View this message in context: http://postgresql.1045698.n5.nabble.com/moving-system-catalogs-to-another-tablespace-tp2014761p5726208.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Never mind - the stop/rsync/change -D/restart completely worked. Whew.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/moving-system-catalogs-to-another-tablespace-tp2014761p5726241.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.