From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Julie Nishimura <juliezain(at)hotmail(dot)com>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: does postgresql backup require additional space on disk |
Date: | 2019-05-13 21:47:42 |
Message-ID: | be1cbd4a-8796-f835-2677-e88dcb184272@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Julie Nishimura | 2019-05-13 22:27:30 | Re: does postgresql backup require additional space on disk |
Previous Message | Julie Nishimura | 2019-05-13 21:36:26 | Re: does postgresql backup require additional space on disk |