|
Adrian, thanks for your reply. I previously found this link, which mentioned 8.2, that is why I assumed it will work with 8.3.
Too bad we don't have dev with the same version. I guess I will create new tablespace using new vol, move the smallest db for which I have backup, create new test table and examine new/existing tables if they have moved by selecting tablespace from pg_tables...
right?
From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
Sent: Monday, May 13, 2019 2:47 PM To: Julie Nishimura; pgsql-general@xxxxxxxxxxxxxxxxxxxx; pgsql-general Subject: Re: does postgresql backup require additional space on disk On 5/13/19 1:26 PM, Julie Nishimura wrote:
> Adrian, thanks for your reply. Couple of clarifications/questions: > 1) we are on 8.3 for this server 8.3 does not have ALTER DATABASE name SET TABLESPACE : https://www.postgresql.org/docs/8.3/sql-alterdatabase.html It does appear until 8.4. > 2) I was under impression, that "ALTER DATABASE name SET TABLESPACE > new_tablespace" won't move anything, and just utilize the new_tablespace > for new tables/indexes. If we would want to move existing tables, we > would need to move them one by one by No think of ALTER DATABASE name SET TABLESPACE as a bulk operation of ALTER TABLE /mytableschema.mytable/ SET TABLESPACE /mynewtablespace/ across all existing tables. This assumes the all existing tables live in the current default tablespace. It is moot in your case as 8.3 is not capable of doing this. > ALTER TABLE /mytableschema.mytable/ SET TABLESPACE /mynewtablespace/ > / > / The above is your option in 8.3. > 3) I thought if I want to have any newly created dbs go to the new vol, > I need to alter template. It is not required you can spec the tablespace in the CREATE DATABASE command: https://www.postgresql.org/docs/8.3/sql-createdatabase.html Though you can move them to make the tablespace the default. See above link. > 4) I was also thinking about changing parameters in config file to point > to the newly volume and reload postgresql.conf > > default_tablespace > > temp_tablespaces You could that. If you do it, moving the template tables would be redundant. > > > Am I wrong here? > > > Thanks, > > Julie > > -- Adrian Klaver adrian.klaver@xxxxxxxxxxx |
