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

Search Discussions

  • Tom Lane at Oct 5, 2009 at 11:24 pm

    Jaime Casanova 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
  • Scc at Oct 1, 2012 at 8:08 pm
    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.
  • Jaime Casanova at Oct 1, 2012 at 8:36 pm

    On Mon, Oct 1, 2012 at 3:08 PM, scc 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
  • Scc at Oct 1, 2012 at 8:50 pm
    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.
  • Scc at Oct 2, 2012 at 3:08 am
    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.
  • Euler Taveira de Oliveira at Oct 6, 2009 at 12:16 am

    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/
  • Jaime Casanova at Oct 6, 2009 at 3:09 am

    On Mon, Oct 5, 2009 at 7:15 PM, Euler Taveira de Oliveira 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
  • Tom Lane at Oct 6, 2009 at 3:27 am

    Jaime Casanova 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
  • Jaime Casanova at Oct 6, 2009 at 2:30 pm

    On Mon, Oct 5, 2009 at 10:26 PM, Tom Lane wrote:
    Jaime Casanova <jcasanov@systemguards.com.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
  • Alvaro Herrera at Oct 6, 2009 at 2:43 pm

    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.
  • Tom Lane at Oct 6, 2009 at 2:58 pm

    Alvaro Herrera 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
  • Csaba Nagy at Oct 6, 2009 at 3:48 pm
    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.
  • Jaime Casanova at Oct 6, 2009 at 3:07 pm

    On Tue, Oct 6, 2009 at 9:43 AM, Alvaro Herrera 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
  • Euler Taveira de Oliveira at Oct 6, 2009 at 3:45 pm

    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/

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedOct 5, '09 at 11:19p
activeOct 2, '12 at 3:08a
posts15
users7
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase