Skip site navigation (1) Skip section navigation (2)

Re: moving system catalogs to another tablespace

From: scc <scott(at)corscadden(dot)ca>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: moving system catalogs to another tablespace
Date: 2012-10-01 20:08:57
Message-ID: 1349122137455-5726202.post@n5.nabble.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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.


In response to

Responses

pgsql-hackers by date

Next:From: Jaime CasanovaDate: 2012-10-01 20:35:57
Subject: Re: moving system catalogs to another tablespace
Previous:From: Josh BerkusDate: 2012-10-01 20:04:54
Subject: Re: setting per-database/role parameters checks them against wrong context

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group