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-14 00:09:23 |
Message-ID: | 757d70ad-259e-4032-f57a-b9b692ee00f5@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 5/13/19 4:53 PM, Julie Nishimura wrote:
> Adrian,
> I think in my first emails in this chain about the effort i did
> specified it properly:
>
> CREATE TABLESPACE vol4
>> OWNER postgres
>> LOCATION '/data/vol4';
>>
>> ALTER DATABASE user_db_1
>> SET default_tablespace = 'vol4';
>>
>> ...for all dbs..
>
> Then "default" part got lost in emails...:)
Well that was a stupid on my part:(
To add insult to injury:
"No, what the link showed is:
ALTER DATABASE mydatabase SET default_tablespace = mynewtablespace
That is is the same as changing default_tablespace in postgresql.conf.
"
is wrong also. What the above does is change the behavior for that
database only.
>
>
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> *Sent:* Monday, May 13, 2019 4:33 PM
> *To:* Julie Nishimura; pgsql-general(at)lists(dot)postgresql(dot)org; pgsql-general
> *Subject:* Re: does postgresql backup require additional space on disk
> On 5/13/19 3:27 PM, Julie Nishimura wrote:
>> 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.
>
> No, what the link showed is:
>
> ALTER DATABASE mydatabase SET default_tablespace = mynewtablespace
>
> That is is the same as changing default_tablespace in postgresql.conf.
>
> It is not the same as:
>
> ALTER DATABASE name SET TABLESPACE
>
>>
>> Too bad we don't have dev with the same version. I guess I will create
>
> You can use the same commands on dev to test, just not ALTER DATABASE
> name SET TABLESPACE as that is not in 8.3.
>
>> new tablespace using new vol, move the smallest db for which I have
>
> You cannot move a db as a unit, you will need to move the individual
> tables/indexes in the db.
>
>> backup, create new test table and examine new/existing tables if they
>> have moved by selecting tablespace from pg_tables... right?
>
> Assuming you either specify the new tablespace on table CREATE or have
> set default_tablespace to the new tablespace.
>
>>
>>
>> http://www.postgresonline.com/journal/archives/123-Managing-disk-space-using-table-spaces.html
>> Managing disk space using table spaces - Postgres OnLine Journal -
>> Postgres OnLine Journal Magazine Jul 2017 - Dec 2017
>> <http://www.postgresonline.com/journal/archives/123-Managing-disk-space-using-table-spaces.html>
>> 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 <http://www.postgresonline.com>
>>
>>
>>
>> ------------------------------------------------------------------------
>> *From:* Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
>> *Sent:* Monday, May 13, 2019 2:47 PM
>> *To:* Julie Nishimura; pgsql-general(at)lists(dot)postgresql(dot)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(dot)klaver(at)aklaver(dot)com
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Julie Nishimura | 2019-05-14 00:39:20 | Re: does postgresql backup require additional space on disk |
Previous Message | Julie Nishimura | 2019-05-13 23:53:33 | Re: does postgresql backup require additional space on disk |