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
