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@aklaver.com