Re: [HACKERS] moving system catalogs to another tablespace
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] moving system catalogs to another tablespace
On Mon, Oct 1, 2012 at 3:08 PM, scc sc...@corscadden.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] moving system catalogs to another tablespace
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] moving system catalogs to another tablespace
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] moving system catalogs to another tablespace
On Mon, Oct 5, 2009 at 10:26 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jaime Casanova jcasa...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] moving system catalogs to another tablespace
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] moving system catalogs to another tablespace
Alvaro Herrera alvhe...@commandprompt.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] moving system catalogs to another tablespace
On Tue, Oct 6, 2009 at 9:43 AM, Alvaro Herrera alvhe...@commandprompt.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] moving system catalogs to another tablespace
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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] moving system catalogs to another tablespace
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] moving system catalogs to another tablespace
Jaime Casanova jcasa...@systemguards.com.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] moving system catalogs to another tablespace
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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] moving system catalogs to another tablespace
On Mon, Oct 5, 2009 at 7:15 PM, Euler Taveira de Oliveira eu...@timbira.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] moving system catalogs to another tablespace
Jaime Casanova jcasa...@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. 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
