Re: does postgresql backup require additional space on disk - Mailing list pgsql-general
| From | Julie Nishimura |
|---|---|
| Subject | Re: does postgresql backup require additional space on disk |
| Date | |
| Msg-id | BYAPR08MB5014D98A67FBD6F9A3F92AEEAC0F0@BYAPR08MB5014.namprd08.prod.outlook.com Whole thread Raw |
| In response to | Re: does postgresql backup require additional space on disk (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: does postgresql backup require additional space on disk
|
| List | pgsql-general |
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?
Below are steps to creating one. First create a folder on an available disk in your filesystem using an filesystem server administrative login; Next give full rights to the postgres server account (the one the daemon process runs under) or you can change the owner of the folder to the postgres account (in linux you can use chown postgres and on windows just use the administrative properties ... www.postgresonline.com |
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Monday, May 13, 2019 2:47 PM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: does postgresql backup require additional space on disk
Sent: Monday, May 13, 2019 2:47 PM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; 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@aklaver.com
> 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
pgsql-general by date: